Question

In: Accounting

CVP Modeling project The purpose of this project is to give you experience creating a profitability...

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

Product #1 Hand-held pliers
Unit CM
CM %
Breakeven point:
-in units
-in sales revenue
Target profit volume:
-in units

-in sales revenue

Cornell Tool Manufacturingg
Pro Forma Contribution Margin Income Statement
For the fiscal year ended December 31
Total
Sales revenue $                                                 -  
Less: variable costs $                                                 -  
Contribution margin $                                                 -  
Less: fixed costs $                                                 -  
Operating income

$                                                 -  

Questions:
(1) If Cornell achieves its expected monthly sales in units, will the company breakeven?
Answer:
(2) Based on your projections, will Cornell achieve its target profit level for the first fiscal year?
Answer:
(3) Based on your projections, by how much will Cornell exceed and/or miss its target profit?
Answer:
(4) If Cornell achieves its expected monthly sales in units, how many months will it take the company to breakeven?
Answer:
(5) Based on your projections, what will be the company's margin of safety (in dollars)?
Answer:


Solutions

Expert Solution

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

Related Solutions

Project 3 Details The purpose of this project is to give you experience with creating and...
Project 3 Details The purpose of this project is to give you experience with creating and using custom objects. In it, you will make a couple custom classes that work in tandem with each other, and call them in your main function. For this project we'll be making something kind of like the Chatbot java file we made in class. You will create a Chatbot class that will contain a number of variables and functions. As you can imagine, a...
Purpose This project is meant to give you experience with sorting, binary searching, and Big-Oh complexity....
Purpose This project is meant to give you experience with sorting, binary searching, and Big-Oh complexity. Objective "Write in Java please" Your goal is to take a book, as a large text file, and build a digital “concordance”. A concordance is like an index for a book in that it contains entries for words in the book, but it also includes passages from the book containing that word. For example, a query for the word Dormouse in Alice in Wonderland...
The purpose of this exercise is to give you experience in writing condition-controlled loops. Create a...
The purpose of this exercise is to give you experience in writing condition-controlled loops. Create a code in Python that will print monthly loan amortization schedule given the loan amount (P), APR i.e. Annual Percentage Rate (r=APR in %/12), loan terms in number of months (n). Please use these formulas: Monthly Payment (A) = P * ((r * (1 + r)**n)/(((1+r)**n)-1))            Monthly Interest Pmt (mIP)= r * Total Remaining Balance Monthly Principal Pmt (mPP) = A –...
Purpose: This lab will give you experience modifying an existing ADT. Lab Main Task 1: Modify...
Purpose: This lab will give you experience modifying an existing ADT. Lab Main Task 1: Modify the ListInterface Java interface source code given below. Change the name of ListInterface to ComparableListInterface, and have ComparableListInterface inherit from the built-in Java interface Comparable. Note that Comparable has a method compareTo(). compareTo() must be used in programming logic you will write in a method called isInAscendingOrder() (more about isInAscendingOrder() is mentioned further down in the lab description). You can find a brief summary...
Purpose This assignment should give you experience in using file descriptors, open(), close(), write(), stat() and...
Purpose This assignment should give you experience in using file descriptors, open(), close(), write(), stat() and chmod(), perror(), and command line arguments. Program Write a C++ program that will allow you to add messages to a file that has NO permissions for any user. A Unix system has many files that have sensitive information in them. Permissions help keep these files secure. Some files can be publicly read, but can not be altered by a regular user (ex.: /etc/passwd). Other...
How can CVP Analysis be used to predict future costs and profitability? Describe how CVP analysis...
How can CVP Analysis be used to predict future costs and profitability? Describe how CVP analysis is used, or could be used, at your current place of employment. If you have not worked for a company that might use CVP Analysis, you may choose a well-known company and describe how you envision that company using CVP Analysis. Try to discuss a concept associated with CVP not already addressed by your classmates. Consider using an article to summarize or apply the...
How can CVP Analysis be used to predict future costs and profitability? Describe how CVP analysis...
How can CVP Analysis be used to predict future costs and profitability? Describe how CVP analysis is used, or could be used, at your current place of employment. If you have not worked for a company that might use CVP Analysis, you may choose a well-known company and describe how you envision that company using CVP Analysis. Try to discuss a concept associated with CVP not already addressed by your classmates. Consider using an article to summarize or apply the...
How can CVP Analysis be used to predict future costs and profitability? Describe how CVP analysis...
How can CVP Analysis be used to predict future costs and profitability? Describe how CVP analysis is used, or could be used, at your current place of employment. If you have not worked for a company that might use CVP Analysis, you may choose a well-known company and describe how you envision that company using CVP Analysis.
Data modeling is a key component to creating a database. There are all different aspects of...
Data modeling is a key component to creating a database. There are all different aspects of data modeling, with concerns from various things to the methodology of modeling, to software, to interfaces used between each model. Paper: Write a 3 page paper (at model 1.2 spacing, 12 pt font, 1 inch margins with at most one half of the paper including a figure, citations not included) on some aspect of data modeling in industry. You must use reliable academic and...
Data modeling is a key component to creating a database. There are all different aspects of...
Data modeling is a key component to creating a database. There are all different aspects of data modeling, with concerns from various things to the methodology of modeling, to software, to interfaces used between each model. Paper: Write a 3 page paper (at model 1.2 spacing, 12 pt font, 1 inch margins with at most one half of the paper including a figure, citations not included) on some aspect of data modeling in industry. You must use reliable academic and...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT