Question: I need help with my excel work 6:11 Table 1: Income Analysis Scenario Values Scenario name Raise Price Changing cells C5:C6, C18:C20 Units_sold (C5) 4500

I need help with my excel work 6:11

Table 1: Income Analysis Scenario Values

Scenario name

Raise Price

Changing cells

C5:C6, C18:C20

Units_sold (C5)

4500

Price_per_unit (C6)

1239

Salaries_and_benefits (C18)

800000

Distribution (C19)

510000

Miscellaneous (C20)

300000

Use the Scenario Manager to create a Scenario Summary report that summarizes the effect of the Current, Lower Price, and Raise Price scenarios. Use the total revenue, total expenses, and net income in the range C24:C26 as the result cells. Go to the Scenario Summary worksheet and delete column D, which repeats the current values.

Return to the Income Analysis worksheet. Create a Scenario PivotTable report of the three scenarios displaying the total revenue, total expenses, and net income (range C24:C26) for each scenario.

Go to the Scenario PivotTable worksheet. Madhu wants to make the PivotTable easier to interpret. Remove the filter from the PivotTable. Use Total Revenue as the row label in cell B3, use Total Expenses as the row label in cell C3, and use Net Income as the row label in cell D3. Display the Revenue, Expenses, and Net Income values in the Currency number format with no decimal places. Display negative values in red, enclosed in parentheses. Resize columns B:D to their best fit using AutoFit.

Madhu also wants to compare the three scenarios in a chart. Create a Clustered Column PivotChart based on the PivotTable. Resize and position the chart so it covers the range A8:E24.

Go to the Product Line worksheet, which lists three wind turbines that Four Winds Energy produces and sells. Madhu wants to find the product mix that generates the most net income for the company. Use Solver to maximize the percentage of difference between the even and optimal product mixes (cell F23) by changing the optimal product mix (range C10:E10) subject to the following constraints: The total units sold in the optimal product mix (cell D18) must be 4,750. The company needs to produce 1,200 or more of each turbine model, so the optimal mix values for each model (range C10:E10) must be at least 1,200. Those same values in the range C10:E10 must be integers. The Remaining values for each assembled part (range J5:J13) must be greater than or equal to zero because the company cannot produce more wind turbines than the parts available.

Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model to the range H17:H24, and then close the Solver Parameters dialog box.

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!