CVP Modeling project The purpose of this project is to give you experience creat
ID: 2542764 • Letter: C
Question
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: $100,000 Plastic grip molder: $15,000 Sander: $5,000 Employee costs: $0 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,000Explanation / Answer
ASSUMPTIONS Product #1 Hand - Held Pliers Sales Price per Unit $20.00 Variable cost per unit : Packaging Material $1.00 Raw material $2.00 Grip material $0.50 Shipping $0.50 Sales Commission $1.00 Total variable cost $5.00 Annual fixed costs: Metal molding machine 100000 Plastic grip molder 15000 Sander 5000 Employe costs 0 Total Fixed Costs 120000 Expected monthly sales in units 1600 Target profit level for the first year 150000 Product #1 Hand - Held Pliers Unit CM $15.00 CM % 75% Break-even point -- in units 8000 -- in sales revenue 200000 Target profit volume -- in units 18000 -- in sales revenue 360000 Cornell Tool Manufacturing Proforma Contribution Margin Income Statement Sales Revenue 384000 Less: Variable costs 96000 Contribution margin 288000 Less: Fixed Costs 120000 Operating Income 168000 Questions (1) If Cornell achieves expected monthly sales in units will the company break even Answer : Yes (2) Based on your projections, will Cornell achieve its target profit level for first year Answer : Yes (3) Based on your projections, by how much will Cornell exceed and/or miss its target profit Answer: It will exceed target profit by $18,000 (4)If Cornell achieves its monthly sales in units, how many months will it take the company to break-even. Answer : 5 months. (8,000 / 1,600) (5) Based on your projections, what will be the Company's margin of safety Answer : $184,000 ( $384,000 - $200,000)