Paste Cell Styles 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 eaned 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-proft 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 6 over and over, using different what if assumptions. 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 1materials and equipment. Management has provided you with the folilowing data: Annual Fixed costs: Metal molding maching: $120,000 Plastic grip molder: $5,000 s Sander: $25,000 26 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 32 3Since this is a new company, the only employee currenty being paid is Sally, the marketing manager. Sally 4estimates that the company can sell its new pliers for $20.00 per unit. She further projects that they will, on 35 average, produce and sell 2,000 units per month. The goal is that they will break-even and start to eam a profit 6 within the first year. The target-profit level for the end of the first fiscal year is $190,000. 38 42 43 45 47 Insert Page LayoutFormulas Data Review View Shape FormatA A., %- ,conditional Formatting , -a . Format as Table Alignment Number Cells Editing Font Cell Styles a. 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 ill reference the changes, the effect will ripple through the entire model. To accomplish this goal, you will use FORMULAs, rather than numbers, in every other cel in the worksheet. In other words, the only place you will type/hard-key numbers is the blue assumptions box. assumptions (blue box) such that if any assumption 1) Complet separately an all fixed costs separately before finding the total for each type of cost. te the assumptions (blue box) based on the data about Comell's business. Identify and list all variable costs 2) Complete the Product Analysis (yellow boxes) assuming Jake only sells the hand-held pliers. 3) Complete the pro forma Contributdon Margin Income Statement for the fiscal year (areen 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 fle worksheet to "Original Assumptions 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. RMA all UNITS to the nearest whole unit. Use the "decrease decimals" button on your tool bar rather than the Rounding how a MONETARYunts as colrs and cents. Round to the cx),se the decrease decimals function. button rather than the rounding function -Show all percentages as %, not as decimals. (0%, not xx) -Right justity all cells (numbers should be to the right side of the cell, not in the middle or left)