Question

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

Answer 1

Produced and sold =20,000 units

1. Computation of Profit in amount and in per unit:

particulars                                                             Amount $

sales 20000* 32                                    640,000

less:Variable cost $16* 20000                      (320,000)

Contribution                                                         320.000

less: Fixed costs                                                  (95000)   

Profit           225,000    

Profit level for the base case= $225000

if want to calculate profit per unit= 225000/20000 = $11.25per unit

2. Amount of units in Break even =

break even Point= Fixed costs/ Contribution per unit

Fixed costs= $95,000

Contribution per unit= sales- variable cost

sale per unit= 32

variable cost per unit= $ 16

Contribution per unit= $16 per unit

thus break even point= 95000/16

                             = 5937.50 units

5937.50 units needs to break even.

if you wants to break even point in $ then simply Break even point units multiply with Selling price per unit.

3. find out profit and higher contribution by using 2 Alternatives:

Alternative 1 :Sales( Quantity made and Sold) can be increased by 35% of their current levels.

and Other costs are remian same as base level.

if Quantity made increased by 35% of 20,000 units = 7000units

means 7000 units will be made further.

thus: 20000+7000= 27,000 units                                                                        

Partiulars                                                                                          $               

Sales 27000* 32                                                                            864,000

Variable cost 27000* 16                                                                  (432,000)    

Contribution                                                                                   432,000

Fixed costs                                                                                   (95,000)      

Profit                                                                                            337,000    

Alternative 2 Variable cost decreased by 20%

if variable cost per unit is $16 per unit and now decreaseby20% then variable cost will be 16* 20%= 12.80per unit

IMPORTANT TIP: WHEN IN QUESTION "BY" IS WRITTEN THEN SAME AMOUNT IS CONSIDER. BUT IF "TO" WRITTEN THEN DIFFREENCE IS CONSIDERED.

Statement showing profit if change in Vaiable cost per unit:                   

particulars                                                                           $        

sale 20000* 32                                                                640,000

Variable cost 20000*12.80 ( 256,000)

Contribution                                                                   384,000

Less: Fixed costs                                                      (95,000)      

profit                                                                            289,000      

units sale Profit 20000 units 27000 units $640,000 864,000 225000 337000

864,000 $640,000 $1,000,000 $900,000 $800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $100,000 $0 337000 225000 20000units 27000 units sale Profit

20000 640000 units sale Vaiable cost Profit 20000 640000 320000 225000 256000 289000

700000 600000 500000 400000 300000 Series1 Series2 200000 100000 0 sale Vaiable cost Profit

4. Interpretation:

if increase in Sales units then it is obivous that increase in Sales in $ but also increase in variable cost increase in sales leads to increase in Profit also.

Number of Units remains same. Then Sale will remain same. But here is decrease in cost, so with this increase in profit. So this option is inrease in %age of profits on sale 45.16% previous it on the base data it was 35.16%.