# Pivot table and IF function

Help me study for my Excel class. I’m stuck and don’t understand.

• 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.