Reference no: EM132694573
Review Assignments
Data File needed for the Review Assignments: Snow.xlsx
Coltivare Tool also produces and sells a line of snow blowers. As you did with the company's line of power lawn mowers, Irena wants you to perform a what if analysis on the company's incon statement for its snow blower line She wants you to create one-variable and two-Variable dati to determine the break-even point for sales and use Scenario Manager to explore the impact on the profitability of the line under different possible scenarios. Finally, she wants you to calculate the product mix that will result in the maximum net income to the company. Complete the following:
1. Open the Snow workbook located in the Excel 10 > Review folder included with your Dato Site and then save the workbook as Snow Blowers in the location specified by your instructor
2. In the Documentation worksheet, enter your name and the date.
3. In the Income Statement worksheet in the range D6:G6, enter formulas that reference Units Sold value in cell B6 and the Revenue, Expenses, and Net Income values in the range B26:B28.
4. Create a one-variable data table in the range D7:G16 using Units Sold values that range from 1,000 to 10,000 in increments of 1,000.
5. Create a cost-volume-profit chart of the revenue and expenses values in the one-variable data table. Resize the chart so that it covers the range D17:G28. Format the chart so that it is easy to read and displays the break-even point.
6. In the range 16:N16, create a two-variable data table in which possible units sold values are placed in the range 17:116 containing values from 1,000 up to 10,000 in 1,000-unit increments. Place the possible average unit price in the range J6:N6 containing values from $400 to $600 in $50 increments. Have the data table display the net income for each combination of units sold and average price. Format the formula in cell 16 to display the text "Units Sold" rather than the net income value.
7. 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. Resize the chart so that it covers the range 117:N28.
8. Use the Scenario Manager to display the financial impact of the different scenarios listed in Figure 10-47. re 10-47 Snow blowers what-if scenarios Input Cells Units Produced and Sold Average Price per Unit Salaries and Benefits Shipping and Distribution Stocking and Storage Reduced Production Increased Production 7,000 $515 Status Quo 6,000 $515 $625,000 $105,000 Son 5,000 $515 Winter Sale 7,500 5485 $725,000 $750,000 $125,000 $515,000 Module 10 Performing What If Analyses Excel dros displaying the to hove the file cach scenario alled using the Currency style with no
9. Create a scenario summary report of the four scenarios proposed by tren impact on total revenue, total expe workbook. expenses, and net income. Move the worksheet to the narios proposed by Irena, displaying their
10. Create a Scenario Pivot Table report of the expenses, and net income under each scenario, formatted using the Currency report of the four scenarios displaying the total revenue, total decimal places. Remove the filter from the Pivot Table, and in cell Al enter the sheet title, formatting using the Title cell style. from the Pivot Table, and in cell A1 enter Scenario Report as
11. Add a PivotChart of the Pivot Table displaying the data as a clustered column chart pos over the range A8:D20. Format the chart to make it easy to read and interpret.
12. The Product Line worksheet lists five Use Solver to find the product mix that maximizes the value in orksheet lists five snow blower models produced and sold by Coltivare Tool. in the range B11:F11 under the following constraints: that maximizes the value in cell C24 by changing the values • The total snow blowers produced and sold as indicated in cell C19 must be 6000. • At least 700 of each model, as specified in the range B11:F11, must be produced and sold. • The values in the range B11:F11 must be integers. • The values in the range K29:K48 must be greater than or equal to zero because Coltivare Tool cannot produce more snow blowers than the available parts.
13. Enter Maximum Net Income in cell A26 formatted using the Accent2 cell style. Save the Solver model you just created to the range A27:A34.
14. Change the values in the range B11:F11 to 1200 units of each model, and then rerun Solver to find the product mix that minimizes the total material cost in cell C21 subject to the same constraints you used for the Maximum Net Income model.
15. Enter Minimum Material Cost in cell A36 formatted using the Accent2 cell style. Save the Solve model to the range A37:A44.
16. Restore the values in the range B11:F11 to 1200 units of each model is produced. Load the Maximum Net Income model into Solver, and then run Solver. Create an answer report, and then move the Answer Report worksheet to the end of the worksheet.
17 In the Product Line worksheet, in the merge cell C26, enter notes indicating which snow blow parts are a binding constraint on the Maximum Net Income solution.
18. Save the workbook, and then close it.
Case Problem 1 Data File needed for this Case Problem: Seminar.xlsx Constance Paget is the owner of Paget Integrated Solutions, a Web Paget Integrated Solutions Constance Fager in Seattle, Washington. Paget is planning a seminar on Web echnology consulting firm based in Seattle, Washington. Paget is planning st speaker to take place in Houston, Texas, next spring. She want for the seminar that will incorporate the fixed, variable, and mis our help in generating a budget for the seminar that will incorporate the fixe litions the seminar will be profitable for her company.
Attachment:- 3Snow.rar