Tutorial 10 Review Assignment Data File needed for the Review Assignments: Mountain.xlsx
Cycle Green is expanding its product mix into a new line of mountain bikes. Gianna has created a workbook with an income statement detailing the revenue and expenses associated with this new lineup. She wants you to perform what-if analyses of the data and determine the optimal product mix. Complete the following:
Open the Mountain workbook located in the Excel10 Review folder included with your Data Files, and then save the workbook as Mountain Bikes in the location specified by your instructor.
In the Documentation worksheet, enter your name and the date.
In the Income Statement worksheet, in cell D4, enter a formula that references cell B4. In cell E4 through cell G4, enter formulas that reference cell B24 through cell B26, respectively.
In the range D5:D13, enter bikes sold values of 100 up to 900 in increments of 100.
In the range D4:G13, create a one-variable data table using $B$4 as the column input cell to explore the impact of different sales volumes on revenue, expenses, and net income.
Create a cost-volume-profit chart as a scatter chart in the range B14:G26. Format the chart appropriately.
Complete the following steps to create a two-variable data table to analyze the impact of price and sales volume on net income:
1. In cell I4, enter a formula using a defined name to reference cell B26. 2. Format cell I4 so that it displays the text Bikes Sold. 3. In the range I5:I13, enter sales volume values of 100 up to 900 in increments of 100. 4. In the range J4:N4, enter average bike prices of $600 up to $1,000 in increments of $100. 5. In the range I4:N13, insert a two-variable data table using $B$5 as the row input cell and $B$4
as the column input cell. 8. 8.
In the range I14:N26, create a scatter chart of the net income values from the two-variable data table, displaying each average sales price as a different line in the chart. Format the chart appropriately; make sure that the chart legend identifies each line by the average sales price value listed in row 4.
Use the Scenario Manager to analyze the financial impact of the different scenarios listed in Figure 10-47.
What-if scenarios for mountain bikes
Input Cells Status Quo Increased
Sale Units Sold (cell B4) 330 500 250 600 Average Price per Unit (cell B5) $890 $820 $800 $775
Salaries and Benefits (cell B17) $150,000 $200,000 $100,000 $175,000
Advertising (cell B18) $10,000 $30,000 $5,000 $40,000 Administrative (cell B19) $15,000 $30,000 $5,000 $25,000 Miscellaneous (cell B20) $5,000 $10,000 $5,000 $10,000
2014 Cengage Learning
Create a scenario summary report of the four scenarios proposed by Gianna, displaying the total revenue, total expenses, and net income under each scenario. Move the worksheet to the end of the workbook. Add a comment to the workbook noting the highest net income to the company.
Based on the values in the Income Statement worksheet, create a Scenario PivotTable report of the four scenarios displaying the total revenue, total expenses, and net income under each scenario. Move the Scenario PivotTable to the end of the worksheet.
Format the Scenario PivotTable worksheet as follows:
1. Remove the filter from the PivotTable. 2. In cell A1, enter Scenario Report and format the text using the Title cell style. 3. Format the revenue, expense, and net income values using the Currency format with negative
numbers displayed in red and enclosed within parentheses. 4. Add a PivotChart of the PivotTable displaying the data as a clustered column chart positioned
over the range A8:E21. Format the chart to make it easy to read and interpret. 13. 13.
The Product Mix worksheet lists four mountain bike models produced by Cycle Green. Use Solver to find the product mix that maximizes the value in cell B18 by changing the values in the range B4:E4.
Apply the following constraints to your model:
o The total bikes produced and sold as indicated in cell B13 must be 330. o Only integer numbers of each bike model indicated in the range B4:E4 can be produced and sold. o At least 50 units of each bike model must be produced and sold. o The number of parts remaining, as indicated in the range J21:J34, after the production run must
be greater than or equal to 0. 15. 15.
Run Solver and note how much net income increases under the Solver model.
Save the parameters of the Solver model to the range A21:A28 in the Product Mix worksheet. Enter the text Maximum Net Income Model in cell A20.
Restore the values in the range B4:E4 to their current product mix value, and then rerun Solver. Create an answer report detailing the Solver solution, and then move the Answer Report 1 worksheet to the end of the workbook.
In the Product Mix worksheet, change the Solver model so that it minimizes the total material cost in cell B15. Save the parameters of this model to the range A31:A38, and then enter the text Minimum Material Expenses in cell A30.
Reload the Maximum Net Income Model into Solver and run Solver to display the product mix that results in the maximum net income.
Save the workbook, and then close it.
Tutorial 10 Review AssignmentWhat-if scenarios for mountain bikes