Pivot table and IF function
Question Description
- 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 your college paper done by experts
Do my questionPlace an order in 3 easy steps. Takes less than 5 mins.
Leave a Reply
Want to join the discussion?Feel free to contribute!