In: Accounting
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.
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.
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.
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:
Annual Fixed costs:
Metal molding maching: $120,000
Plastic grip molder: $5,000
Sander: $25,000
Employee costs: $0
Variable costs (per unit):
Packaging material: $0.50
Raw material: $1.75
Grip material: $1.00
Shipping: $0.75
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 2,000 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 $190,000.
Directions
You have been hired by Cornell 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.
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. ($x.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)
ASSUMPTIONS | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Product #1: | Hand-held pliers | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sales price per unit | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Variable costs per unit: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Packaging material | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Raw material | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Grip material | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Shipping | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sales commission | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Total variable cost per unit | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Annual fixed costs: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Metal molding machine | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Plastic grip molder | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sander | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Employee costs | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Total fixed costs | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Expected monthly sales in units | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Target profit-level for the first fiscal year
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sale Price | 20 per unit | ||||||
Production monthly | 2000 units | ||||||
Sale monthly | 2000 units | ||||||
Yearly production and sales in units | 24000 | ||||||
Annual Fixed Cost | |||||||
Metal Moulding Machine | 120000 | ||||||
Plastic Grip molder | 5000 | ||||||
Sander | 25000 | ||||||
Employee Cost | 0 | ||||||
Variable Cost per unit | |||||||
Pacakaging material | 0.5 | ||||||
Raw Material | 1.75 | ||||||
Grip Material | 1 | ||||||
Shipping | 0.75 | ||||||
Sales Commission | 5% of sales | ||||||
Total Sales Value | 480000 | ||||||
Sales commission 5% of sales | 24000 | ||||||
Sales Commission per unit | 1 | ||||||
Expected Target Profit for first year | 190,000 | ||||||
ASSUMPTIONS | |||||||
Product #1: | Hand-held pliers | ||||||
Sales price per unit | 20 | ||||||
Variable costs per unit: | |||||||
Packaging material | 0.5 | ||||||
Raw material | 1.75 | ||||||
Grip material | 1 | ||||||
Shipping | 0.75 | ||||||
(Total yearly Sales *sales commission%)/total yearly sales in units | Sales commission | 1 | |||||
Total variable cost per unit | 5 | ||||||
Annual fixed costs: | |||||||
Metal molding machine | 120000 | ||||||
Plastic grip molder | 5000 | ||||||
Sander | 25000 | ||||||
Employee costs | 0 | ||||||
Total fixed costs | 150000 | ||||||
Expected monthly production and sales in units | 2000 | ||||||
Selling price in $ | 20 | ||||||
Target profit-level for the first fiscal year | |||||||
Product #1 | Hand-held pliers | ||||||
Contribution Margin= Sales per unit - Variable cost per unit | Unit CM | 15 | |||||
Contribution Margin Ratio= (Contribution per unit/Sale price per unit)*100 | CM % | 75% | |||||
Breakeven point: | |||||||
Fixed Cost/Contribution margin per unit | -in units | 10,000 | |||||
Break even in units* selling price per unit | -in sales revenue | 200,000 | |||||
Target profit volume: | |||||||
Target Profit in Sales Revenue= Contribution margin less fixed cost | -in units | ||||||
-in sales revenue | |||||||
Cornell Tool Manufacturingg | |||||||
Pro Forma Contribution Margin Income Statement | |||||||
For the fiscal year ended December 31 | |||||||
Units Yearly | 24000 | ||||||
Total | |||||||
Selling price per unit*Volume (20*2000*12) | Sales revenue | 480,000 | |||||
Variable cost per unit*volume | Less: variable costs | 120,000 | |||||
Contribution= [Sales-Variable Cost ] | Contribution margin | 360,000 | |||||
Less: fixed costs | 150,000 | ||||||
Operating income | 210,000 | Profit | |||||
Questions: | |||||||
(1) If Cornell achieves its expected monthly sales in units, will the company breakeven? | |||||||
Answer: | Yes company will breakeven at $ 200000 sales | ||||||
(2) Based on your projections, will Cornell achieve its target profit level for the first fiscal year? | |||||||
Yes | |||||||
Answer: | It is achieving higher than the target profit of $190000 | ||||||
(3) Based on your projections, by how much will Cornell exceed and/or miss its target profit? | |||||||
Answer: | Will exceed the target profit of $ 190000 | ||||||
No. of months to break even= Break even Sales in unit/monthly sales in units | (4) If Cornell achieves its expected monthly sales in units, how many months will it take the company to breakeven? | 5 | |||||
Answer: | 5 months | ||||||
Margin of Safety= Total Projected Sales - Break Even Sales | (5) Based on your projections, what will be the company's margin of safety (in dollars)? | ||||||
(480000-200000) | |||||||
Answer: | $280,000 | ||||||