Sensitivity Analysis Using Excel

(d) Using your Excel model, use GOAL SEEK to find how many pounds per day that the company needs to sell to break even? (5 points)

Sensitivity analysis begins with the base case (or for this analysis, the “most likely case”) developed using expected values for all uncertain variables. The uncertain variables used in this analysis are procedures per day, average net revenue, and building/equipment salvage value.

5. Determine the necessary sales in unit and dollars to break-even or attain desired profit using the break-even formula.

Determine the unit break-even point, assuming fixed costs are $60,000 per period, variable costs are $16.00 per unit, and the sales price is $25.00 per unit.

2. Uncertainty/sensitivity analysis: The effects of uncertainty are considered in the profitability analyses. Examples include calculating or discussing the impact of various potential revenue levels on product line profit margins, and applying the probabilities of successfully negotiating postal outlet and lottery booth contracts.

Assume that next year management wants the company to earn a minimum profit of $162,000. How many units be sold to meet this target profit figure? [3 points]

ffman Trucking Balance Sheet / Horizontal Analysis December 31st, 2006 | In Thousands$ | Increase / (Decrease)(Rounded) | | 2006 | 2005 | Amount | Percent | Assets | Current Assets | | Cash& Cash Equivalents | 51,993 | 38,893 | 13,100 | 34% | Accounts Receivable | 56,292 | 57,441 | (1,149) | (2%) | Prepaid Expenses & Supplies | 3,443 | 3,343 | 100 | 3% | Total Current Assets | 111,728 | 99,677 | 12,051 | 12% | |

These number will be used for predicting future financial statements later in this case study.

LARGE Units sold (excluding specials) Units sold Units embroidered Units dyed Number of orders Number of shipments Number of invoices Sales dollars Acc. Bal. > 60 days 99.770 100.250 5.959 20.536 133 147 112 308.762 1

The revenue is $600,600*1.2= $720,720. The variable cost changes as sales increases and fixed cost stays the same, the gross profit is $175,500. After tax, the net income is $100,557.

A careful analysis on the sensitivity is necessary in order to make sound business decisions.

This question gives students an opportunity to exercise their ability to interpret break-even analyses. Key teaching points should include explaining the preparation of a break-even chart, the interpretation of the break-even volume (938,799 hectoliters [HL]), and the comparison of the break-even volume to the current volume (1,173,000 HL). Another key point is that the chart in case Exhibit 5 is relevant only for the current cost structure of the company—if variable costs increase or the plant expansion is approved, the break-even volume will rise. Finally, students should be aided in understanding that “break-even” refers to operating profit, not free cash flow. The typical use of the break-even chart ignores taxes, investments, and the depreciation tax shield.

Sensitivity analysis allows a change in one particular variable of simulation. This shows how a project is affected by the change. It shows us what can happen in a project with different input

1994 Liabilities and Equity Short-term borrowings Accounts payable Progress collections and price adjustments accrued Dividends payable Taxes accrued Other costs and expenses accrued Current liabilities Long-term borrowings Other liabilities Total liabilities Minority interest in equity of consolidated affiliates Preferred stock Common stock Amounts received for stock in excess of par value Retained earnings Deduct common stock held in treasury Total shareowners’ equity Total liabilities and equity $644.9 696.0 1,000.5 72.8 337.2 1,128.1 $3,879.5 1,195.2 518.9 5,593.6 $ 71.2 $ — $465.2 414.5 3,000.5 $3,880.2 (175.9 ) $3,704.3 $9,369.1 $665.2 673.5 718.4 72.7 310.0 1,052.6 $3,492.4 917.2 492.1 4,901.7 50.1 — $463.8 409.5 2,683.6 $3,556.9 (184.5 ) $3,372.4 $8,324.2 $ $120.6 376.2 300.5 58.7 318.3 392.6 $1,566.9 364.1 221.0 2,152.0 41.4 — $455.8 266.9 1,384.5 $2,107.2 — $2,107.0 $4,300.6 1993 1985

YEAR 0 2009 1 2010 2 2011 3 2012 4 2013 5 2014 6 2015 7 2016 8 2017 9 2018 10 Initial Investment Gross Revenue 2 COGS 3 Add'l revenue Less: COGS Loan down payment 4 Loan repayment Depreciation Additional workers Land square required Moving cost 5 Operating Expenses Total Expenses Net Income Before Tax Income Tax Net Income After Tax After Tax Cash Flow ATCF Cummulative ATCF NPV through Year N