Problem: Big Al 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 $32 per unit, with a variable cost of $16 per unit and a fixed cost of $95,000 per yoar. Using the "HW 4 Starting File" workbook in Blackboard, create and amend the workbook to do the following. Make sure each part is in a separate worksheet within the same workbook. (i.e. - tabs in the same file) 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 in the same workbook. 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:27. Only provide the Goal Seek dialog box. c. In the textbox 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:019. 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: Sales (Quantity Made and Sold) can be increased by 35% of their current levels. Alternative 2: Variable costs can be decreased by 20%. 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 c. Provide a written recommendation of your recommendation between Alternative 1 and Alternative 2 in the text box provided in 13:011 Response: 2 3 Inputs 4 Unit Sales Price 5 Annual Fixed Cost 6 Unit Variable Cost 7 8 Quantity Made and Sold 9 10 Calculations and Outputs 11 Annual Revenue 12 Annual Variable Costs 13 Annual Fixed Cost Annual Total Cost 15 Annual Profit (Loss) 16 17 18 2 3 Alternative 1 (Increase sales by 35%) 4 Inputs Alternative 2 (Reduce Variable Cost by 20%) Inputs Unit Sales Price Annual Fixed Cost Unit Variable Cost Quantity Made and Sold 5 Unit Sales Price G Annual Fixed Cost 7 Unit Variable Cost B 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 20 21 22 23 24 25 25 Calculations and Outputs Annual Revenue Annual Variable Costs Annual Fixed Cost Annual Total Cost Annual Profit (Loss) Interpretation for Alternative 2