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.

Looking for solution of this Assignment?

WHY CHOOSE US?

We deliver quality original papers	Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected
Free revisions	We offer our clients multiple free revisions just to ensure you get what you want.
Discounted prices	All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount
100% originality	We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI
On-time delivery	We will deliver your paper on time even on short notice or  short deadline, overnight essay or even an urgent essay