[SOLVED] 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.