Docstoc

Capital Budgeting Workbook

Document Sample
Capital Budgeting Workbook Powered By Docstoc
					Capital Budgeting Analysis for Medical Services USA
Purpose of Spreadsheet

Supplemental Material for Short Course 3: Budgeting Analysis, located on the internet www.exinfm.com/training. Prepared by: Ma Evans, CPA, CMA, CFM

Illustrate concepts related to capital budgeting analysis of projects. Certain aspects of a capital project may have not been included in order to help highlight basic concepts, such as Net Present Value. The spreadsheet is setup to evaluate six different projects and summarize all projects based on both economic analysis and risk factors assigned. Note: The Solver feature of Excel is used in an example at the end. Please make sure that you have installed Solver: Solver add-on feature (Tools => Add-Ins => Solver). Economic Analysis Three economic criteria are applied to projects: Net Present Value, Modified Internal Rate of Return and Discounted Payback Period. If your project(s) have non-periodic payments (payments are not equal over the life of the project), then you should use these Excel Functions: =XNPV for Net Present Value by entering specific dates of cash flows =XIRR for Internal Rate of Return by entering specific dates of cash flows Cell Indicators Certain cells are highlighted as follows: Selected input cells (not all input cells are highlighted since each project is unique) Cell includes a comment - move mouse and point over cell for comment Indication of an error in calculation or a red flag that a criteria has not been met Organization of Spreadsheet Lead Worksheet Project A Analysis - Example with Annual Cash Flow Calculations Project B Analysis - Example with Sunk Costs & Projected Financials Project C Analysis - Example of Upgrade Investment Project D Analysis - Example of Project Financing Project E Analysis - Example of Foreign Investment Project F Analysis - Example of Monthly Inflows / Outflows Summary and Example of Using Excel Solver Answer Report 1 - Output from Using Excel Solver in Summary Example General Input The following general inputs have been used on different worksheets: A B C D E F Diesel Generation System New Clinic in Kansas City Upgrade to DuBois Center Southeastern Upgrades Canadian Partnership Regulatory Compliance NE 27.50% 9.50% <= Enter project name <= Enter project name <= Enter project name <= Enter project name <= Enter project name <= Enter project name <= Marginal Tax Rate * <= Weighted Average Cost of Capital

$10,000 <= Threshold investment amount where formal project analysis is not required - general expenditure item. * If you expect changes in future tax rates, you may want to consider these changes in your analysis. Project Codes (Used to help categorize various capital projects) Project Classification Codes: 1 Land 2 Buildings 3 Leasehold Improvement 4 Equipment 5 Furniture and Fixtures 6 Vehicles 7 Acquisitions 8 Investments 9 Other Primary Justification for Project: A Cost Reduction B Replacement C Expansion / Addition D Service Improvement E Safety & Compliance F Operating Necessity G Other Priority Code: 1 Carry over project, already in progress, requires additional funding 2 Essential for continued operations, regulatory compliance, etc. 3 Economically desired for revenue growth, cost reductions, etc. 4 General improvement for building or expanding the business

ental Material for Short Course 3: Capital g Analysis, located on the internet at nfm.com/training. Prepared by: Matt H. PA, CMA, CFM Lead Wks Project A Project B Project C Project D Project E Project F Summary

oject is unique)

where formal project analysis

A

Capital Budgeting Analysis for Medical Services USA Diesel Generation System

Project Information Project Description > New diesel backup system for high volume medical plant Project Benefits > Eliminate downtime, improve efficiency, better service Project Location > Mobile, AL Responsible Division > Southern Responsible Department > Plant Engineering Contact Person Name > John Pearson, Southern Div Manager Estimated Project Start Date > 01/01/92 Classification > 4 Justification > F Priority > 2 Preliminary Review Assign points from 0 to 5 for each of the following project attributes. 0 indicates that the attribute does not apply to the project. 5 is the highest rating, indicating that the project strongly meets this project attribute. Financial Attributes: F1 Project improves overall profitability of the company F2 Project lowers cost structure F3 Project will generate a rate of return F4 Project improves asset utilization F5 Other Financial Attribute __________________________________ F6 Other Financial Attribute __________________________________ Operating Attributes: O1 Improves operating efficiencies O2 Increases the customer base O3 Improves overall customer service O4 Improves competitive position of company O5 Other Operating Attribute _________________________________ O6 Other Operating Attribute _________________________________ Contingency Attributes: C1 Project has options that allow for change during life C2 Project will positively impact company even if value is negative C3 Project can be abandoned easily with some positive value C4 Project permits several options to maximize value C5 Other Cont Attribute _____________________________________ Miscellaneous Attributes: M1 Expands Human Resource Capital M2 Enhances workforce productivity M3 Project meets a critical regulatory, security or specific need

2 3 3 1 0 0

4 0 0 0 0 0

2 4 1 2 0

0 0 0

M4 M5 M6 M7 M8

Project fits with company strategy and goals Probability of project success is very high / low risk Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Total Preliminary Points

1 4 0 0 0 27

Projects with point totals less than 15 may represent poor investments and require additional approval before further analysis and processing. Projects with point totals between 15 and 20 require caution and careful analysis. Projects with point totals greater than 20 may proceed with analysis and submission. Financial Analysis

* * * * * *

Proposed Project Expenditure: Equipment & Facilities (purchase price) Installation Cost Labor Materials Shipping Taxes Other Costs (expensed) Intial Cash Outlay for Project Tax Breaks for Investment Sale of Existing Assets Tax Benefit on Loss - Sale of Assets Total Project Investment Project Analysis Required?

165,000 650 4,500 250 1,280 11,550 350 183,580 (1,500) 0 0 182,080 Yes

* capitalized costs subject to depreciation using double declining method over 8 years with $ 8,000 salvag NOTE: Depreciation for tax purposes is considered the same for accounting purposes. If tax depreciatio different than accounting depreciation, deduct tax depreciation in calculating taxes. Cash Flows associated with Project: Year 1: Reductions in annual operating costs New revenues from higher output volumes Eliminate third party vendor service Annual service and maintenance Annual fuel costs Depreciation: Cost 183,230 Salvage Value 8,000 Useful Life 8 Depreciation in Yr 1 Operating Cash Flow in Year 1 Less Taxes Net Income - Year 1 Add Back Non Cash Depreciation

28,900 6,200 35,000 (4,600) (1,500)

(45,808) 18,193 (5,003) 13,190 45,808

Change to Net Working Capital Net Cash Flow - Year 1 Year 2: Reductions in annual operating costs New revenues from higher output volumes Eliminate third party vendor service Annual service and maintenance Annual fuel costs Depreciation: Cost 183,230 Salvage Value 8,000 Useful Life 8 Depreciation in Yr 2 Operating Cash Flow in Year 2 Less Taxes Net Income - Year 2 Add Back Non Cash Depreciation Change to Net Working Capital Net Cash Flow - Year 2 Year 3: Reductions in annual operating costs New revenues from higher output volumes Eliminate third party vendor service Annual service and maintenance Annual fuel costs Depreciation: Cost 183,230 Salvage Value 8,000 Useful Life 8 Depreciation in Yr 3 Operating Cash Flow in Year 3 Less Taxes Net Income - Year 3 Add Back Non Cash Depreciation Change to Net Working Capital Net Cash Flow - Year 3 Year 4: Reductions in annual operating costs New revenues from higher output volumes Eliminate third party vendor service Annual service and maintenance Annual fuel costs Depreciation: Cost 183,230 Salvage Value 8,000 Useful Life 8 Depreciation in Yr 4 Operating Cash Flow in Year 4

(3,500) 55,497

32,100 6,900 35,000 (5,000) (1,800)

(34,356) 32,844 (9,032) 23,812 34,356 (650) 57,518

34,800 7,100 35,000 (5,000) (2,000)

(25,767) 44,133 (12,137) 31,997 25,767 (550) 57,213

37,200 7,900 0 (5,200) (2,100)

(19,325) 18,475

Less Taxes Net Income - Year 4 Add Back Non Cash Depreciation Change to Net Working Capital Net Cash Flow - Year 4 Year 5: Reductions in annual operating costs New revenues from higher output volumes Eliminate third party vendor service Annual service and maintenance Annual fuel costs Depreciation: Cost 183,230 Salvage Value 8,000 Useful Life 8 Depreciation in Yr 5 Operating Cash Flow in Year 5 Less Taxes Net Income - Year 5 Add Back Non Cash Depreciation Change to Net Working Capital Net Cash Flow - Year 5 Year 6: Reductions in annual operating costs New revenues from higher output volumes Eliminate third party vendor service Annual service and maintenance Annual fuel costs Depreciation: Cost 183,230 Salvage Value 8,000 Useful Life 8 Depreciation in Yr 6 Operating Cash Flow in Year 6 Less Taxes Net Income - Year 6 Add Back Non Cash Depreciation Change to Net Working Capital Net Cash Flow - Year 6 Year 7: Reductions in annual operating costs New revenues from higher output volumes Eliminate third party vendor service Annual service and maintenance Annual fuel costs Depreciation: Cost 183,230 Salvage Value 8,000

(5,081) 13,394 19,325 (250) 32,469

40,900 8,900 0 (5,300) (2,200)

(14,494) 27,806 (7,647) 20,160 14,494 (350) 34,303

44,200 9,700 0 (5,400) (2,300)

(10,870) 35,330 (9,716) 25,614 10,870 (150) 36,334

50,100 10,200 0 (5,500) (2,400)

Useful Life Depreciation in Yr Operating Cash Flow in Year 7 Less Taxes Net Income - Year 7 Add Back Non Cash Depreciation Change to Net Working Capital Net Cash Flow - Year 7

8 7

(8,153) 44,247 (12,168) 32,079 8,153 (150) 40,082

Year 8: Reductions in annual operating costs New revenues from higher output volumes Rebuild / Repair Unit Option Annual service and maintenance Annual fuel costs Depreciation: Cost 183,230 Salvage Value 8,000 Useful Life 8 Depreciation in Yr 8 Operating Cash Flow in Year 8 Less Taxes Net Income - Year 8 Add Back Non Cash Depreciation Change to Net Working Capital Net Cash Flow - Year 8 Terminal Year 9 Salvage Value of Asset Working Capital Reversed Terminal Value

55,500 11,000 (25,000) (4,600) (2,600)

(6,115) 28,185 (7,751) 20,434 6,115 (50) 26,499

5,000 2,500 7,500

At the end of Year 8, a decision will be made to either rebuild the asset or outsource to third party. Economic Analysis Summarize Cash Outflows and Inflows for Project: Cash Present Recovery Year Flows Value Payback 0 (182,080) (182,080) 1 55,497 49,997 (132,083) 2 57,518 46,683 (85,400) 3 57,213 41,834 (43,566) 4 32,469 21,389 (22,177) 5 34,303 20,357 (1,820) 6 36,334 19,426 17,606 <= payback 7 40,082 19,306 36,912 8 26,499 11,499 48,410 9 7,500 2,932 51,342 Net Present Value 51,342

Required Rate of Return for Project => Reinvestment Rate for Project => Net Present Value Modified IRR Discounted Payback (years) $51,342 11.03% 6.1

11.00% 6.00%

Economic Assessment Project has positive Net Present Value? Project has IRR in excess of cost? Project has a positive payback? Yes Yes Yes

Project must meet at least two of the three Economic Criteria, otherwise special approval is required.

Risk Analysis Risk Premium Applied to Project 1.50%

Compare Risk Factor with Government Treasury Bond (lowest risk) to Project Risk Factor: Risk Ranking = 1 for lowest possible risk up to 10 for highest possible risk Probability of Accurate and Reliable Information - Gov't T Bond 1.00 (a) Risk Ranking assigned to Gov't Treasury Bond ( 1 to 10) 1 (b) Exponential power to apply to Risk Ranking is 2 - (a) 1 (c) Risk Factor = (b) raised to the power (c) 1 Probability of Accurate and Reliable Information for Project Risk Ranking assigned to project (1 to 10) Exponential power to apply to Project Risk Factor for Project Assign probabilities to three possible outcomes for project: P Pesimistic outlook, declining growth, slower volumes, etc. N Normal expected outlook as applied in analysis O Optomistic outlook, better than expected growth Total should equal 100.00% Enter Expected Cash Flows for different outcomes: Standard Deviation (Abs Risk) 17,758 21,584 22,854 10,699 15,679 22,531 Coeff of Variation (Rel Risk) 0.332 0.396 0.426 0.332 0.490 0.725 0.6 5 1.4 10

25.00% 60.00% 15.00% 100.00%

Year 1 2 3 4 5 6

Net Cash Flows Pesimist Normal Optomist 41,800 55,497 64,900 39,750 57,518 67,300 37,400 57,213 66,100 26,200 32,469 41,050 21,050 34,303 40,900 13,200 36,334 39,800

Expected Value 53,483 54,543 53,593 32,189 31,979 31,071

7 8 Terminal Totals

11,800 8,100 1,000 200,300

40,082 26,499 7,500 347,416

40,800 35,700 8,500 405,050

33,119 23,279 6,025 319,282

25,430 21,793 6,346 164,674 164,674 0.516

0.768 0.936 1.053 0.516

Absolute Risk of Project (Std Deviation) Relative Risk of Project (Coeff of Variation)

Revised Economic Analysis using Expected Values Expected Value (182,080) 53,483 54,543 53,593 32,189 31,979 31,071 33,119 23,279 6,025 Present Value (182,080) 48,183 44,268 39,187 21,204 18,978 16,612 15,952 10,102 2,355 34,761 $34,761 10.07% 6.6 Recovery Payback (133,897) (89,628) (50,442) (29,238) (10,259) 6,352 payback 22,304 32,406 34,761

Year 0 1 2 3 4 5 6 7 8 9 Totals

Net Present Value Modified IRR Discounted Payback (years)

d over 8 years with $ 8,000 salvage value unting purposes. If tax depreciation is

or outsource to third party.

B

Capital Budgeting Analysis for Medical Services USA New Clinic in Kansas City

Project Information Project Description > New Walk In Clinic - Kansas City Project Benefits > Toehold market position, profit center, business expansion Project Location > Kansas City Responsible Division > Mid West Responsible Department > Business Development Contact Person Name > Bill Watson, Operations Director Estimated Project Start Date > 04/01/92 Classification > 2 Justification > C Priority > 3 Preliminary Review Assign points from 0 to 5 for each of the following project attributes. 0 indicates that the attribute does not apply to the project. 5 is the highest rating, indicating that the project strongly meets this project attribute. Financial Attributes: F1 Project improves overall profitability of the company F2 Project lowers cost structure F3 Project will generate a rate of return F4 Project improves asset utilization F5 Other Financial Attribute __________________________________ F6 Other Financial Attribute __________________________________ Operating Attributes: O1 Improves operating efficiencies O2 Increases the customer base O3 Improves overall customer service O4 Improves competitive position of company O5 Other Operating Attribute _________________________________ O6 Other Operating Attribute _________________________________ Contingency Attributes: C1 Project has options that allow for change during life C2 Project will positively impact company even if value is negative C3 Project can be abandoned easily with some positive value C4 Project permits several options to maximize value C5 Other Cont Attribute _____________________________________ Miscellaneous Attributes: M1 Expands Human Resource Capital M2 Enhances workforce productivity M3 Project meets a critical regulatory, security or specific need

M4 M5 M6 M7 M8

Project fits with company strategy and goals Probability of project success is very high / low risk Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Total Preliminary Points

Projects with point totals less than 15 may represent poor investments and require additional approval before further analysis and processing. Projects with point totals between 15 and 20 require caution and careful analysis. Projects with point totals greater than 20 may proceed with analysis and submission. Financial Analysis Proposed Project Expenditure: Rework / Upgrade Existing Building Equipment and Fabrication Marketing and Promotion of Clinic Contingency Costs Market Study / Research Total Project Investment Project Analysis Required?

85,000 (1) 45,000 (2) 7,500 3,500 2,500 <= exclude since sunk cost, not relevan 141,000 Yes

(1): Depreciated over 30 years using the straight line method with no salvage value. (2): Depreciated over 12 years using the declining balance method with no salvage value. Tax depreciation and accounting depreciation are considered the same. Working Capital Requirements: Historical analysis of other similar clinics indicates that clinics require: Cash Accounts Receivable Inventories Total Current Assets Accounts Payable Other accruals Total Current Liab Net Working Capital Sales Revenues Ratio (NWC / Sales) 3,500 7,500 5,000 16,000 6,000 4,000 10,000 6,000 60,000 10.00%

The above Ratio will be applied to estimate working capital requirements Opportunity Cost Analysis: As a result of this project investment, the company expects some negative impact to its Topeka, Kansas facility as follows: Year 1 Year 2 Reduction to Sales Revenues (4,000) (5,000) Reductions in overall cost 1,500 2,000

Net Reduction to Income Five Year Financial Forecast:

(2,500)

(3,000)

Sales Revenues Cost of Services: Personnel / Labor Supplies, Vendors, etc. Adm Overhead Increases Opportunity Cost (per above) Profit before Tax Taxes Investment Credits Net Profit Depreciation: Upgrade to Building Useful Life => 30 Equipment 12 Operating Cash Flow Net Working Capital Planned Critical Cash Outlays Total Cash Flow

Year 1 65,000 (15,000) (8,000) (22,000) (2,500) 17,500 (4,813) 50 12,738

Year 2 75,000 (18,000) (10,000) (28,000) (3,000) 16,000 (4,400) 0 11,600

2,833 6,923 22,494 (6,500) (1,500) 14,494

2,833 6,346 20,779 (7,500) (2,500) 10,779

Economic Analysis Summarize Cash Outflows and Inflows for Project: Cash Present Year Flows Value 0 (141,000) (141,000) 1 14,494 12,883 2 10,779 8,517 3 17,723 12,447 4 30,696 19,163 5 30,634 17,000 6 34,197 16,868 7 33,845 14,840 8 35,043 13,658 9 36,604 12,681 10 36,914 11,368 11 31,450 8,609 12 31,450 7,652 13 31,450 6,802 14 31,450 6,046 15 31,450 5,374 16 31,450 4,777 17 31,450 4,246 18 31,450 3,775 19 31,450 3,355

Payback (128,117) (119,599) (107,152) (87,989) (70,990) (54,121) (39,282) (25,624) (12,943) (1,575) 7,033 payback 14,686 21,488 27,534 32,908 37,686 41,932 45,707 49,062

20 31,450 Net Present Value

2,982 52,044

52,044

Required Rate of Return for Project => Reinvestment Rate for Project => Net Present Value Modified IRR Discounted Payback (years)

12.50% 7.50% $52,044 11.36% 11.18

Economic Assessment Project has positive Net Present Value? Project has IRR in excess of cost? Project has a positive payback? Yes No Yes

Project must meet at least two of the three Economic Criteria, otherwise special approval is required.

Risk Analysis Risk Premium Applied to Project 3.00%

Compare Risk Factor with Government Treasury Bond (lowest risk) to Project Risk Factor: Risk Ranking = 1 for lowest possible risk up to 10 for highest possible risk Probability of Accurate and Reliable Information - Gov't T Bond Risk Ranking assigned to Gov't Treasury Bond ( 1 to 10) Exponential power to apply to Risk Ranking is 2 - (a) Risk Factor = (b) raised to the power (c) Probability of Accurate and Reliable Information for Project Risk Ranking assigned to project (1 to 10) Exponential power to apply to Project Risk Factor for Project Assign probabilities to three possible outcomes for project: P Pesimistic outlook, declining growth, slower volumes, etc. N Normal expected outlook as applied in analysis O Optomistic outlook, better than expected growth Total should equal 100.00% Enter Expected Cash Flows for different outcomes: Standard Deviation (Abs Risk) 13,845 15,031 19,774

Year 1 2 3

Net Cash Flows Pesimist Normal Optomist 8,650 14,494 26,800 6,800 10,779 27,500 7,900 17,723 33,200

Expected Value 13,064 10,223 15,059

4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Totals

8,600 8,100 7,500 7,100 6,200 6,000 5,500 5,100 4,900 4,250 3,705 3,250 2,680 2,005 1,490 1,105 790 101,625

30,696 30,634 34,197 33,845 35,043 36,604 36,914 31,450 31,450 31,450 31,450 31,450 31,450 31,450 31,450 31,450 31,450 595,428

37,800 43,900 42,100 41,850 40,950 40,200 39,900 37,750 35,950 34,150 32,990 31,650 31,280 31,000 30,850 29,990 28,750 698,560

23,317 23,410 25,248 24,884 25,243 26,072 26,068 22,543 22,383 22,065 21,816 21,590 21,372 21,122 20,934 20,756 20,584 427,753

25,865 30,422 30,794 30,908 31,501 31,630 31,993 29,386 28,367 27,779 27,520 27,054 27,349 27,806 28,197 27,986 27,454 540,660 540,660 1.264

Absolute Risk of Project (Std Deviation) Relative Risk of Project (Coeff of Variation)

Revised Economic Analysis using Expected Values Expected Values 0 (141,000) 1 13,064 2 10,223 3 15,059 4 23,317 5 23,410 6 25,248 7 24,884 8 25,243 9 26,072 10 26,068 11 22,543 12 22,383 13 22,065 14 21,816 15 21,590 16 21,372 17 21,122 18 20,934 19 20,756 20 20,584 Net Present Value Year Net Present Value Present Value (141,000) 11,612 8,077 10,576 14,557 12,991 12,454 10,911 9,838 9,032 8,028 6,171 5,446 4,772 4,194 3,689 3,246 2,852 2,513 2,214 1,952 4,127 $4,127 Recovery Payback (129,388) (121,311) (110,734) (96,177) (83,186) (70,732) (59,821) (49,983) (40,951) (32,923) (26,752) (21,306) (16,534) (12,340) (8,651) (5,404) (2,552) (40) 2,175 payback 4,127

Modified IRR Discounted Payback (years)

9.68% 19.02

3 2 4 2 0 0

2 3 3 2 0 0

0 0 0 0 0

0 0 0

3 0 0 0 0 24

equire additional etween 15 and 20 0 may proceed

<= exclude since sunk cost, not relevant

h no salvage value. od with no salvage value.

e negative impact to its Year 3 (3,000) 1,200 Year 4 (1,500) 700 Year 5 (500) 100

(1,800)

(800)

(400) Terminal Flow 11 to 20 160,000 (40,000) (18,000) (40,000) 0 62,000 (17,050) 0 44,950

Year 3 100,000 (22,000) (15,000) (30,000) (1,800) 31,200 (8,580) 0 22,620

Year 4 125,000 (25,000) (18,000) (32,000) (800) 49,200 (13,530) 0 35,670

Year 5 135,000 (30,000) (19,000) (35,000) (400) 50,600 (13,915) 0 36,685

Year 6 140,000 (32,000) (15,000) (36,000) 0 57,000 (15,675) 0 41,325

Year 7 145,000 (35,000) (15,000) (37,000) 0 58,000 (15,950) 0 42,050

Year 8 149,000 (36,000) (15,000) (37,000) 0 61,000 (16,775) 0 44,225

Year 9 153,000 (36,500) (15,000) (37,000) 0 64,500 (17,738) 0 46,763

Year 10 155,000 (37,000) (15,000) (37,000) 0 66,000 (18,150) 0 47,850

2,833 5,769 31,223 (10,000) (3,500) 17,723

2,833 5,192 43,696 (12,500) (500) 30,696

2,833 4,615 44,134 (13,500) 0 30,634

2,833 4,038 48,197 (14,000) 0 34,197

2,833 3,462 48,345 (14,500) 0 33,845

2,833 2,885 49,943 (14,900) 0 35,043

2,833 2,308 51,904 (15,300) 0 36,604

2,833 1,731 52,414 (15,500) 0 36,914

2,500 0 47,450 (16,000) 0 31,450

herwise special

sk) to Project Risk Factor: 1.00 (a) 1 (b) 1 (c) 1 0.5 7 1.5 19

35.00% 60.00% 5.00% 100.00%

Coeff of Variation (Rel Risk) 1.060 1.470 1.313

1.109 1.300 1.220 1.242 1.248 1.213 1.227 1.304 1.267 1.259 1.261 1.253 1.280 1.316 1.347 1.348 1.334 1.264

C

Capital Budgeting Analysis for Medical Services USA Upgrade to DuBois Center

Project Information Project Description > Upgrade DuBois for unused capacity Project Benefits > Better use of facility, more income Project Location > Iowa Responsible Division > Mid West Responsible Department > Finance Contact Person Name > Cheryl Strickland, Controller Estimated Project Start Date > 03/15/92 Classification > 4 Justification > C Priority > 3 Preliminary Review Assign points from 0 to 5 for each of the following project attributes. 0 indicates that the attribute does not apply to the project. 5 is the highest rating, indicating that the project strongly meets this project attribute. Financial Attributes: F1 Project improves overall profitability of the company F2 Project lowers cost structure F3 Project will generate a rate of return F4 Project improves asset utilization F5 Other Financial Attribute __________________________________ F6 Other Financial Attribute __________________________________ Operating Attributes: O1 Improves operating efficiencies O2 Increases the customer base O3 Improves overall customer service O4 Improves competitive position of company O5 Other Operating Attribute _________________________________ O6 Other Operating Attribute _________________________________ Contingency Attributes: C1 Project has options that allow for change during life C2 Project will positively impact company even if value is negative C3 Project can be abandoned easily with some positive value C4 Project permits several options to maximize value C5 Other Cont Attribute _____________________________________ Miscellaneous Attributes: M1 Expands Human Resource Capital M2 Enhances workforce productivity M3 Project meets a critical regulatory, security or specific need

M4 M5 M6 M7 M8

Project fits with company strategy and goals Probability of project success is very high / low risk Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Total Preliminary Points

Projects with point totals less than 15 may represent poor investments and require additional approval before further analysis and processing. Projects with point totals between 15 and 20 require caution and careful analysis. Projects with point totals greater than 20 may proceed with analysis and submission. Cost Volume Profile of DuBois Facility Personnel & Labor Supplies and Inventory Variable Adm Overhead Fixed Overhead Costs Total Unit Costs per Billable Hour 37.50 2.65 3.60 10.50 54.25

Current Operating Capacity => 60% Maximum Capacity per month 15,000 billable hours Sales growth rates over the next ten years are estimated at 4% per year with increases to costs estimated at 1.5% per year for each variable component. Estimated Changes from Upgrade Investment Upgrade Investment will result in additional 5,000 billable hours per month New Services from Upgrade Investment are billable to customers at $ 50.00 per hour Capacity after upgrade => 14,000 Does upgrade project exceed max? No Financial Analysis Proposed Project Expenditure: Upgrade Systems & Facilities Executive Management Time Training and Promotion Contingency Costs Total Investment Project Analysis Required?

1,150,000 405,000 135,000 90,000 1,780,000 Yes

Year 1 2 3 4 5

Annual < - - relevant annual costs to project - - > Incremental Personnel Supplies Var Adm Revenues & Labor & Inventory Overhead 3,000,000 (2,250,000) (159,000) (216,000) 3,090,000 (2,283,750) (161,385) (219,240) 3,182,700 (2,318,006) (163,806) (222,529) 3,278,181 (2,352,776) (166,263) (225,867) 3,376,526 (2,388,068) (168,757) (229,255)

6 7 8 9 10

3,477,822 3,582,157 3,689,622 3,800,310 3,914,320

(2,423,889) (2,460,247) (2,497,151) (2,534,608) (2,572,627)

(171,288) (173,857) (176,465) (179,112) (181,799)

(232,693) (236,184) (239,727) (243,322) (246,972)

Due to the rapid changes in services provided by this upgrade investment project, no term calculated for the periods beyond year 10. Economic Analysis Summarize Cash Outflows and Inflows for Project: Cash Present Year Flows Value 0 (1,780,000) (1,780,000) 1 256,875 233,523 2 295,578 244,279 3 334,811 251,548 4 374,625 255,874 5 416,074 258,349 6 459,215 259,215 7 504,105 258,685 8 550,802 256,953 9 599,369 254,191 10 649,868 250,552 Net Present Value 743,170 Required Rate of Return for Project => Reinvestment Rate for Project => Net Present Value Modified IRR Discounted Payback (years) Economic Assessment Project has positive Net Present Value? Project has IRR in excess of cost? Project has a positive payback? Yes Yes Yes

Payback (1,546,477) (1,302,198) (1,050,650) (794,776) (536,427) (277,212) (18,527) 238,427 492,618 743,170

Project must meet at least two of the three Economic Criteria, otherwise special approval is required.

Risk Analysis Risk Premium Applied to Project 0.50%

Compare Risk Factor with Government Treasury Bond (lowest risk) to Project Risk Factor: Risk Ranking = 1 for lowest possible risk up to 10 for highest possible risk Probability of Accurate and Reliable Information - Gov't T Bond

Risk Ranking assigned to Gov't Treasury Bond ( 1 to 10) Exponential power to apply to Risk Ranking is 2 - (a) Risk Factor = (b) raised to the power (c) Probability of Accurate and Reliable Information for Project Risk Ranking assigned to project (1 to 10) Exponential power to apply to Project Risk Factor for Project Assign probabilities to three possible outcomes for project: P Pesimistic outlook, declining growth, slower volumes, etc. N Normal expected outlook as applied in analysis O Optomistic outlook, better than expected growth Total should equal 100.00% Enter Expected Cash Flows for different outcomes:

Year 1 2 3 4 5 6 7 8 9 10

Pesimist 212,500 242,500 301,200 326,700 370,900 411,800 461,900 510,200 561,300 615,100 4,014,100

Net Cash Flows Normal Optomist 256,875 285,700 295,578 324,300 334,811 362,050 374,625 401,200 416,074 441,300 459,215 483,700 504,105 533,100 550,802 582,200 599,369 631,100 649,868 677,700 4,441,320 4,722,350

Expected Value 252,324 289,271 332,174 369,026 410,823 453,405 500,013 547,391 596,515 647,089 4,398,031

Absolute Risk of Project (Std Deviation) Relative Risk of Project (Coeff of Variation)

Revised Economic Analysis using Expected Values Expected Values (1,780,000) 252,324 289,271 332,174 369,026 410,823 453,405 500,013 547,391 596,515 647,089 Present Value (1,780,000) 229,385 239,067 249,568 252,050 255,089 255,935 256,586 255,362 252,980 249,481 Recovery Payback (1,550,615) (1,311,548) (1,061,981) (809,931) (554,842) (298,907) (42,321) 213,041 466,021 715,502

Year 0 1 2 3 4 5 6 7 8 9 10

Net Present Value Net Present Value Modified IRR Discounted Payback (years)

715,502 $715,502 11.12% 8.17

ndicates that the that the project

3 3 4 4 0 0

4 3 3 2 0 0

0 0 0 0 0

0 0 0

2 3 0 0 0 31

and require additional als between 15 and 20 han 20 may proceed

ar with increases to costs

1.03 1.015

50.00 per hour

Profits before Tax 375,000 425,625 478,359 533,275 590,447

Less Taxes (103,125) (117,047) (131,549) (146,651) (162,373)

Project Income 271,875 308,578 346,811 386,625 428,074

Adj to Income (15,000) (13,000) (12,000) (12,000) (12,000)

Cash Flow 256,875 295,578 334,811 374,625 416,074

649,952 711,868 776,279 843,267 912,921

(178,737) (195,764) (213,477) (231,898) (251,053)

471,215 516,105 562,802 611,369 661,868

(12,000) (12,000) (12,000) (12,000) (12,000)

459,215 504,105 550,802 599,369 649,868

d by this upgrade investment project, no terminal value was

payback

10.00% 4.00% $743,170 11.24% 8.07

eria, otherwise special

owest risk) to Project Risk Factor: est possible risk ion - Gov't T Bond

1.00 (a)

1 (b) 1 (c) 1 0.7 4 1.3 6

ion for Project

volumes, etc.

20.00% 65.00% 15.00% 100.00%

Standard Deviation (Abs Risk) 54,978 62,301 44,892 56,638 53,493 54,949 53,239 53,323 51,374 46,230 531,419 531,419 0.121

Coeff of Variation (Rel Risk) 0.218 0.215 0.135 0.153 0.130 0.121 0.106 0.097 0.086 0.071 0.121

payback

D

Capital Budgeting Analysis for Medical Services USA Southeastern Upgrades

Project Information Project Description > Upgrade to various Southeastern facilities Project Benefits > Better use of facility, more income Project Location > Atlanta Responsible Division > Southeast Responsible Department > Engineering Contact Person Name > Bob Ferrell Estimated Project Start Date > 02/05/92 Classification > 4 Justification > C Priority > 3 Preliminary Review Assign points from 0 to 5 for each of the following project attributes. 0 indicates that the attribute does not apply to the project. 5 is the highest rating, indicating that the project strongly meets this project attribute. Financial Attributes: F1 Project improves overall profitability of the company F2 Project lowers cost structure F3 Project will generate a rate of return F4 Project improves asset utilization F5 Other Financial Attribute __________________________________ F6 Other Financial Attribute __________________________________ Operating Attributes: O1 Improves operating efficiencies O2 Increases the customer base O3 Improves overall customer service O4 Improves competitive position of company O5 Other Operating Attribute _________________________________ O6 Other Operating Attribute _________________________________ Contingency Attributes: C1 Project has options that allow for change during life C2 Project will positively impact company even if value is negative C3 Project can be abandoned easily with some positive value C4 Project permits several options to maximize value C5 Other Cont Attribute _____________________________________ Miscellaneous Attributes: M1 Expands Human Resource Capital M2 Enhances workforce productivity M3 Project meets a critical regulatory, security or specific need

M4 M5 M6 M7 M8

Project fits with company strategy and goals Probability of project success is very high / low risk Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Total Preliminary Points

Projects with point totals less than 15 may represent poor investments and require additional approval before further analysis and processing. Projects with point totals between 15 and 20 require caution and careful analysis. Projects with point totals greater than 20 may proceed with analysis and submission. Project Summary Several facilities in the Southeast area need to upgrade existing scanner equipment to better serve customers and produce higher quality analysis / treatments. The Atlanta Bank has agreed to finance $ 500,000 towards the investment at 9% over a five year period. The loan repayment schedule is as follows: Interest Number of Rate Installments 0.09 5 Loan Balance 500,000 416,454 325,388 226,127 117,932 Interest Payment (45,000) (37,481) (29,285) (20,351) (10,614) Principal Payment (83,546) (91,065) (99,261) (108,195) (117,932) Total Payment (128,546) (128,546) (128,546) (128,546) (128,546)

Year 1 2 3 4 5

Financial Analysis Proposed Project Expenditure: Total Upgrade Acquisition Price Installation / Training / Other Costs Total Investment Financed through Bank Net Cash Outlay Project Analysis Required? 1,650,000 (1) 65,000 1,715,000 (500,000) 1,215,000 Yes

(1): $ 650,000 of this price is subject to capitalization. Depreciation deducted on the tax return differs from depreciation for accounting purposes: Tax Return Accounting Year Depreciation Depreciation 1 325,000 65,000 2 162,500 65,000 3 81,250 65,000 4 40,625 65,000 5 40,625 65,000 6 65,000 7 65,000 8 65,000 9 65,000 10 65,000

Total

650,000

650,000

Cash Flow Analysis #1 - Only Project Cash Flows Service Other Depreciation Year Revenues Costs Costs Deduction 1 535,800 (112,000) (38,000) (325,000) 2 585,900 (155,100) (46,500) (162,500) 3 612,600 (188,600) (54,100) (81,250) 4 636,050 (202,900) (63,200) (40,625) 5 644,112 (224,200) (68,900) (40,625) 6 653,500 (241,600) (72,110) 7 668,200 (258,800) (77,800) 8 677,400 (272,100) (83,100) 9 689,800 (287,800) (88,900) 10 705,300 (298,400) (94,200) 11 712,900 (309,100) (97,800) 12 719,600 (319,400) (101,050) 13 722,800 (325,400) (104,900) 14 729,100 (329,900) (107,800) 15 734,300 (334,100) (110,100) Economic Analysis #1 Cash Flow 0 (1,215,000) 1 339,580 2 290,205 3 254,921 4 241,486 5 227,256 6 207,148 7 200,010 8 192,395 9 185,098 10 184,408 11 179,050 12 173,284 13 167,963 14 166,665 15 165,223 Net Present Value Year Present Value (1,215,000) 303,875 232,386 182,668 154,846 130,400 106,364 91,901 79,107 68,104 60,716 52,754 45,686 39,627 35,187 31,214 399,835 Payback (911,125) (678,739) (496,071) (341,225) (210,825) (104,461) (12,560) 66,547 134,651 195,367 248,120 293,807 333,434 368,621 399,835

Taxable Income 60,800 221,800 288,650 329,325 310,387 339,790 331,600 322,200 313,100 312,700 306,000 299,150 292,500 291,400 290,100

payback

Required Rate of Return for Project => Reinvestment Rate for Project => Net Present Value Modified IRR Discounted Payback (years) $399,835 9.25% 8.16

11.75% 4.50%

Cash Flow Analysis #2 - Include Financing Flows / Discount Rate = Cost of Borrowing:

Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Economic Analysis #2

Revenues 535,800 585,900 612,600 636,050 644,112 653,500 668,200 677,400 689,800 705,300 712,900 719,600 722,800 729,100 734,300

Service Costs (112,000) (155,100) (188,600) (202,900) (224,200) (241,600) (258,800) (272,100) (287,800) (298,400) (309,100) (319,400) (325,400) (329,900) (334,100)

Other Costs (38,000) (46,500) (54,100) (63,200) (68,900) (72,110) (77,800) (83,100) (88,900) (94,200) (97,800) (101,050) (104,900) (107,800) (110,100)

Taxable Income 385,800 384,300 369,900 369,950 351,012 339,790 331,600 322,200 313,100 312,700 306,000 299,150 292,500 291,400 290,100

Less Taxes (106,095) (105,683) (101,723) (101,736) (96,528) (93,442) (91,190) (88,605) (86,103) (85,993) (84,150) (82,266) (80,438) (80,135) (79,778)

Cash Flow 0 (1,215,000) 1 223,409 2 171,966 3 134,428 4 118,536 5 101,628 6 207,148 7 200,010 8 192,395 9 185,098 10 184,408 11 179,050 12 173,284 13 167,963 14 166,665 15 165,223 Net Present Value

Year

Present Value (1,215,000) 209,724 151,544 111,208 92,054 74,089 141,766 128,496 116,033 104,794 98,008 89,332 81,159 73,849 68,790 64,017 389,865

Payback (1,005,276) (853,731) (742,523) (650,469) (576,380) (434,614) (306,118) (190,085) (85,291) 12,718 payback 102,050 183,209 257,058 325,848 389,865

After Tax Cost of Borrowing => Reinvestment Rate for Project => Net Present Value Modified IRR Discounted Payback (years) $389,865 6.38% 10.87

6.53% 2.50%

Economic Assessment (based on Analysis #1) Project has positive Net Present Value? Project has IRR in excess of cost? Yes No

Project has a positive payback?

Yes

Project must meet at least two of the three Economic Criteria, otherwise special approval is required.

Risk Analysis (based on analysis #1) Risk Premium Applied to Project 2.25%

Compare Risk Factor with Government Treasury Bond (lowest risk) to Project Risk Factor: Risk Ranking = 1 for lowest possible risk up to 10 for highest possible risk Probability of Accurate and Reliable Information - Gov't T Bond Risk Ranking assigned to Gov't Treasury Bond ( 1 to 10) Exponential power to apply to Risk Ranking is 2 - (a) Risk Factor = (b) raised to the power (c) Probability of Accurate and Reliable Information for Project Risk Ranking assigned to project (1 to 10) Exponential power to apply to Project Risk Factor for Project Assign probabilities to three possible outcomes for project: P Pesimistic outlook, declining growth, slower volumes, etc. N Normal expected outlook as applied in analysis O Optomistic outlook, better than expected growth Total should equal 100.00% Enter Expected Cash Flows for different outcomes: Standard Deviation (Abs Risk) 30,106 30,291 29,762 27,901 27,951 28,057 27,325 26,549 26,935 26,078 25,763 26,038 23,958 23,512 23,671 403,897

1.00 1 1 1 0.65 6 1.35 11

10.00% 70.00% 20.00% 100.00%

Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Totals

Pesimist 321,100 271,600 237,200 224,850 211,800 190,450 184,100 176,350 169,150 168,340 163,100 158,350 153,300 152,820 152,650 2,935,160

Net Cash Flows Normal Optomist 339,580 361,200 290,205 311,950 254,921 276,705 241,486 261,890 227,256 248,605 207,148 227,690 200,010 220,280 192,395 211,650 185,098 204,880 184,408 203,090 179,050 197,450 173,284 192,770 167,963 185,200 166,665 183,990 165,223 183,690 3,174,689 3,471,040

Expected Value 342,056 292,694 257,506 243,903 229,980 209,586 202,473 194,642 187,459 186,537 181,135 175,688 169,944 168,746 167,659 3,210,006

Coeff of Variation (Rel Risk) 0.088 0.103 0.116 0.114 0.122 0.134 0.135 0.136 0.144 0.140 0.142 0.148 0.141 0.139 0.141 0.126 403,897

Absolute Risk of Project (Std Deviation)

Relative Risk of Project (Coeff of Variation)

0.126

Revised Economic Analysis using Expected Values Expected Year Values 0 (1,215,000) 1 342,056 2 292,694 3 257,506 4 243,903 5 229,980 6 209,586 7 202,473 8 194,642 9 187,459 10 186,537 11 181,135 12 175,688 13 169,944 14 168,746 15 167,659 Net Present Value Net Present Value Modified IRR Discounted Payback (years) Present Value (1,215,000) 306,090 234,379 184,520 156,396 131,963 107,616 93,032 80,030 68,973 61,417 53,368 46,320 40,095 35,626 31,675 416,502 $416,502 9.33% 8.01 Recovery Payback (908,910) (674,531) (490,011) (333,614) (201,651) (94,035) (1,002) 79,028 payback 148,001 209,418 262,786 309,106 349,201 384,827 416,502

2 2 4 5 0 0

5 1 2 3 0 0

0 1 1 0 0

0 2 0

2 2 0 0 0 32

better serve customers e $ 500,000 towards

Tax Benefit of Interest 12,375 10,307 8,053 5,597 2,919

d on the tax return Tax Benefit Depreciation 89,375 44,688 22,344 11,172 11,172

Less Taxes (16,720) (60,995) (79,379) (90,564) (85,356) (93,442) (91,190) (88,605) (86,103) (85,993) (84,150) (82,266) (80,438) (80,135) (79,778)

Net Add Back Income Depreciation 44,080 325,000 160,805 162,500 209,271 81,250 238,761 40,625 225,031 40,625 246,348 240,410 233,595 226,998 226,708 221,850 216,884 212,063 211,265 210,323

Working Capital (29,500) (33,100) (35,600) (37,900) (38,400) (39,200) (40,400) (41,200) (41,900) (42,300) (42,800) (43,600) (44,100) (44,600) (45,100)

Cash Flow 339,580 290,205 254,921 241,486 227,256 207,148 200,010 192,395 185,098 184,408 179,050 173,284 167,963 166,665 165,223

Net Tax Benefit Tax Benefit Income Depreciation Interest 279,705 89,375 12,375 278,618 44,688 10,307 268,178 22,344 8,053 268,214 11,172 5,597 254,484 11,172 2,919 246,348 240,410 233,595 226,998 226,708 221,850 216,884 212,063 211,265 210,323

Loan Payment (128,546) (128,546) (128,546) (128,546) (128,546)

Working Capital (29,500) (33,100) (35,600) (37,900) (38,400) (39,200) (40,400) (41,200) (41,900) (42,300) (42,800) (43,600) (44,100) (44,600) (45,100)

Cash Flow 223,409 171,966 134,428 118,536 101,628 207,148 200,010 192,395 185,098 184,408 179,050 173,284 167,963 166,665 165,223

(a) (b) (c)

E

Capital Budgeting Analysis for Medical Services USA Canadian Partnership

Project Information Project Description > Expand Toronto Urban Centers Project Benefits > Market expansion, new source of revenues, leverage of assets Project Location > Toronto Responsible Division > Canadian Division Responsible Department > Marketing Contact Person Name > Allen J. Herbert Estimated Project Start Date > 04/01/92 Classification > 8 Justification > C Priority > 3 Preliminary Review Assign points from 0 to 5 for each of the following project attributes. 0 indicates that the attribute does not apply to the project. 5 is the highest rating, indicating that the project strongly meets this project attribute. Financial Attributes: F1 Project improves overall profitability of the company F2 Project lowers cost structure F3 Project will generate a rate of return F4 Project improves asset utilization F5 Other Financial Attribute __________________________________ F6 Other Financial Attribute __________________________________ Operating Attributes: O1 Improves operating efficiencies O2 Increases the customer base O3 Improves overall customer service O4 Improves competitive position of company O5 Other Operating Attribute _________________________________ O6 Other Operating Attribute _________________________________ Contingency Attributes: C1 Project has options that allow for change during life C2 Project will positively impact company even if value is negative C3 Project can be abandoned easily with some positive value C4 Project permits several options to maximize value C5 Other Cont Attribute _____________________________________ Miscellaneous Attributes: M1 Expands Human Resource Capital M2 Enhances workforce productivity M3 Project meets a critical regulatory, security or specific need

M4 M5 M6 M7 M8

Project fits with company strategy and goals Probability of project success is very high / low risk Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Total Preliminary Points

Projects with point totals less than 15 may represent poor investments and require additional approval before further analysis and processing. Projects with point totals between 15 and 20 require caution and careful analysis. Projects with point totals greater than 20 may proceed with analysis and submission. Project Summary In order to establish a toehold position in the Toronto area, a joint venture investment will be made since the current major provider in Toronto needs improved child care services for the urban areas of Toronto. Medical Services USA will provide personnel and overall management for urban child care. In return, Medical Services USA will share in the profits 50% / 50%. The Joint Venture Agreement has a ten year term period. Financial Analysis (all amounts are expressed in Canadian Dollars) Convert to U.S.$ * Initial Relocation / Setup Costs $750,000 1.12 Ten Year Projected Income Statement Year 1 ** 2 3 4 5 6 7 8 9 10 Sales (100%) 637,500 895,000 912,000 942,000 977,000 1,015,000 1,045,000 1,088,000 1,135,000 1,180,000 Cost of Service 270,000 366,000 377,000 378,000 370,000 368,000 362,000 355,000 350,000 348,000 Income before Tax 367,500 529,000 535,000 564,000 607,000 647,000 683,000 733,000 785,000 832,000 Less Taxes 79,500 119,000 101,000 104,000 112,000 129,000 140,000 138,000 151,000 157,000 Net Income 288,000 410,000 434,000 460,000 495,000 518,000 543,000 595,000 634,000 675,000 Adj to Cash Flow 42,000 82,000 55,000 68,000 88,000 90,000 110,000 115,000 122,000 135,000

$840,000

* convert from Canadian Dollars to U.S. Dollars ** partial year in 1992 Economic Analysis (U.S. Dollars) Summarize Cash Outflows and Inflows for Project: Cash Present Year Flows Value 0 (840,000) (840,000)

Payback

1 189,750 2 290,280 3 295,845 4 316,800 5 341,055 6 355,680 7 382,005 8 415,350 9 442,260 10 473,850 Net Present Value

167,181 225,333 202,338 190,898 181,069 166,374 157,434 150,816 141,486 133,562 876,491

(672,819) (447,486) (245,148) (54,250) 126,819 293,193 450,626 601,442 742,929 876,491

Required Rate of Return for Project => Reinvestment Rate for Project => Net Present Value Modified IRR Discounted Payback (years) Economic Assessment Project has positive Net Present Value? Project has IRR in excess of cost? Project has a positive payback? Yes Yes Yes

13.50% 3.50% $876,491 16.95% 5.30

Project must meet at least two of the three Economic Criteria, otherwise special approval is required.

Risk Analysis Risk Premium Applied to Project 4.00%

Compare Risk Factor with Government Treasury Bond (lowest risk) to Project Risk Factor: Risk Ranking = 1 for lowest possible risk up to 10 for highest possible risk Probability of Accurate and Reliable Information - Gov't T Bond Risk Ranking assigned to Gov't Treasury Bond ( 1 to 10) Exponential power to apply to Risk Ranking is 2 - (a) Risk Factor = (b) raised to the power (c) Probability of Accurate and Reliable Information for Project Risk Ranking assigned to project (1 to 10) Exponential power to apply to Project Risk Factor for Project Assign probabilities to three possible outcomes for project: P Pesimistic outlook, declining growth, slower volumes, etc. N Normal expected outlook as applied in analysis O Optomistic outlook, better than expected growth Total should equal 100.00%

Enter Expected Cash Flows for different outcomes: Standard Deviation (Abs Risk) 55,685 66,708 60,421 76,871 79,304 79,171 84,006 100,391 92,352 105,845 800,754 800,754 0.236

Year 1 2 3 4 5 6 7 8 9 10

Pesimist 132,000 227,300 239,100 244,050 267,800 285,200 307,900 329,100 363,300 380,050 2,775,800

Net Cash Flows Normal Optomist 189,750 198,500 290,280 311,100 295,845 315,200 316,800 340,500 341,055 368,500 355,680 387,500 382,005 416,900 415,350 460,900 442,260 484,800 473,850 517,100 3,502,875 3,801,000

Expected Value 177,500 279,740 286,498 304,538 329,603 346,015 372,203 405,175 433,155 461,213 3,395,638

Absolute Risk of Project (Std Deviation) Relative Risk of Project (Coeff of Variation)

Revised Economic Analysis using Expected Values Expected Values 0 (840,000) 1 177,500 2 279,740 3 286,498 4 304,538 5 329,603 6 346,015 7 372,203 8 405,175 9 433,155 10 461,213 Net Present Value Year Net Present Value Modified IRR Discounted Payback (years) Present Value (840,000) 156,388 217,152 195,945 183,509 174,989 161,853 153,394 147,121 138,574 130,000 818,923 $818,923 16.58% 5.50 Recovery Payback (683,612) (466,461) (270,516) (87,007) 87,982 249,835 403,228 550,350 688,923 818,923

3 2 4 4 0 0

1 4 4 3 0 0

0 2 0 0 0

0 0 0

3 2 0 0 0 32

50%. The Joint

Estimated Cash Flow 330,000 492,000 489,000 528,000 583,000 608,000 653,000 710,000 756,000 810,000

50% Convert Share to U.S.$ * 165,000 1.15 246,000 1.18 244,500 1.21 264,000 1.20 291,500 1.17 304,000 1.17 326,500 1.17 355,000 1.17 378,000 1.17 405,000 1.17

189,750 290,280 295,845 316,800 341,055 355,680 382,005 415,350 442,260 473,850

Project Risk Factor: 1.00 (a) 1 (b) 1 (c) 1 0.6 6 1.4 12

25.00% 50.00% 25.00% 100.00%

Coeff of Variation (Rel Risk) 0.314 0.238 0.211 0.252 0.241 0.229 0.226 0.248 0.213 0.229 0.236

F

Capital Budgeting Analysis for Medical Services USA Regulatory Compliance NE

Project Information Project Description > Regulatory Compliance in NE Project Benefits > Compliance Project Location > Northeast Regional Office - Boston, MA Responsible Division > Finance Responsible Department > Finance Contact Person Name > Carl Jackson, V.P. Finance Estimated Project Start Date > 01/01/92 Classification > 9 Justification > E Priority > 2

Preliminary Review Assign points from 0 to 5 for each of the following project attributes. 0 indicates that the attribute does not apply to the project. 5 is the highest rating, indicating that the project strongly meets this project attribute. Financial Attributes: F1 Project improves overall profitability of the company F2 Project lowers cost structure F3 Project will generate a rate of return F4 Project improves asset utilization F5 Other Financial Attribute __________________________________ F6 Other Financial Attribute __________________________________ Operating Attributes: O1 Improves operating efficiencies O2 Increases the customer base O3 Improves overall customer service O4 Improves competitive position of company O5 Other Operating Attribute _________________________________ O6 Other Operating Attribute _________________________________ Contingency Attributes: C1 Project has options that allow for change during life C2 Project will positively impact company even if value is negative C3 Project can be abandoned easily with some positive value C4 Project permits several options to maximize value C5 Other Cont Attribute _____________________________________ Miscellaneous Attributes: M1 Expands Human Resource Capital M2 Enhances workforce productivity M3 Project meets a critical regulatory, security or specific need

M4 M5 M6 M7 M8

Project fits with company strategy and goals Probability of project success is very high / low risk Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Other Misc Attribute _____________________________________ Total Preliminary Points

Projects with point totals less than 15 may represent poor investments and require additional approval before further analysis and processing. Projects with point totals between 15 and 20 require caution and careful analysis. Projects with point totals greater than 20 may proceed with analysis and submission. Project Summary A major regulatory change is expected to change certain services in the Northeastern United States. In order to meet this new mandate, an investment is required in field personnel, training, and equipment. Revenues from the new services are also forecasted based on demand in California which adopted this regulatory change two years ago. The regulatory change is subject to future modification and therefore, the project is only projected over two years on a month to month basis. There is no initial investment required for this project. Economic Analysis (Cash Flows by Month) Monthly Period Revenues 35,200 39,400 41,000 42,550 43,990 44,550 45,100 46,990 47,800 48,650 49,280 48,300 532,810 47,100 45,500 44,100 42,400 41,000 39,100 38,500 37,900 37,100 36,800 36,400

Date 1 2 3 4 5 6 7 8 9 10 11 12 1992 Total 13 14 15 16 17 18 19 20 21 22 23 31-Jan-92 28-Feb-92 31-Mar-92 30-Apr-92 31-May-92 30-Jun-92 31-Jul-92 31-Aug-92 30-Sep-92 31-Oct-92 30-Nov-92 31-Dec-92 31-Jan-93 28-Feb-93 31-Mar-93 30-Apr-93 31-May-93 30-Jun-93 31-Jul-93 31-Aug-93 30-Sep-93 31-Oct-93 30-Nov-93

Setup Costs -16,000 -5,000 -3,500 -1,500 -1,500 -2,000

Service Costs -3,500 -3,500 -3,500 -4,000 -4,000 -4,000 -4,500 -4,500 -4,500 -5,100 -5,100 -5,100 -5,800 -5,800 -5,800 -6,200 -6,200 -6,200 -6,750 -6,750 -6,750 -7,000 -7,000

Misc / Taxes -2,500 -2,000 -2,000 -1,750 -1,750 -1,750 -1,650 -1,650 -1,650 -1,500 -1,500 -1,500 -1,350 -1,350 -1,350 -1,275 -1,275 -1,275 -1,205 -1,205 -1,205 -1,170 -1,170

Total Outflows -22,000 -10,500 -9,000 -7,250 -7,250 -7,750 -6,150 -6,150 -6,150 -6,600 -6,600 -6,600 -102,000 -17,150 -9,150 -7,150 -7,475 -7,475 -7,475 -7,955 -7,955 -7,955 -8,170 -8,170

Net Cash Flow 13,200 28,900 32,000 35,300 36,740 36,800 38,950 40,840 41,650 42,050 42,680 41,700 29,950 36,350 36,950 34,925 33,525 31,625 30,545 29,945 29,145 28,630 28,230

-10,000 -2,000

24 1993 Total Total

31-Dec-93

36,000 481,900 1,014,710

-5,000

-7,000

-1,170

-13,170 -109,250 -211,250

22,830

Required Rate of Return for Project => Net Present Value Rate of Return (IRR Annual Basis) Discounted Payback

Annual 6.50%

Immediate

Economic Assessment Project has positive Net Present Value? Project has IRR in excess of cost? Project has a positive payback? Yes Yes Yes

Project must meet at least two of the three Economic Criteria, otherwise special approval is required.

Risk Analysis Risk Premium Applied to Project 0.00% (Required for Compliance)

Compare Risk Factor with Government Treasury Bond (lowest risk) to Project Risk Factor: Risk Ranking = 1 for lowest possible risk up to 10 for highest possible risk Probability of Accurate and Reliable Information - Gov't T Bond Risk Ranking assigned to Gov't Treasury Bond ( 1 to 10) Exponential power to apply to Risk Ranking is 2 - (a) Risk Factor = (b) raised to the power (c) Probability of Accurate and Reliable Information for Project Risk Ranking assigned to project (1 to 10) Exponential power to apply to Project Risk Factor for Project Assign probabilities to three possible outcomes for project: P Pesimistic outlook, declining growth, slower volumes, etc. N Normal expected outlook as applied in analysis O Optomistic outlook, better than expected growth Total should equal 100.00% Enter Expected Cash Flows for different outcomes: Standard Deviation (Abs Risk) 1,200 2,684 2,971

Month 1 2 3

Net Cash Flows Pesimist Normal Optomist 12,050 13,129 13,700 26,200 28,589 29,900 28,830 31,486 32,920

Expected Value 13,081 28,493 31,374

4 5 6 7 8 9 10 11 12 Annual Sub Total 13 14 15 16 17 18 19 20 21 22 23 24 Annual Sub Total

31,390 32,710 32,600 34,170 35,870 36,275 36,510 36,810 35,860 25,620 30,850 31,170 29,350 27,960 26,300 25,120 24,470 23,690 23,150 22,660 18,230 687,845

34,545 35,761 35,626 37,505 39,113 39,673 39,839 40,218 39,083 27,919 33,702 34,074 32,033 30,583 28,695 27,565 26,878 26,019 25,422 24,932 20,054 752,443

36,050 37,430 37,250 39,250 40,900 41,470 41,680 42,050 40,920 29,200 35,240 35,630 33,510 31,920 30,010 28,850 28,140 27,300 26,700 26,200 21,100 787,320

34,373 35,637 35,497 37,354 38,984 39,523 39,708 40,073 38,967 413,063 27,830 33,582 33,950 31,926 30,457 28,599 27,456 26,769 25,926 25,337 24,845 19,990 336,665 749,729

3,417 3,425 3,379 3,699 3,648 3,780 3,748 3,808 3,660 2,594 3,189 3,242 3,017 2,889 2,691 2,715 2,672 2,619 2,570 2,565 2,075 72,259 72,259 0.096

Absolute Risk of Project (Std Deviation) Relative Risk of Project (Coeff of Variation)

Revised Economic Analysis using Expected Values Expected Values 13,129 28,589 31,486 34,545 35,761 35,626 37,505 39,113 39,673 39,839 40,218 39,083 27,919 33,702 34,074 32,033 30,583 28,695 27,565 Present Value 13,058 28,282 30,979 33,807 34,808 34,490 36,113 37,458 37,791 37,744 37,898 36,629 26,025 31,247 31,422 29,381 27,900 26,036 24,876

Dates 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

20 26,878 21 26,019 22 25,422 23 24,932 24 20,054 Net Present Value Net Present Value Rate of Return (IRR Annual Basis) Discounted Payback

24,126 23,229 22,573 22,019 17,615 705,507 $705,507 298.86% 0

Immediate

2 3 4 3 0 0

2 3 3 2 0 0

3 1 0 0 0

0 0 5

2 1 0 0 0 34

rn United States. in California

o month basis.

Present Value (simple calc) 13,129 28,589 31,486 34,545 35,761 35,626 37,505 39,113 39,673 39,839 40,218 39,083 27,919 33,702 34,074 32,033 30,583 28,695 27,565 26,878 26,019 25,422 24,932

Present Value (functional) 13,129 28,590 31,488 34,551 35,771 35,642 37,527 39,144 39,714 39,889 40,280 39,155 27,980 33,788 34,173 32,140 30,698 28,815 27,695 27,018 26,168 25,582 25,103

20,054 752,443 Monthly 0.54% 752,443 419% 0

20,204 754,244

Calculate IRR (annual basis): Annual Outflow -102,000 -109,250 Annual Inflow 532,810 481,900

rwise special

Compliance)

k) to Project Risk Factor: 1.00 (a) 1 (b) 1 (c) 1 0.75 4 1.25 6

15.00% 65.00% 20.00% 100.00%

Coeff of Variation (Rel Risk) 0.092 0.094 0.095

Adjusted Inflows

Adjusted Outflows

0.099 0.096 0.095 0.099 0.094 0.096 0.094 0.095 0.094 0.093 0.095 0.095 0.095 0.095 0.094 0.099 0.100 0.101 0.101 0.103 0.104 0.096

Calculate IRR (annual basis): Annual Outflow -102,000 -109,250 Annual Inflow 413,063 336,665

Capital Budgeting Analysis for Medical Services USA
Summarize Economic and Risk Analysis for All Projects Project Ref A B C D E F Required Investment $182,080 $141,000 $1,780,000 $1,215,000 $840,000 $0 Preliminary Points 27 24 31 32 32 34 Justification Code F C C C C E Priority Code 2 3 3 3 3 2

Project Name Diesel Generation System New Clinic in Kansas City Upgrade to DuBois Center Southeastern Upgrades Expand Toronto Urban Centers Regulatory Compliance in NE

* Provide additional narrative information on "strategic" reasons for making this investment since the return is less th

Using Solver for Program Constraints

Financial Modeling Textbooks provide useful examples of how Excel Solver can be used to solve for capital budgeti set of constraints. The following example will illustrate how we could apply Solver for finding the right set of projects Example: Objective: Maximize Net Present Value of Projects Contraints: Year 1: Only $ 200,000 can be spent on all capital projects Year 2: Only $ 150,000 can be spent on all capital projects Year 3: Only $ 120,000 can be spent on all capital projects Year 4: Only $ 100,000 can be spent on all capital projects Year 5: Only $ 75,000 can be spent on all capital projects Five Projects require investments over five years and have the following Net Present Values: Select? 0 = No 1 = Yes A B C D E 1 0 1 1 0

Project Ref

Project Name Annual Marketing Program IT Infrastructure Development Executive Leadership HR Capital Improvement Product Research Total (SumProduct) Maximum Allowed Budget

NPV 650,000 820,000 540,000 701,000 490,000 1,806,385

< - - - - - Five Year Capital Budgets for Each Project - Year 1 Year 2 Year 3 45,000 55,000 25,000 35,000 50,000 99,051 200,000 40,000 60,000 20,000 30,000 45,000 85,282 150,000 38,000 60,000 20,000 30,000 42,000 83,672 120,000

We will enter the following constraints into Solver: $ 45,000 A + $ 55,000 B + $ 25,000 C + $ 35,000 D + $ 50,000 E .LE. $ 200,000 $ 40,000 A + $ 60,000 B + $ 20,000 C + $ 30,000 D + $ 45,000 E .LE. $ 150,000 $ 38,000 A + $ 60,000 B + $ 20,000 C + $ 30,000 D + $ 42,000 E .LE. $ 120,000 $ 35,000 A + $ 58,000 B + $ 18,000 C + $ 28,000 D + $ 40,000 E .LE. $ 100,000

$ 35,000 A + $ 55,000 B + $ 17,000 C + $ 27,000 D + $ 40,000 E .LE. $ 75,000 Each of the above constraints recognizes that we can spend no more than what is budgeted each year. We also have the equation we are trying to solve for: Maximize NPV = $ 650,000 A + $ 820,000 B + $ 540,000 C + $ 701,000 D + $ 490,000 E We will also use as our variables "0" for No and "1" for Yes as to which projects we should select given the above constraints and equation. The "maximum" NPV will show up in our "set" cell which is cell D43. Now go to the main toolbar, select Tools -> Solver and enter the following: 1. Target Cell is D43 2. Equal to: Select the Max button since we are solving for maximum values. 3. By Changing Cells: Select the range B38:B42 as our variables. 4. Subject to Constraints: Add two contraints as follows: B38:B42 .EQ. Binary EQ or = (equal) E43:I43 .LE. E44:I44 LE or <= (less than or equal to) 5. Click on Solve. The Find Solution dialog box may pop up. Click on Answer. Solver will change the variables (which we first entered all as zero's in cells B38:B42) and produce a report (Answer Report 1) Using Excel Solver, we would select Projects A, C, and D!

Project Value $34,761 $4,127 $715,502 $416,502 $818,923 $705,507

9.50% <= weighted average cost of capital rate of return is less than weighted average cost of capital * (years) Rate of Payback Risk Relative Absolute Return Period Factor Risk Risk 10.07% 9.68% 11.12% 9.33% 16.58% 298.86% 6.6 19.0 8.2 8.0 5.5 0.0 9.5 18.5 6.1 11.2 12.3 5.7 0.52 1.26 0.12 0.13 0.24 0.10 $164,674 $540,660 $531,419 $403,897 $800,754 $72,259

ent since the return is less than the cost of investments (cost of capital).

d to solve for capital budgeting program decisions given a ding the right set of projects given a set of contraints:

Budgets for Each Project - - - - - > Year 4 Year 5 35,000 58,000 18,000 28,000 40,000 77,154 100,000 35,000 55,000 17,000 27,000 40,000 75,000 75,000

LE: Less than or Equal to

eted each year. We also

uld select given the above

l change the variables

Microsoft Excel 9.0 Answer Report Worksheet: [CBAnalysis.xls]Summary Report Created: 2/26/2003 9:58:29 AM

Target Cell (Max) Cell Name $D$43 Total (SumProduct) NPV

Original Value 0

Final Value 1,806,385

Adjustable Cells Cell Name $B$38 A 1 = Yes $B$39 B 1 = Yes $B$40 C 1 = Yes $B$41 D 1 = Yes $B$42 E 1 = Yes

Original Value 0 0 0 0 0

Final Value 1 0 1 1 0

Constraints Cell Name $E$43 Total (SumProduct) Year 1 $F$43 Total (SumProduct) Year 2 $G$43 Total (SumProduct) Year 3 $H$43 Total (SumProduct) Year 4 $I$43 Total (SumProduct) Year 5 $B$38 A 1 = Yes $B$39 B 1 = Yes $B$40 C 1 = Yes $B$41 D 1 = Yes $B$42 E 1 = Yes

Cell Value 99,051 85,282 83,672 77,154 75,000 1 0 1 1 0

Formula $E$43<=$E$44 $F$43<=$F$44 $G$43<=$G$44 $H$43<=$H$44 $I$43<=$I$44 $B$38=binary $B$39=binary $B$40=binary $B$41=binary $B$42=binary

Status Not Binding Not Binding Not Binding Not Binding Binding Binding Binding Binding Binding Binding

Slack 100948.6953 64717.9446 36328.20554 22846.15817 0 0 0 0 0 0


				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:445
posted:1/22/2008
language:English
pages:72