CVP Excel exercise 1 PDF

Preview

Summary

Download CVP Excel exercise 1 PDF

Description

Excel Exercise - CVP Analysis for multiple products Shine Alloys Ltd is a retailer of Alloy wheels for Hotr unique. For the purpose of cost control and planni Note each category refers to a set of four alloys.

Product Custom design set Limited edition set In a normal month, the Management of Shine expec remaining Limited edition sets. Fixed costs per month: REQUIRED: All cells in yellow need to be completed. Orange arrows provide > Requirement 1 Calculate the variable cost (sales commission), tota (Contribution margin) Product Custom design set Limited edition set

> Requirement 2 (Break even)

Calculate the amount of sales units and dollars) the Fixed costs Weighted average contribution margin Break even total (units) Custom design set breakeven (units) Limited edition sets breakeven (units) Weighted average sales price Break even total (sales) Custom design set breakeven (sales) Limited edition sets breakeven (sales)

> Requirement 3 (Target Shine Ltd managers would like to generate a target p Profit) TIP: A table like the one in requirement 2 can be us Fixed Costs + profit

Weighted average contribution margin Break even total (units) Custom design set breakeven (units) Limited edition sets breakeven (units) Weighted average sales price Break even total (sales) Custom design set breakeven (sales) Limited edition sets breakeven (sales) > Requirement 4 (Scenario planning)

The sales price, variable costs and fixed costs are all a b c d TIP: Managers would like to compare the relevant scenario worksheets below. You can then change t on the mouse, select copy, click on the relevant req

> Requirement 5

Print each worksheet to take to your tutorial. TIP: To save on printing click on the view tab above

rods imported from Southern California. There are a vast number of different types of Alloy wheels imported ng Shine classifies its stock according to whether it is a custom design (high price) or limited edition (low pr

Sale price $3,900 $2,900

Sales commission (based on sale price) 5% 2%

Cost price $2,400 $1,900

cts that 30% of total sales will be Custom design sets and the $57,000 guidance on completing the relevant formulas using excel.

To calculate Custom design se "Total variable cost" enter the following formul in cell F17: =D17+E17

l variable cost, contribution margin and weight average contribution margin.

Sale price $3,900 $2,900

Variable cost (alloy set) $2,400 $1,900

Variable cost (sales commission) $195.00 $58.00

Total variable cost $2,595.00 $1,958.00

To calculate Custom design set "Variable cost (sale commision)" enter the following formula in cell E17: =C7*E7 company must generate if it is to break even. Calculate amount in total and for each product. $57,000.00 $1,050.90 54.24 16.27 37.97 $105.09 542.3922352 162.7176706 379.6745647

To calculate breakeven you will need to use the division key : "

Apply the same process as the weighted average contribu

profit of $105,000 per month. How many of each alloy wheel set must be sold to generate the target profit. ed to calculate the relevant numbers in this question. The weight sales mix can then be used to calculate $162,000.00

1,050.90 154.15 46.25 107.91 $105.09 1541.535826 462.4607479 1079.075079 based on estimates. Shine Ltd management would like to see how much the breakeven point and target sal The Australian dollar depreciates & Custom Alloy wheel set cost price increases to $3,000 & Limited editio Warehouse unit rent increases, raising the fixed costs to $79,000 per month Economic growth decreases and 80% of sales are now Limited edition sets A competitor enters the Australia market selling imported wheels from Detroit, Michigan. To remain comp to $3,000 and Limited edition sets to $2,000 numbers for each scenario. To keep a record of each scenario, copy and paste the requirements you have the numbers as required to see how the breakeven and target sales change for each scenario. To copy, hig quirement tab below, click on cell A1, right click the mouse and select "Paste Special" and finally select "Fo

e and then page break preview. The dashed line can then be moved so each worksheet is printed on one p

d and retailed by Shine because customers want their Hotrod to look rice) alloy. Information concerning each category is provided below.

If you successfully calculated the "Variable cost (sales commission)" the same type of multiplication formula can be used for cells I17 & I18 below.

et

la

Contribution Margin $1,305.00 $942.00

Weighted sales mix 0.3 0.7

Weighted average contribution margin $391.50 $659.40

To calculate Custom design set "Contribution Margin" enter the following formula in cell G17: =C17-F17

" / "

tion margin to calculate weight average sales price

. Calculate amount in total and for each product. the neccesary set of each alloy wheel set.

es changes based on the following independent scenarios: n set to $2,500

petitive Shine Ltd is forced to reduce the price of Custom design sets completed above into the relevant hlight the relevant area, right click rmulas".

age....