Directions CVP Modeling project The purpose of this project is to give you exper
ID: 2543855 • Letter: D
Question
Directions CVP Modeling project The purpose of this project is to give you experience creating a profitability analysis that can be used to determine the effects of changing business conditions on a client's financial position. Managers often want to know the production level where profits earned from a product cover the cost of resources used to create it. Break-even analysis is how we determine this level. The point at which total sales revenues covers the costs of committed resources is called the break-even point. In addition to knowing the break-even point, managers may also want to know the point at which sales volume reaches a pre-set target- profit level. You have been hired by Comell to build a CVP model that will help the company understand the impact of business conditions on its operating income. In your model, all of the original assumptions will be listed one area of the spreadsheet (blue box). All other calculations in the model will reference the assumptions (blue box) such that if any assumption changes, the effect will ripple through the entire model. To accomplish this goal, you will use FORMULAs, rather than numbers, in every other cell in the worksheet. In other words, the only place you will type/hard-key numbers is the blue assumptions box. This tool will help you perform both of these calculations. The first is break-even analysis where your goal is to determine how many units you must sell to recover all of your fixed costs. The second is target-profit analysis where your goal is to determine how many units you must sell to reach a pre-defined profit level. The difference between the two is that at break-even your target-profit is zero, whereas when you specify a target-profit that is greater than zero, you are setting your goal above the break-even point. FORMATTING conventions to use throughout this project: - Round all UNITS to the nearest whole unit. Use the "decrease decimals" button on your tool bar rather than the Rounding function. - Show all MONETARY amounts as dollars and cents. Round to the nearest cent. (SX.XX). Use the "decrease decimals" button rather than the rounding function. - Show all percentages as %, not as decimals. (x%, not .xx) - Right justify all cells (numbers should be to the right side of the cell, not in the middle or left) Your challenge will be to use Excel in such a way that any changes to the assumptions will correctly ripple through the entire profitability analysis. If executed properly. the client should be able to use this spreadsheet over and over, using different "what if" assumptions. 1) Complete the assumptions (blue box) based on the data about Cornell's business. Identify and list all variable costs separately and all fixed costs separately before finding the total for each type of cost. Business Description Cornell Tool Manufacturing wants to begin selling a new pair of hand-held pliers in the upcoming fiscal year. They want to know how many hand-held pliers they will have to sell in order to break-even on this investment in materials and equipment Management has provided you with the following data: 2) Complete the Product Analysis (yellow boxes) assuming Jake only sells the hand-held pliers. 3) Complete the pro forma Contribution Margin Income Statement for the fiscal year (green box) assuming that the company achieved its expected unit monthly sales. 4) Answer the questions asked of you in the "Questions" box under the pro forma income statement. 5) Change the name of the starting file worksheet to "Original Assumptions". Annual Fixed costs: Metal molding maching: $100,000 Plastic grip molder: $15,000 Sander: $5,000 Employee costs: $0 6) Make sure you have cleaned up your worksheet using the formatting conventions listed above. 7) Go to the "Advising client" worksheet and follow the directions found there. Variable costs (per unit): Packaging material: $1.00 Raw material: $2.00 Grip material: $0.50 Shipping: $0.50 Sales commission: 5% of sales Since this is a new company, the only employee currently being paid is Sally, the marketing manager. Sally estimates that the company can sell its new pliers for $20.00 per unit. She further projects that they will, on average, produce and sell 1,600 units per month. The goal is that they will break-even and start to earn a profit within the first year. The target-profit level for the end of the first fiscal year is $150,000.Explanation / Answer
Cornell Tool Manufacturing
Assumptions
Product #1:
Hand-held pliers
Sales price per unit
$20
Variable cost per unit:
packaging material
$1
Raw material
$2
Grip material
$0.50
Shipping
$0.50
Sales commission -5% of sales
$1
Total variable cost per unit
$5
Annual Fixed Costs:
Metal molding machine
$100,000
Plastic grip molder
$15,000
Sander
$5,000
Employee costs
$0
Total fixed costs
$120,000
Expected monthly sales in units
1,600
Target profit level for the first fiscal year
$150,000
Product #1
Hand-held pliers
Unit CM
$15
CM%
75%
Break-even point:
in units
8,000
in sales revenue
$160,000
Target profit volume:
in units
18,000
in sales revenue
$360,000
Notes –
Unit CM = $20 - $5 = $15
= $15/$20 = 75%
= $120,000/$15 = 8,000 units
= $120,000/75% = $160,000
= ($150,000 +$120,000)/$15 = 18,000 units
= (150,000 + 120,000)/75% = $360,000
Cornell Tool Manufacturing
Pro Forma Contribution Margin Income Statement
for the fiscal year ended December 31
Total
Sales revenue
$384,000
Less: variable costs
$96,000
Contribution margin
$288,000
Less: Fixed costs
$120,000
Operating income
$168,000
Annual unit sales = expected monthly sales x 12 months
= 1,600 units x 12 = 19,200 units
Sales revenue = $20 x 19,200 units = $384,000
Variable cost = $5 x 19,200 units = $96,000
Yes, the company will break-even if Cornell achieves its expected monthly sales in units.
Expected monthly sales units 1,600
Break-even monthly sales in units = 8,000/12 = 667 units
Hence, expected monthly sales in units is higher than the break-even point, and the company break-evens at its expected sales volume.
Yes, the company will achieve its target profit level for the first fiscal year.
Target profit - $150,000
Operating profit in first fiscal year = $168,000.
Answer
Based on the given projections, Cornell would exceed its target profit by $18,000.
$168,000 - $150,000 = $18,000
Cornell needs less than one month to reach break-even point.
Explanation – monthly break-even point in unit sales = 8,000/12 = 667 units
Expected monthly sales in units = 1,600
So, the company needs less than one month to reach break-even point in unit sales.
Margin of safety = actual sales – break-even sales
Actual sales = $384,000
Break-even sales = $160,000
MOS = $384,000 - $160,000 = $224,000
Impact of Cost Changes –
Product #1:
Hand-held pliers
cost changes
Sales price per unit
$20
Variable cost per unit:
packaging material
increase by 300%
$4
Raw material
increased by 50%
$3
Grip material
increased by 200%
$1.50
Shipping
increased by 400%
$2.50
Sales commission -5% of sales
No change
$1
Total variable cost per unit
$12
Product #1
Hand-held pliers
Unit CM
$8
CM%
40%
Break-even point:
in units
15,000
in sales revenue
$300,000
Cornell Tool Manufacturing
Pro Forma Contribution Margin Income Statement
for the fiscal year ended December 31
Total
Sales revenue
$384,000
Less: variable costs
$230,400
Contribution margin
$153,600
Less: Fixed costs
$120,000
Operating income
$33,600
Answer – new operating income = $33,600
Answer = CM% = new unit CM/unit sales price
New unit CM = $20 - $12 = $8
CM% = $8/$20 = 40%
MOS = actual sales – break-even sales
= $384,000 - $300,000 = $84,000
New
Original
Change
operating income
$33,600
$168,000
($134,400)
CM%
40%
75%
-35%
Margin of safety
$84,000
$224,000
($140,000)
Brief Explanation:
The increases in variable cost components resulted in a decrease in operating income, CM% and margin of safety. Though, Cornell continues to reach the break-even point with the changes in variable cost, its margin of safety decreases as the break-even point in unit sales increases. The company’s monthly break-even point in units = 15,000/12 = 1,250 units, which is less than the expected monthly sales of 1,600 units.
The changes resulted in operating income to decrease by $134,400 and the company cannot reach its yearly target income level of $150,000.
Product #1:
Hand-held pliers
cost changes
Sales price per unit
$20
Variable cost per unit:
packaging material
$1
Raw material
$2
Grip material
$0.50
Shipping
$0.50
Sales commission -5% of sales
decreased to 2.5%
$0.50
Total variable cost per unit
$4.50
Annual Fixed Costs:
Metal molding machine
$100,000
Plastic grip molder
$15,000
Sander
$5,000
Employee costs
$18,000
Total fixed costs
$138,000
Note:
Change in the salary of Marketing manager –
Variable cost 2.5% of revenue = $20 x 2.5% = $0.50
Fixed salary - $1,500 per month
Per annum = 1,500 x 12 = $18,000
Cornell Tool Manufacturing
Pro Forma Contribution Margin Income Statement
for the fiscal year ended December 31
Total
Sales revenue
$384,000
Less: variable costs
$86,400
Contribution margin
$297,600
Less: Fixed costs
$138,000
Operating income
$159,600
New
Original
Change
operating income
$159,600
$168,000
($8,400)
CM%
77.5%
75%
2.5%
Margin of safety
$205,935
$224,000
($18,065)
Product #1
Hand-held pliers
Unit CM
$15.50
CM%
77.5%
Break-even point:
in units
8,903
in sales revenue
$178,065
Answer – new operating income = $159,600
Answer = CM% = new unit CM/unit sales price
New unit CM = $20 - $4.50 = $15.50
CM% = $15.5/$20 = 77.5%
New fixed cost = $138,000
CM ratio = 77.5%
Break-even sales = $138,000/77.5% = $178,065
MOS = actual sales – break-even sales
= $384,000 - $178,065 = $205,935
Brief Explanation:
The change in marketing manager’s salary resulted in a decrease in variable cost but increased fixed cost by $18,000 per annum.
The increase in fixed cost resulted in break-even sales to increase marginally by 903 units per year.
Despite the changes, the company exceeds its target income of $150,000 per annum by $9,600.
Assumptions
Product #1:
Hand-held pliers
Sales price per unit
$20
Variable cost per unit:
packaging material
$1
Raw material
$2
Grip material
$0.50
Shipping
$0.50
Sales commission -5% of sales
$1
Total variable cost per unit
$5
Annual Fixed Costs:
Metal molding machine
$100,000
Plastic grip molder
$15,000
Sander
$5,000
Employee costs
$0
Total fixed costs
$120,000
Expected monthly sales in units
1,600
Target profit level for the first fiscal year
$150,000
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.