Pivot Table And If Function

By
Responsive Centered Red Button

Need Help with this Question or something similar to this? We got you! Just fill out the order form (follow the link below), and your paper will be assigned to an expert to help you ASAP.

Order Form

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.

How to create Testimonial Carousel using Bootstrap5

Clients' Reviews about Our Services