Student Version

This case illustrates a complete capital budgeting analysis, including cash flow analysis, and

profitability measures. Note that the model extends to Column I.

The model consists of a complete base case analysis$--$no changes need to be made to the existing

MODEL$-$GENERATED DATA section. However, values in the INPUT DATA section of the student

spreadsheet have been replaced by zeros. Students must select appropriate input values and enter

them into the cells with values colored red. After this is done, any error cells will be corrected and

the base case solution will appear. The KEY OUTPUT section includes the most important output

from the MODEL$-$GENERATED DATA section.

INPUT DATA: KEY OUTPUT:

Land initial cost $$300,000$ NPV $($$$533,095)-$$$533,0950\%1\%$

Land opportunity cost $($and salvage value$)$ $$400,000$ IRR $9\mathrm{.}1\%0\%-668143\mathrm{.}6564-280210\mathrm{.}51$

Building$/$equipment cost $$20,000,000$ MIRR $9\mathrm{.}4\%1\%-880149\mathrm{.}1224-492215\mathrm{.}97$

Build$/$equipment salvage value $$12,000,000$ Payback $4\mathrm{.}42\%-1096207\mathrm{.}365-708274\mathrm{.}22$

Procedures per day $203\%-1316377\mathrm{.}217-928444\mathrm{.}07$

Average net patient revenue per procedure $$2,0004\%-1540717\mathrm{.}966-1152784\mathrm{.}8$

Labor costs $$3,200,0005\%-1769289\mathrm{.}357-1381356\mathrm{.}2$

Utilities costs $$100,0006\%-2002151\mathrm{.}59-1614218\mathrm{.}4$

Incremental overhead $$72,000$

Supply cost $($$$/$procedure$)$ $$400$

Tax rate $30\mathrm{.}0\%$

Inflation rate on net patient revenue $2\mathrm{.}0\%$

Inflation rate on costs $3\mathrm{.}0\%$

Revenues lost from inpatient surgeries $$2,000,000$

Reduction in inpatient surgery costs $$1,000,000$

Cost of capital $10\mathrm{.}0\%$

MODEL$-$GENERATED DATA:

Depreciation Schedule:

MACRS Deprec. End of Year

Year Factor Expense Book value

$10\mathrm{.}20$ $$4,000,000$ $$16,000,000$

$20\mathrm{.}326,400,0009,600,000$

$30\mathrm{.}193,800,0005,800,000$

$40\mathrm{.}122,400,0003,400,000$

$50\mathrm{.}112,200,0001,200,000$

$60\mathrm{.}061,200,0000$

Net Cash Flows:

Project Cash Flows

$012345$

Land opportunity cost $($$$400,000)$

Building$/$equipment cost $(20,000,000)$

Net patient revenue $($including inpatient loss$)$ $$8,000,000$ $$8,160,000$ $$8,323,200$ $$8,489,664$ $$8,659,457$

Less: Labor costs $3,200,0003,296,0003,394,8803,496,7263,601,628$

Cost savings on inpatients $(1,000,000)(1,030,000)(1,060,900)(1,092,727)(1,125,509)$

Utilities costs $100,000103,000106,090109,273112,551$

Supplies $2,000,0002,060,0002,121,8002,185,4542,251,018$

Incremental overhead $72,00074,16076,38578,67681,037$

Depreciation $4,000,0006,400,0003,800,0002,400,0002,200,000$

Income before taxes $($$$372,000)($$$2,743,160)($$$115,055)$ $$1,312,262$ $$1,538,733$

Taxes $(111,600)(822,948)(34,516)393,678461,620$

Project net income $($$$260,400)($$$1,920,212)($$$80,538)$ $$918,583$ $$1,077,113$

Plus: Depreciation $4,000,0006,400,0003,800,0002,400,0002,200,000$

Plus: Net land salvage value $370,000$

Plus: Net building$/$equipment salvage value $8,760,000$

Net cash flow $($$$20,400,000)$ $$3,739,600$ $$4,479,788$ $$3,719,462$ $$3,318,583$ $$12,407,113$

Cumulative net cash flow $($$$20,400,000)($$$16,660,400)($$$12,180,612)($$$8,461,150)($$$5,142,567)$ $$7,264,546$

$($For payback calculation$)$

Profitability and Breakeven Measures:

Net present value $($NPV$)($$$533,095)$

Internal rate of return $($IRR$)9\mathrm{.}1\%$

Modified IRR $($MIRR$)9\mathrm{.}4\%$

Payback $4\mathrm{.}4$Question $3$

a$.$ Perform a sensitivity analysis for the three variables identified as highly uncertain. Assume that

input values can range from $-30$ to $+30$ percent of base case.

b$.$ How sensitive is NPV to changes in each of the three variables identified as highly uncertain?

c$.$ If the ASC proceeds, how might information about the sensitivity of NPV to each of the three

variables identified as highly uncertain be used by management? That is$,$ what might management

focus on to increase the chances of success?Question $4$

a$.$ Perform a scenario analysis using the three variables identified as highly uncertain.

b$.$ Is the ASC an average$-$risk or high$-$risk project?

c$.$ Why is the expected NPV obtained in the scenario analysis different from the base case NPV$?$

d$.$ In the worst$-$case scenario, what should management be most concerned about?Question $5$

a$.$ Perform a break$-$even analysis for each of the three variables identified as highly uncertain.