Pivot table and IF function

  • From the Orders tab, insert a pivot table, selecting a new worksheet.Name the new worksheet Pivot Table. Navigate the pivot table in such a way that you can show the answer to the question: “How many units were sold by Salesrep and by Region for the year?”Format the fields with commas and be sure to use both the column and the row drilldown/drill across to show your answer. (5 points)
  • Back on the Orders tab, add a new column that shows Total Sales and use an IF statement to calculate the figure using the following logic:The price per unit for Product A is $5.00, but for everything else, it is $6.00.This price should be multiplied by the figure in the Units column in order to show the Total Sales. (5 points)
  • On the Sales Planning tab, determine your next year’s sales goals by doing the following:(5 points)
  • Scenario Manager (5 points):
    • Copy your Sales Planning worksheet to a new worksheet and call the worksheet Scenario Analysis.
    • Create a Scenario analysis in the new worksheet that shows the following scenarios:
      • “Base” = 0% price increase, same sales volumes for each product as in the Sales Volume worksheet
      • “50% Price Increase” = Price increases at 50%, volumes stays as it was in the Base scenario.
      • “Double Sales Volume” = Sales volumes double from where they are in the Base scenario, no price increase
      • “Recession” = Volumes are half of what they are in the Base scenario and prices decline by 35%.
    • Create a Summary that shows all scenarios and the impact on Total Sales.
  • Solver (5 points):
    • Go to the Income Statement tab and copy it to create a new one.Call the new tab Solver Analysis.Net profit to start should be $7,025.76.
    • On the new Solver Analysis tab, input the following Solver Parameters:
  • Add a new column after the Base Price and call it Target Price, followed by another column called Target Sales.
  • In cell H1, enter a percentage increase value that you’ll use to determine your goal.Set it to 0 to start.Format as a percentage with one decimal point.
  • Make the Target Price for each product equal to the respective value from the Base Price column increased by the percentage increase value that you set in cell H1.
  • Make the Target Sales column equal to the Projected Units times the Target Price.
  • Add a total sales figures at the bottom of the Target Sales column.
  • Use the Goal Seek tool and determine what rate of price increase you would need to achieve a sales target of $30,000 next year.Leave this value in the worksheet that you save and submit later.
  • Objective cell: Maximize your Net Profit
  • By changing your product volumes
  • Constraint 1: Product A must sell at least 3000 units
  • Constraint 2: Product D must sell at least 6000 units
  • Constraint 3: Total Revenues cannot exceed $150,000
  • Constraint 4: Total Operating Expenses cannot exceed $25,000.
  • Constraint 5: All unit sold quantities must be integers.
    • Solve for the goal against these constraints.

"Get 15% discount on your first 3 orders with us"
Use the following coupon

Order Now