Description
This assignment will be completed using the attached spreadsheet. I have a list of steps that I will attach showing the full steps to be completed. These assignments usually take me one around one hour. I have provided example steps showing below for the first few pages of the assignment to give you an idea of what is needed. The only steps that need to be completed are the ones numbered. I would like to request the tutor andythewxman. I can provide/attach the full assignment steps today around 2PM once I get to a scanner that will scan the assignment steps in to my e-mail.
10-2b Exploring the Break-Even Point
The point where total revenue equals total expenses is called the break-even pointbreak-even pointThe point where total revenue equals total expenses. break-even point The point where total revenue equals total expenses. . For this reason, CVP analysis is sometimes called break-even analysisbreak-even analysisAnother term for CVP analysis. break-even analysis Another term for CVP analysis. . The more mowers that Coltivare Tool sells above the break-even point, the greater its profit. Conversely, when sales levels fall below the break-even point, the company loses money.
You can illustrate the break-even point by graphing revenue and total expenses against sales volume. The break-even point occurs where the two lines cross. This type of chart is called a cost-volume-profit (CVP) chartcost-volume-profit (CVP) chartA branch of financial analysis that studies the relationship between expenses, sales volume, and profitability. cost-volume-profit (CVP) chart A branch of financial analysis that studies the relationship between expenses, sales volume, and profitability. . As shown in Figure 10-4, a CVP chart shows the relationship between total expenses and total revenue.
Figure 10-4Break-even point in a CVP chart
Irena has prepared an income statement for the power mower division of Coltivare Tool that includes projected revenue, variable expenses, and fixed expenses based on the previous year’s sales. You’ll review the worksheet with this data. Later, you will use this data to calculate the company’s break-even point.
To review the income statement for Coltivare Tool:
- 1 Open the Coltivare workbook located in the Excel10 > Module folder included with your Data Files, and then save the workbook as Coltivare Tool in the location specified by your instructor.
- 2 In the Documentation worksheet, enter your name and the date.
- 3 Go to the Income worksheet and review its contents and formulas. See Figure 10-5. Figure 10-5Revenue and expenses from Coltivare Tool’s mower division
As itemized in the Income worksheet, the company projects that it will sell 12,000 mowers at an average price of $335 per mower, generating more than $4 million in revenue. The variable expenses involved in producing 12,000 mowers are $2.7 million. The company’s fixed expenses are $1.02 million. Based on this sales volume, Coltivare Tool would generate $300,000 in revenue.
10-2c Finding the Break-Even Point with What-If Analysis
What-if analysis lets you explore the impact of changing different values in a worksheet. You can use such an analysis to explore the impact of changing financial conditions on a company’s profitability. Irena wants to know what the impact would be if the number of mowers Coltivare Tool produces and sells rises to 15,000 or falls to 9000.
To perform what-if analysis for different sales volumes:
- 1 In cell B6, enter 15,000 to change the units produced and sold value. Under this sales volume, the net income of the company shown in cell B28 increases to $630,000.
- 2 In cell B6, enter 9,000. If the units produced and sold drop to 9000 units, the net income shown in cell B28 becomes –$30,000. The company will lose money with that low of a sales volume.
- 3 In cell B6, enter 12,000 to return to the original units produced and sold projection.
Irena wants to know how low sales can go and still maintain a profit. In other words, what is the sales volume for the break-even point? One way of finding the break-even point is to use Goal Seek. Recall that Goal Seek is a what-if analysis tool that can be used to find the input value needed for an Excel formula to match a specified value. In this case, you’ll find out how many mowers must be sold to set the net income to $0.
To use Goal Seek to find the break-even point:
- 1 On the ribbon, click the Data tab.
- 2 In the Forecast group, click the What-If Analysis button, and then click Goal Seek. The Goal Seek dialog box opens with the cell reference in the Set cell box selected.
- 3 In the Income worksheet, click cell B28 to replace the selected cell reference in the Set cell box with $B$28. The absolute reference specifies the Net Income cell as the cell whose value you want to set.
- 4 Press the Tab key to move the insertion point to the To value box, and then type 0. This specifies that the goal is to set the net income value in cell B28 to 0.
- 5 Press the Tab key to move the insertion point to the By changing cell box, and then click cell B6 in the Income worksheet to enter the cell reference $B$6. The absolute reference specifies that you want to reach the goal of setting the net income to 0 by changing the units produced and sold value in cell B6.
- 6 Click the OK button. The Goal Seek Status dialog box opens once Excel finds a solution.
- 7 Click the OK button to return to the worksheet. The value 9,273 appears in cell B6, indicating that the company must produce and sell about 9,273 mowers to break even. See Figure 10-6.Figure 10-6Sales required to break even
- 8 In cell B6, enter 12,000 to return to the original units produced and sold projection.
Irena wants to continue to analyze the company’s net income under different sales assumptions. For example, what would happen to the company’s net income if sales increased to 16,000 mowers? How much would the company lose if the number of sales fell to 8,000 mowers? How many mowers must the company sell to reach a net income of exactly $50,000? You could continue to use Goal Seek to answer these questions, but a more efficient approach is to use a data table.
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
I have done everything but the review assigment that needs the Snow.xlsx, Crystal.xlsx, and Travel.xlsx, and others, perhaps.I am marking this as final, but if you need the other exercises done and have the files, just tell me.
8/6/2017
Coltivare Tool
Author
Date
Purpose
To perform a break-even analysis of the Coltivare
Tool line of power lawn mowers and to determine
the product mix that optimizes company profits
20170806143803coltivare.xlsx
Page 1
Documentation
Coltivare Tool
Income Analysis
Revenue
Units Produced and Sold
Average Price per Unit
Total Revenue
Variable Expenses
Units Produced
Average Material Cost per Unit
Total Material Cost
Average Manufacturing Cost per Unit
Total Manufacturing Cost
Total Variable Expenses
Fixed Expenses
Salaries and Benefits
Shipping and Distribution
Stocking and Storage
Miscellaneous
Total Fixed Expenses
$
12.000
$335
4.020.000
$
12.000
$155
1.860.000
$70
840.000
2.700.000
$
$
775.000
95.000
85.000
65.000
1.020.000
Break Even Analysis
Units Sold
Revenue
Expenses
Net Income
12.000 $ 4.020.000 $ 3.720.000 $
300.000
2.000 $
670.000 $ 1.470.000 $
(800.000)
4.000 $ 1.340.000 $ 1.920.000 $
(580.000)
6.000 $ 2.010.000 $ 2.370.000 $
(360.000)
8.000 $ 2.680.000 $ 2.820.000 $
(140.000)
10.000 $ 3.350.000 $ 3.270.000 $
80.000
12.000 $ 4.020.000 $ 3.720.000 $
300.000
14.000 $ 4.690.000 $ 4.170.000 $
520.000
16.000 $ 5.360.000 $ 4.620.000 $
740.000
18.000 $ 6.030.000 $ 5.070.000 $
960.000
$7.000.000
$2.500.000
$6.000.000
$2.000.000
$5.000.000
$
4.020.000
3.720.000
300.000
$
$
$
$
$
$
$
$
$
300
(870.000)
(720.000)
(570.000)
(420.000)
(270.000)
(120.000)
30.000
180.000
330.000
$
$
$...