Title | Tutorial 9 CVP Excel Spreadsheet |
---|---|
Author | Tristan Cham |
Course | Accounting for Business Decisions A |
Institution | University of Technology Sydney |
Pages | 7 |
File Size | 407 KB |
File Type | |
Total Downloads | 101 |
Total Views | 182 |
Download Tutorial 9 CVP Excel Spreadsheet PDF
Tristan Chambers 11502299
Tutorial 9 - CVP Spreadsheet
Excel Exercise - CVP Analysis for multiple products Tristan Chambers 11502299 Shine Alloys Ltd is a retailer of Alloy wheels for Hotrods imported from Southern California. There are a vast number of different types of Alloy wheels imported and retailed by Shine because customers want their Hotrod to look unique. For the purpose of cost control and planning Shine classifies its stock according to whether it is a custom design (high price) or limited edition (low price) alloy. Information concerning each category is provided below. Note each category refers to a set of four alloys.
Product Custom design set Limited edition set
Sale price $3,900 $2,900
Cost price $2,400 $1,900
Sales commission (based on sale price) 5% 2%
In a normal month, the Management of Shine expects that 30% of total sales will be Custom design sets and the remaining Limited edition sets. Fixed costs per month: $57,000 REQUIRED: All cells in yellow need to be completed. Orange arrows provide guidance on completing the relevant formulas using excel. > Requirement 1 Calculate the variable cost (sales commission), total variable cost, contribution margin and weight average contribution margin. (Contribution margin) Variable cost (sales commission) Product Sale price Variable cost (alloy set) Custom design set $3,900 $2,400 $120.00 Limited edition set $2,900 $1,900 $38.00 > Requirement 2 (Break even)
Contribution Margin $1,380.00 $962.00
Weighted sales mix 0.3 0.7
Weighted average contribution margin $414.00 $673.40
Calculate the amount of sales units and dollars) the company must generate if it is to break even. Calculate amount in total and for each product. 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 Profit)
Total variable cost $2,520.00 $1,938.00
$57,000.00 $1,087.40 52.42 15.73 36.69 $3,200.00 17.81 5.34 12.47
Shine Ltd managers would like to generate a target profit of $105,000 per month. How many of each alloy wheel set must be sold to generate the target profit. Calculate amount in total and for each product. TIP: A table like the one in requirement 2 can be used to calculate the relevant numbers in this question. The weight sales mix can then be used to calculate the neccesary set of each alloy wheel set. Fixed costs $57,000.00 Profit $105,000.00 Weighted average contribution margin $1,087.40 Break even total (units) 148.98 Custom design set breakeven (units) 44.69 Limited edition sets breakeven (units) 104.29 Weighted average sales price $3,200.00 Break even total (sales) 50.63 Custom design set breakeven (sales) 15.19
1 of 2
Tutorial 9 - CVP Spreadsheet
Limited edition sets breakeven (sales)
Tristan Chambers 11502299
35.44
> Requirement 4 (Scenario planning)
The sales price, variable costs and fixed costs are all based on estimates. Shine Ltd management would like to see how much the breakeven point and target sales changes based on the following independent scenarios: a The Australian dollar depreciates & Custom Alloy wheel set cost price increases to $3,000 & Limited edition set to $2,500 b Warehouse unit rent increases, raising the fixed costs to $79,000 per month c Economic growth decreases and 80% of sales are now Limited edition sets d A competitor enters the Australia market selling imported wheels from Detroit, Michigan. To remain competitive Shine Ltd is forced to reduce the price of Custom design sets to $3,000 and Limited edition sets to $2,000 TIP: Managers would like to compare the relevant numbers for each scenario. To keep a record of each scenario, copy and paste the requirements you have completed above into the relevant scenario worksheets below. You can then change the numbers as required to see how the breakeven and target sales change for each scenario. To copy, highlight the relevant area, right click on the mouse, select copy, click on the relevant requirement tab below, click on cell A1, right click the mouse and select paste.
> Requirement 5
Print each worksheet to take to your tutorial. TIP: To save on printing click on the view tab above and then page break preview. The dashed line can then be moved so each worksheet is printed on one page.
2 of 2
Tristan Chambers 11502299
Tutorial 9 - CVP Spreadsheet
Shine Alloys Ltd is a retailer of Alloy wheels for Hotrods imported from Southern California. There are a vast number of different types of Alloy wheels imported and retailed by Shine because customers want their Hotrod to look unique. For the purpose of cost control and planning Shine classifies its stock according to whether it is a custom design (high price) or limited edition (low price) alloy. Information concerning each category is provided below. Note each category refers to a set of four alloys. > Requirement 4 (Scenario planning)
The sales price, variable costs and fixed costs are all based on estimates. Shine Ltd management would like to see how much the breakeven point and target sales changes based on the following independent scenarios: a The Australian dollar depreciates & Custom Alloy wheel set cost price increases to $3,000 & Limited edition set to $2,500
Product Custom design set Limited edition set
Sale price $3,900 $2,900
Cost price $3,000 $2,500
Sales commission (based on sale price) 5% 2%
Variable cost (alloy set) $3,000 $2,500
Variable cost (sales commission) $150.00 $50.00
In a normal month, the Management of Shine expects that 30% of total sales will be Custom design sets and the remaining Limited edition sets. Fixed costs per month:
$57,000
Contribution margin
Product Custom design set Limited edition set
Sale price $3,900 $2,900
Break Even
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)
$57,000.00 $470.00 121.28 36.38 84.89 $3,200.00 17.81 5.34 12.47
Target Profit
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)
$57,000.00 $105,000.00 $470.00 344.68 103.40 241.28 $3,200.00 50.63
Total variable cost $3,150.00 $2,550.00
Contribution Margin $750.00 $350.00
Weighted sales Weighted average mix contribution margin 0.3 $225.00 0.7 $245.00
1 of 2
Tutorial 9 - CVP Spreadsheet
Custom design set breakeven (sales) Limited edition sets breakeven (sales)
Tristan Chambers 11502299
15.19 35.44
2 of 2
Tristan Chambers 11502299
Tutorial 9 - CVP Spreadsheet
Shine Alloys Ltd is a retailer of Alloy wheels for Hotrods imported from Southern California. There are a vast number of different types of Alloy wheels imported and retailed by Shine because customers want their Hotrod to look unique. For the purpose of cost control and planning Shine classifies its stock according to whether it is a custom design (high price) or limited edition (low price) alloy. Information concerning each category is provided below. Note each category refers to a set of four alloys. > Requirement 4 (Scenario The sales price, variable costs and fixed costs are all based on estimates. Shine Ltd management would like to see how much the breakeven point and target sales changes based on the following independent scenarios: planning) b Warehouse unit rent increases, raising the fixed costs to $79,000 per month
Product Custom design set Limited edition set In a normal month, the Management of Shine expects that 30% of total sales will be Custom design sets and the remaining Limited edition sets. Fixed costs per month:
Sale price $3,900 $2,900
Cost price $2,400 $1,900
Sales commission (based on sale price) 5% 2%
Variable cost (alloy set) $2,400 $1,900
Variable cost (sales commission) $120.00 $38.00
$79,000
Contribution margin
Product Custom design set Limited edition set
Sale price $3,900 $2,900
Break Even
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)
$79,000.00 $1,087.40 72.65 21.80 50.86 $3,200.00 24.69 7.41 17.28
Target Profit
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)
$79,000.00 $105,000.00 $1,087.40 169.21 50.76 118.45 $3,200.00 57.50 17.25 40.25
Total variable cost $2,520.00 $1,938.00
Contribution Margin $1,380.00 $962.00
Weighted sales mix 0.3 0.7
Weighted average contribution margin $414.00 $673.40
1 of 1
Tristan Chambers 11502299
Tutorial 9 - CVP Spreadsheet
Shine Alloys Ltd is a retailer of Alloy wheels for Hotrods imported from Southern California. There are a vast number of different types of Alloy wheels imported and retailed by Shine because customers want their Hotrod to look unique. For the purpose of cost control and planning Shine classifies its stock according to whether it is a custom design (high price) or limited edition (low price) alloy. Information concerning each category is provided below. Note each category refers to a set of four alloys. > Requirement 4 (Scenario planning)
The sales price, variable costs and fixed costs are all based on estimates. Shine Ltd management would like to see how much the breakeven point and target sales changes based on the following independent scenarios: c Economic growth decreases and 80% of sales are now Limited edition sets
Product Custom design set Limited edition set
Sale price $3,900 $2,900
Cost price $2,400 $1,900
Sales commission (based on sale price) 5% 2%
Variable cost (alloy set) $2,400 $1,900
Variable cost (sales commission) $120.00 $38.00
In a normal month, the Management of Shine expects that 30% of total sales will be Custom design sets and the remaining Limited edition sets. Fixed costs per month: $57,000
Contribution margin
Product Custom design set Limited edition set
Sale price $3,900 $2,900
Break Even
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)
$57,000.00 $1,045.60 54.51 10.90 43.61 $3,100.00 18.39 3.68 14.71
Target Profit
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)
$57,000.00 $105,000.00 $1,045.60 154.93 30.99 123.95 $3,100.00 52.26 10.45 41.81
Total variable cost $2,520.00 $1,938.00
Contribution Margin $1,380.00 $962.00
Weighted sales mix 0.2 0.8
Weighted average contribution margin $276.00 $769.60
1 of 1
Tristan Chambers 11502299
Tutorial 9 - CVP Spreadsheet
Shine Alloys Ltd is a retailer of Alloy wheels for Hotrods imported from Southern California. There are a vast number of different types of Alloy wheels imported and retailed by Shine because customers want their Hotrod to look unique. For the purpose of cost control and planning Shine classifies its stock according to whether it is a custom design (high price) or limited edition (low price) alloy. Information concerning each category is provided below. Note each category refers to a set of four alloys. > Requirement 4 (Scenario planning)
The sales price, variable costs and fixed costs are all based on estimates. Shine Ltd management would like to see how much the breakeven point and target sales changes based on the following independent scenarios: d A competitor enters the Australia market selling imported wheels from Detroit, Michigan. To remain competitive Shine Ltd is forced to reduce the price of Custom design sets to $3,000 and Limited edition sets to $2,000
Product Custom design set Limited edition set
Sale price $3,000 $2,000
Cost price $2,400 $1,900
Sales commission (based on sale price) 5% 2%
Variable cost (alloy set) $2,400 $1,900
Variable cost (sales commission) $120.00 $38.00
In a normal month, the Management of Shine expects that 30% of total sales will be Custom design sets and the remaining Limited edition sets. Fixed costs per month: $57,000
Contribution margin
Product Custom design set Limited edition set
Sale price $3,000 $2,000
Break Even
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)
$57,000.00 $187.40 304.16 91.25 212.91 $2,300.00 24.78 7.43 17.35
Target Profit
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)
$57,000.00 $105,000.00 $187.40 864.46 259.34 605.12 $2,300.00 70.43 21.13 49.30
Total variable cost $2,520.00 $1,938.00
Contribution Margin $480.00 $62.00
Weighted sales mix 0.3 0.7
Weighted average contribution margin $144.00 $43.40
1 of 1...