Advantage Cookware
Case 1
Karen Drexel is a financial analyst for Advantage Cookware, a manufacturer of quality cooking products based in Helena, Montana. She has been asked to develop a product mix for Advantage Cookware’s line of food processors that optimizes the company’s profits. To do this analysis, she will obtain information about the parts used in each model of food processor and the revenue and expenses associated with the production. Complete the following:
1. Open the Advantage workbook and then save the workbook as Advantage Cookware.
2. In the Documentation worksheet, enter your name and the date.
3. In the Product Mix worksheet, AutoFit the contents of column G and column M.
4. In the range B7:E7, calculate the total revenue from each of the four food processors.
5. In cell B10, calculate the material cost of the Prep 1000 food processor using the SUMPRODUCT function based on the range H4:H23 that contains a list of the Prep 1000 parts and the range L4:L23 that contains the cost of each part. In the range C10: E10, use the same function to calculate the material cost of the other three food processors.
6. In the range B12:E12, calculate the total cost per unit of each food processor by adding the material and manufacturing costs.
7. In the range B13:E13, calculate the total variable expenses of producing the food processors by multiplying the per unit cost by the number of units produced and sold.
8. In cell B21, calculate the total revenue generated from all four models. In cell B22, calculate the total variable expenses. In cell B23, calculate the total fixed expenses. In cell B24, calculate the net income from this line of food processors by subtracting the total variable and fixed expenses from the total revenue.
9. The current workbook has the company producing 3000 units of each model. To determine how many parts that would involve, enter Parts Used in cell N3, and then use the SUMPRODUCT function in cell N4 to multiply the number of models produced and sold in the range B5:E5 by the parts required for each product in the range H4:K4. (Use an absolute reference to the range B5:E5.) Copy the formula to the range N5:N23 to calculate the number of each part that will be used in the production run.
10. To determine the number of each part remaining, enter Remaining in cell O3, and then in the range O4:O23, calculate the parts remaining by subtracting the Parts Used value from the Parts In Stock value.
11. Copy the format from the range M3:M23 and apply it to the range N3:O23.
12. In the range O4:O23, apply conditional formatting so that any value less than zero appears in a bold red font.
13. In cell A26, enter Production Status. In cell B26, enter an IF statement that tests whether the minimum value in the range O4:O23 is less than zero. If it is, display Not Enough Parts; otherwise, display Parts OK.
14. Use the Scenario Manager to create the following scenarios that Karen wants to investigate for production:
· Base scenario with the company producing and selling 3000 units of every model.
· Expanded scenario with the company producing and selling 5000 units of every model.
· High End scenario with the company producing and selling 2000 units each of the Prep 1000 and Prep 1200 models and 5000 units each of the Prep 2000 and Elite Prep models.
· Low End scenario with the company producing and selling 5000 units each of the Prep 1000 and Prep 1200 models and 2000 units each of the Prep 2000 and Elite Prep models.
15. Use Solver to find the optimal product mix by maximizing the net income value by changing the number of each model produced and sold, subject to the following constraints
· The number of each model produced and sold is an integer.
· At least 3000 of each food processor model is produced.
· The number of each part remaining after the production run is greater than or equal to zero.
16. Save the resulting Solver solution as a scenario under the name Optimal.
17. Create defined names for the production values in the range B5:E5 using the labels in the range B4:E4. Create defined names for the revenue, expense, and net income values in the range B21:B24 using the labels from the range A21:A24. Create a defined name for cell B26 using the label in cell A26.
18. Create a scenario summary report for the five scenarios using the range B21:B24;B26 as the result cells. Add Invalid Scenario as a note (Excel 365) or comment (Excel 2019) to any scenario in which there are not enough parts to complete this order. Move the Scenario Summary worksheet to the end of the workbook.
19. Modify the following document properties of the workbook:
· Add your name as an author
· Set the Title property as Optimal Product Mix
· Set the Tags property as food processors; product mix
· Set the Categories property as food processors
· Set the Company property as Advantage Cookware
20. Create the custom Department property with the value Financial Analysis, and then create the custom Checked by property with your name as the value.
21. Modify the page layout so that all 3 pages are printed in landscape orientation and scaled so that each worksheet fits on a single page.
22. Create a footer for each page that displays the filename in the left section, the sheet name in the right section, and the page number in the center section. Create a header for each page that displays your name in the right section.
23. Display the results of the Optimal scenario in the Product Mix worksheet, and then print preview all of the worksheets in the workbook.
24. Mark the workbook as Final, and then close it.
