OM300: HW3 Problem: Shutter Plastics produces a variety of plastic items for packaging and distribution. One item, container #145, has had a low contribution to profits. Last year, 20,000 units of container #145 were produced and sold. The selling price of the container was $25 per unit, with a variable cost of $16 per unit and a fixed cost of $70,000 per year. Using the HW 3 Starting File workbook in Blackboard, create and amend the workbook to do the following. Make sure each part is in a separate worksheet. 1. (25 pts) By amending the Question 1 worksheet, what is the profit level for the base case? a. Validate you have constructed a correct, flexible, and documented base-case spreadsheet model that allows the user to easily vary the inputs to the problem and see the resulting revenue, costs, and profit b. There should be no hard-coding in the Calculations and Outputs. c. In the textbox provided in E4:J12, provided at least a one-sentence response to the question asked. 2. (25 pts) Create a copy of the Question 1 worksheet; rename this copy Question 2. Using the Question 2 worksheet, find the amount of units to break-even. a. Ensure you have used Goal Seek in order to find this quantity. b. Provide a screenshot of the Goal Seek Dialog Box in A17:C27. Only provide the Goal Seek dialog box. c. In the textbox provided in E4:J12, provided an explanation of the analysis that was performed. 3. (25 pts) Create a copy of the Question 1 worksheet; rename this copy Question 3. Using the Question 3 worksheet, construct a data table showing profit versus quantity. Also, create a chart that will show the created data table in graphical form. a. Vary the quantity from 0 to 70,000 in increments of 5,000 for the data table. b. Put the data table in columns E and F. (You can delete the text box) c. Ensure there are headers on the data table for easy reading. d. Put the chart in H4:O19. Ensure the chart has a meaningful title. The company is considering ways to improve profitability by either stimulating sales volume or reducing variable costs. There are two alternatives. Alternative 1: Management believes sales (Quantity Made and Sold) can be increased by 35% of their current levels. Alternative 2: Variable costs can be decreased by 10%. Assuming all other costs stay at the base-case levels (values established in question 1), use the model to determine which alternative would lead to a higher profit contribution. 4. (25 pts) Using the Question 4 worksheet, provide a side by side set of computations for the two alternatives described above. a. Ensure Alternative 1 is placed in the area for Alternative 1 in columns A:C. Ensure Alternative 2 is placed in the area for Alternative 2 in columns E:G. b. Provide a written interpretation of the result for each alternative in the provided text boxes. Alterative 1s interpretation should be placed in the text box in A18:C24. Alterative 2s interpretation should be placed in the text box in E18:G24. c. Provide a written recommendation of your recommendation between Alternative 1 and Alternative 2 in the text box provided in I3:O11.

AutoSave Off Have HW 3 Starting File (1) - Excel Search Joseph Crawford JC 11 File Home Insert Draw Page Layout Formulas Data Review View Developer Help Share Comments Calibri 11 AP == ab Wrap Text General CIT ST TI AY W o TA 5 Paste BIU A $ %, -20 EE E Merge & Center Insert Delete Format y Ideas Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Ideas B35 X A B D E F G H 1 J K L M N o Q R SA 1 OM 300 HW 3 2 3 Inputs 4 5 Response: Unit Sales Price Annual Fixed Cost Unit Variable Cost 6 7 Quantity Made and Sold 8 9 10 Calculations and Outputs 11 Annual Revenue 12 Annual Variable Costs 13 Annual Fixed Cost 14 Annual Total Cost 15 Annual Profit (Loss) 16 17 18 19 20 21 22 Documentation Question 1 Question 4 # Accessibility: Investigate B + 100% 1 Type here to search PI = sling E e 9:36 AM 6/20/2020 AutoSave Off Have HW 3 Starting File (1) - Excel Search Joseph Crawford JC 11 File Home Insert Draw Page Layout Formulas Data Review View Developer Help Share Comments Calibri 11 AP == ab Wrap Text General CIT ST TI AY W o TA 5 Paste BIU MA Ideas EE E Merge & Center y $ - % Insert Delete Format 820 Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Ideas J15 X A B D E F H - K L M N o P 1 OM 300 HW 3 Alternative 2 Recommendation: Inputs Unit Sales Price Annual Fixed Cost Unit Variable Cost Quantity Made and Sold 2 3 Alternative 1 4 Inputs 5 Unit Sales Price 6 Annual Fixed Cost 7 Unit Variable Cost 8 9 Quantity Made and Sold 10 11 Calculations and Outputs 12 Annual Revenue 13 Annual Variable Costs 14 Annual Fixed Cost 15 Annual Total Cost 16 Annual Profit (Loss) 17 18 Interpretation for Alternative 1: 19 Calculations and Outputs Annual Revenue Annual Variable Costs Annual Fixed Cost Annual Total Cost Annual Profit (Loss) Interpretation for Alternative 2: 20 21 Documentation Question 1 Question 4 + Accessibility: Investigate B + 100% 1 Type here to search PI sling e 9:36 AM 6/20/2020 AutoSave Off Have HW 3 Starting File (1) - Excel Search Joseph Crawford JC 11 File Home Insert Draw Page Layout Formulas Data Review View Developer Help Share Comments Calibri 11 AP == ab Wrap Text General CIT ST TI AY W o TA 5 Paste BIU A $ %, -20 EE E Merge & Center Insert Delete Format y Ideas Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Ideas B35 X A B D E F G H 1 J K L M N o Q R SA 1 OM 300 HW 3 2 3 Inputs 4 5 Response: Unit Sales Price Annual Fixed Cost Unit Variable Cost 6 7 Quantity Made and Sold 8 9 10 Calculations and Outputs 11 Annual Revenue 12 Annual Variable Costs 13 Annual Fixed Cost 14 Annual Total Cost 15 Annual Profit (Loss) 16 17 18 19 20 21 22 Documentation Question 1 Question 4 # Accessibility: Investigate B + 100% 1 Type here to search PI = sling E e 9:36 AM 6/20/2020 AutoSave Off Have HW 3 Starting File (1) - Excel Search Joseph Crawford JC 11 File Home Insert Draw Page Layout Formulas Data Review View Developer Help Share Comments Calibri 11 AP == ab Wrap Text General CIT ST TI AY W o TA 5 Paste BIU MA Ideas EE E Merge & Center y $ - % Insert Delete Format 820 Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Ideas J15 X A B D E F H - K L M N o P 1 OM 300 HW 3 Alternative 2 Recommendation: Inputs Unit Sales Price Annual Fixed Cost Unit Variable Cost Quantity Made and Sold 2 3 Alternative 1 4 Inputs 5 Unit Sales Price 6 Annual Fixed Cost 7 Unit Variable Cost 8 9 Quantity Made and Sold 10 11 Calculations and Outputs 12 Annual Revenue 13 Annual Variable Costs 14 Annual Fixed Cost 15 Annual Total Cost 16 Annual Profit (Loss) 17 18 Interpretation for Alternative 1: 19 Calculations and Outputs Annual Revenue Annual Variable Costs Annual Fixed Cost Annual Total Cost Annual Profit (Loss) Interpretation for Alternative 2: 20 21 Documentation Question 1 Question 4 + Accessibility: Investigate B + 100% 1 Type here to search PI sling e 9:36 AM 6/20/2020