Excel What If Analysis

Need your ASSIGNMENT done? Use our paper writing service to score better and meet your deadlines.


Order a Similar Paper Order a Different Paper

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.

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

A chart shows the relationship between total expenses and volume with three callouts. The first reading “break-even point” points toward an interlinking junction between loss and profit. The second reading “variable expenses” points toward a bracketed space between profit and a dotted line of loss. The third reading “fixed expenses” points toward a bracketed space between the dotted lines in loss to the end of the 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. 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. 2 In the Documentation worksheet, enter your name and the date.
  3. 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 divisionA screenshot shows a spreadsheet with four callouts. The first reading “revenue from selling 12,000 mowers at an average price of $335 per mower” points toward the cell with the title Average Price per Unit. The second reading “variable expenses directly related to the cost of producing 12,000 mowers” points toward a bracketed set of reading and inputs. The third reading “fixed expenses regardless of how many mowers are actually produced” points toward bracketed set of inputs with the total fixed expenses. The fourth reading “net income calculated by subtracting the company’s total expenses from its revenue” points toward bracketed set of inputs carrying the Net Income, Revenue and Expenses.

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.

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. 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. 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. 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. 1 On the ribbon, click the Data tab.
  2. 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. 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. 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. 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. 6 Click the OK button. The Goal Seek Status dialog box opens once Excel finds a solution.
  7. 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 evenSales required to break even
  8. 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.

"Is this question part of your assignment? We can help"

ORDER NOW
Writerbay.net

Need help with an assignment? We work for the best interests of our clients. We maintain professionalism and offer brilliant writing services in most of the fields ranging from nursing, philosophy, psychology, biology, finance, accounting, criminal justice, mathematics, computer science, among others.

Get a 15% discount on your order using the following coupon code SAVE15


Order a Similar Paper Order a Different Paper