Using Excel set up the formula to calculate the terminal value of the project at year 10. Set up the formula to calculate the NPV.

Given: WACC=15%

calculating terminal value

Wald Review View Add-ins Help Analytic Solver Data Mining == Share 11-AA A-E General $ % Search Insert - 2X Delete Format 9. 2 Wrap Text S E Merge & Center - gnment SN E ) 14:09 Conditional Format as Cell Formatting Table Table Styles Styles Styles Sort & Filter Font Find & Select Ideas SE Alignment Number Cells Editing Ideas s G H I Tea R Salary 1730480 1730480 1730480 1730480 1730480 1730480 1730480 1730480 1 730480 1730480 FICA/Benefits 346096 346096 346096 346096 346096 346096 346096 346096 346096 3 46096 Premiums 160000 160000 160000 160000 160000 1160000 1 60000 1 60000 160000 1 60000 Uninsured Patient cost $1,549,190 $1,611,158 $1,675,610 51,742,663 $1,812,377 $1,884,897 $1,941,457 $1,999,712 $2,059,692 $2,121,513 G&A 430000 430000 430000 430000 430000 430000 430000 430000 430000 430000 Upgrades & Maintenance $929,514 $966,695 $1,005,366 $1,045,598 $1,087,426 $1,130,938 51,164,874 51,199,827 $1,235,815 $1,272,90B Depreciation 500000 500000 500000 500000 500000 500000 500000 500000 500000 500000 Earnings Before Tax $121,120 $252,628 $389,408 $531,709 $679,656 $833,558 $953,589 $1,077,219 $1,204,509 $1,335,707 Tax (21%) S25,435 $53,052 $81,776 $111,659 $142,728 $175,047 $200,254 $226,216 $252,947 $280,498 $95,685 $199,576 Earnings After Tax $307,633 $420,050 $536,928 $658,510 $753,336 $851,003 $951,562 $1,055,208 500000 500000 500000 500000 500000 500000 500000 500000 500000 500000 Depreciation $807,633 (516,700,000) $595,685 $699,576 $920,050 $1,036,928 $1,158,510 $1,253,336 $1,351,003 $1,451,562 $1,555,208 Cash Flow a ox N O P W A ns Help Analytic Solver Data Mining Share 11 == - General - Calibri BIU - AA - A- Search Insert - X Delete Format Wrap Text Merge & Center - AY - $ -% 868 Conditional Format as Formatting Table Cell Styles Sort & Filter Find & Select Ideas Font Alignment Number Styles Cells Editing Ideas VS Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Revenue Services 57,745,950 $8,055,788 $8,378,049 $8,713,315 59,061,883 59,424,483 $9,707,283 59,998,561 $10,298,460 $10,607 567 Expenses Acquisition Cost $15,000,000 Change in NWC $1,700,000 Variable costs $1,979,550 $2,058,732 $2,141,089 $2,226,769 $2,313,849 $2,408,515 $2,480,787 $2,555,226 S2,631,868 $2,710,863 Salary 1730480 1730480 1730480 1730480 1730480 1730480 1730480 1730480 17304801 1730480 346096 346096 FICA/Benefits 346096 346096 346096 346096 346096 346096 346096 346095 160000 160000 160000 160000 160000 160000 Premiums 1 60000 160000 160000 160000 $1,549,190 $1,611,158 $1,675,610 $1,742,663 $1,812,377 $1,884,897 $1,941,45731 $1,999,712 $2,059,692 Uninsured Patient cost $2,121,513 430000 430000 430000 G&A 430000 430000 430000 430000 430000 430000 430000 5929,514 5966,695 $1,005,366 $1,045,598 51,087,426 $1,130,938 $1,164 874 Upgrades & Maintenance $1,199,827 51,235,815 $1,272,908 EOS En can con Question 2 a 9 XN0PW AutoSave all - = capital budgeting Excel file - Excel Michelle LOK File Home Insert Draw Page Layout Formulas Data Review View Add-ins Help Analytic Solver Data Mining == 2 Wrap Text General Search Insert 2X Delete Format Calibri - 10-AA BIU-B- A. Font Paste = = = Merge & Center - $ % 4898 Conditional Format as Cell Formatting - Table Styles Styles Clipboard Alignment Number Cells F81 - X for Adult % Children % 10% Simple Lab Test Checkups $180 $135 $140 25% 35% 40% Variable Costs $55 $30 $35 20% NO pay 4 doctors 140000 6 pa 75000 1 director 160000 12 nurses 14 50/hr 4 office staff 13.00/hr 4A 1 G&A 430000 2 Capacity 70000 3 Patients 53000 4 Adult 6094 5 Children 6 growth 7 After 6 yrs, grows to perpetuity at 3% till capaity 8 WACC 159 9 Tax rate 21% 10 Acquisition cost 15000000 11 change in net working capital 1700000 70% # Patients Aduts Children 53000 31800 21200 55120 33072 22048 57325 34395 22930 59618 35771 23848 62003 37202 24802 64484 38691 25794 66419 39852 26568 68412 41048 27365 70465 42279 2 8186 72579 43548 29032 17 Revenue & Expenses Adults Revenue Simple Lab Test Checkups Simple Lab Test Checkups Simple Lab Test Checkups simple Children 51.431.000 $1,502,550 51.780.800 $381,600 $572,400 $2,077,600 $437,250 $333,900 $445,200 $116,600 $1.488,240 $1,547,775 $1,562,652 $1,625,164 51.852,032 $1,926,120 $396,864 $412,740 5595,296 $619,110 $2,160,704 $2,247 140 $454,740 $472,931 $347.256 $361,148 $463,008 $481,530 $121,264 $126, 115 $1,609.695 $1,690,180 $2.003.176 $429,264 $643,896 $2,337,104 $491,851 $375,596 $500,794 $131,164 $1,674,090 $1,741,095 $1,793,340 $1,847,160 $1,757,795 $1,828,150 $1,883,007 $1,939,518 $2.0B3,312 $2,166,696 $2,231,712 $2,298,688 $446,436 $464,292 $478,224 $492,570 $669,654 $696,438 5717,336 $738,855 $2,430,596 $2,527,812 $2,603,664 $2,681,770 $511,528 $532,001 $547,965 $564, 410 $390,621 $406,256 $418,446 $431,004 $520,828 $541,674 $557,928 $574,672 $136,411 5 141,867 $146,124 $150,508 $1,902,555 $1,997,683 $2,367,624 $507,348 $761,022 $2,762,228 $581,336 $443,930 $591,906 $155,023 $1,959,660 $2,057,643 $2,438,688 $522,576 $783,864 $2,845,136 $598,785 $457,254 $609,672 $159,676 Variable Costs Adults 27 variable cost Question 2 Ready Wald Review View Add-ins Help Analytic Solver Data Mining == Share 11-AA A-E General $ % Search Insert - 2X Delete Format 9. 2 Wrap Text S E Merge & Center - gnment SN E ) 14:09 Conditional Format as Cell Formatting Table Table Styles Styles Styles Sort & Filter Font Find & Select Ideas SE Alignment Number Cells Editing Ideas s G H I Tea R Salary 1730480 1730480 1730480 1730480 1730480 1730480 1730480 1730480 1 730480 1730480 FICA/Benefits 346096 346096 346096 346096 346096 346096 346096 346096 346096 3 46096 Premiums 160000 160000 160000 160000 160000 1160000 1 60000 1 60000 160000 1 60000 Uninsured Patient cost $1,549,190 $1,611,158 $1,675,610 51,742,663 $1,812,377 $1,884,897 $1,941,457 $1,999,712 $2,059,692 $2,121,513 G&A 430000 430000 430000 430000 430000 430000 430000 430000 430000 430000 Upgrades & Maintenance $929,514 $966,695 $1,005,366 $1,045,598 $1,087,426 $1,130,938 51,164,874 51,199,827 $1,235,815 $1,272,90B Depreciation 500000 500000 500000 500000 500000 500000 500000 500000 500000 500000 Earnings Before Tax $121,120 $252,628 $389,408 $531,709 $679,656 $833,558 $953,589 $1,077,219 $1,204,509 $1,335,707 Tax (21%) S25,435 $53,052 $81,776 $111,659 $142,728 $175,047 $200,254 $226,216 $252,947 $280,498 $95,685 $199,576 Earnings After Tax $307,633 $420,050 $536,928 $658,510 $753,336 $851,003 $951,562 $1,055,208 500000 500000 500000 500000 500000 500000 500000 500000 500000 500000 Depreciation $807,633 (516,700,000) $595,685 $699,576 $920,050 $1,036,928 $1,158,510 $1,253,336 $1,351,003 $1,451,562 $1,555,208 Cash Flow a ox N O P W A ns Help Analytic Solver Data Mining Share 11 == - General - Calibri BIU - AA - A- Search Insert - X Delete Format Wrap Text Merge & Center - AY - $ -% 868 Conditional Format as Formatting Table Cell Styles Sort & Filter Find & Select Ideas Font Alignment Number Styles Cells Editing Ideas VS Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Revenue Services 57,745,950 $8,055,788 $8,378,049 $8,713,315 59,061,883 59,424,483 $9,707,283 59,998,561 $10,298,460 $10,607 567 Expenses Acquisition Cost $15,000,000 Change in NWC $1,700,000 Variable costs $1,979,550 $2,058,732 $2,141,089 $2,226,769 $2,313,849 $2,408,515 $2,480,787 $2,555,226 S2,631,868 $2,710,863 Salary 1730480 1730480 1730480 1730480 1730480 1730480 1730480 1730480 17304801 1730480 346096 346096 FICA/Benefits 346096 346096 346096 346096 346096 346096 346096 346095 160000 160000 160000 160000 160000 160000 Premiums 1 60000 160000 160000 160000 $1,549,190 $1,611,158 $1,675,610 $1,742,663 $1,812,377 $1,884,897 $1,941,45731 $1,999,712 $2,059,692 Uninsured Patient cost $2,121,513 430000 430000 430000 G&A 430000 430000 430000 430000 430000 430000 430000 5929,514 5966,695 $1,005,366 $1,045,598 51,087,426 $1,130,938 $1,164 874 Upgrades & Maintenance $1,199,827 51,235,815 $1,272,908 EOS En can con Question 2 a 9 XN0PW AutoSave all - = capital budgeting Excel file - Excel Michelle LOK File Home Insert Draw Page Layout Formulas Data Review View Add-ins Help Analytic Solver Data Mining == 2 Wrap Text General Search Insert 2X Delete Format Calibri - 10-AA BIU-B- A. Font Paste = = = Merge & Center - $ % 4898 Conditional Format as Cell Formatting - Table Styles Styles Clipboard Alignment Number Cells F81 - X for Adult % Children % 10% Simple Lab Test Checkups $180 $135 $140 25% 35% 40% Variable Costs $55 $30 $35 20% NO pay 4 doctors 140000 6 pa 75000 1 director 160000 12 nurses 14 50/hr 4 office staff 13.00/hr 4A 1 G&A 430000 2 Capacity 70000 3 Patients 53000 4 Adult 6094 5 Children 6 growth 7 After 6 yrs, grows to perpetuity at 3% till capaity 8 WACC 159 9 Tax rate 21% 10 Acquisition cost 15000000 11 change in net working capital 1700000 70% # Patients Aduts Children 53000 31800 21200 55120 33072 22048 57325 34395 22930 59618 35771 23848 62003 37202 24802 64484 38691 25794 66419 39852 26568 68412 41048 27365 70465 42279 2 8186 72579 43548 29032 17 Revenue & Expenses Adults Revenue Simple Lab Test Checkups Simple Lab Test Checkups Simple Lab Test Checkups simple Children 51.431.000 $1,502,550 51.780.800 $381,600 $572,400 $2,077,600 $437,250 $333,900 $445,200 $116,600 $1.488,240 $1,547,775 $1,562,652 $1,625,164 51.852,032 $1,926,120 $396,864 $412,740 5595,296 $619,110 $2,160,704 $2,247 140 $454,740 $472,931 $347.256 $361,148 $463,008 $481,530 $121,264 $126, 115 $1,609.695 $1,690,180 $2.003.176 $429,264 $643,896 $2,337,104 $491,851 $375,596 $500,794 $131,164 $1,674,090 $1,741,095 $1,793,340 $1,847,160 $1,757,795 $1,828,150 $1,883,007 $1,939,518 $2.0B3,312 $2,166,696 $2,231,712 $2,298,688 $446,436 $464,292 $478,224 $492,570 $669,654 $696,438 5717,336 $738,855 $2,430,596 $2,527,812 $2,603,664 $2,681,770 $511,528 $532,001 $547,965 $564, 410 $390,621 $406,256 $418,446 $431,004 $520,828 $541,674 $557,928 $574,672 $136,411 5 141,867 $146,124 $150,508 $1,902,555 $1,997,683 $2,367,624 $507,348 $761,022 $2,762,228 $581,336 $443,930 $591,906 $155,023 $1,959,660 $2,057,643 $2,438,688 $522,576 $783,864 $2,845,136 $598,785 $457,254 $609,672 $159,676 Variable Costs Adults 27 variable cost Question 2 Ready