For this assignment, you will be provided with a spreadsheet containing projected numbers for two different patient services programs. You will n
For this assignment, you will be provided with a spreadsheet containing projected numbers for two different patient services programs. You will need to download the Program Projections [XLSX] Download Program Projections [XLSX]spreadsheet and use it to conduct your analysis.
Instructions
You are a member of the financial services department at Benson Regional Medical Center. The chief financial officer and chair of the capital budgeting committee, Dana Foster, has requested that you perform some capital analysis of two proposed patient service programs.
You have been provided with a spreadsheet that covers much of the projected financials for each of the proposed programs. Your task is to perform an analysis of that information and provide your recommendation to the capital budgeting committee as to which program they should pursue.
You have been asked to create a presentation to present your findings to the capital budgeting committee.
Using the provided spreadsheet, complete a capital budgeting analysis on the information provided in the spreadsheet. Specifically, you will need to identify a net present value (NPV), internal rate of return (IRR), and a discounted payback period for proposed Program #1 and Program #2. You will present your finding in a presentation.
- Design a PowerPoint presentation for the capital budgeting committee that includes all of the following:
- Create a brief 1-2 slide description of the proposed programs.
- Develop a comparison between the cash flow projects of each program from Year 0 to Year 5. Highlight the differences.
- Compare the results and interpretation of the discounted payback period between both programs.
- Compare the net present value (NPV) for each program.
- Compare the Internal rate of return (IRR) for each program.
- Develop a recommendation for which program the capital budgeting committee should take into consideration. Include supporting rationale.
Formatting Requirements
The presentation should be 8-10 slides in length and include speaker notes with each slide.
Program #1
| Stage 1: Depreciation (25% reducing balance) | Salvage Value: | $ 30,000.00 | ||||||
| Years | Initial Value | Depreciation | Residual Value | The salvage value is how much the firm believes they can sell the asset for once it reached the end of its' economic life. By "end of its' economic life", this means it has been fully depreciated. Depreciation is a non-case expense. These are monies that the government allows a compay to set aside tax-free, so that they will have cash available to assist in replacing the asset once it reaches the end of its' economic life. | ||||
| 1 | $170,000.00 | $42,500.00 | $127,500.00 | |||||
| 2 | $127,500.00 | $31,875.00 | $95,625.00 | |||||
| 3 | $95,625.00 | $23,906.25 | $71,718.75 | |||||
| 4 | $71,718.75 | $17,929.69 | $53,789.06 | |||||
| 5 | $53,789.06 | $13,789.06 | $40,000.00 | |||||
| Operating Revenues | Inflationary Adjustment to Revenues: | 2.00% | ||||||
| Years | Volume | Price per Unit of Service | Operating Revenue | There are several ways to forecast future inflation. I normally use an inflationary adjustment between 2% and 3%, this is typically aligns with the 10 year Treasury rates. For the costs, in this scenario, I went a little more aggressive on the projected inflationary rate. Many finance folks will be a little more conservation when forecasting revenues (using a lower inflationary rate) and a bit more aggressive, yet realistic, when using an inflationary rate for costs. | ||||
| 1 | 3,000 | $130.00 | $390,000.00 | |||||
| 2 | 6,500 | $132.60 | $861,900.00 | |||||
| 3 | 9,200 | $135.25 | $1,244,318.40 | |||||
| 4 | 7,500 | $137.96 | $1,034,677.80 | |||||
| 5 | 4,000 | $140.72 | $562,864.72 | |||||
| Operating Costs | Inflationary Adjustment to Costs: | 10% | Operating revenue are the revenues generated from the sell of good and services by a firm. | |||||
| Years | Volume | Cost per Unit of Service | Operating Costs | |||||
| 1 | 3000 | $95.00 | $285,000.00 | Operating costs are those costs necessary to run the business. Operating costs include the cost of goods sold (COGS), which are costs directly related to the production of goods and services, but also includes other business expenses. | ||||
| 2 | 6500 | $104.50 | $679,250.00 | |||||
| 3 | 9200 | $114.95 | $1,057,540.00 | |||||
| 4 | 7500 | $126.45 | $948,337.50 | |||||
| 5 | 4000 | $139.09 | $556,358.00 | |||||
| Stage 2 | Revenues in Excess of Expenses | Corporate Tax Rate: | 28% | [Federal & State] | ||||
| Years | ||||||||
| 1 | 2 | 3 | 4 | 5 | ||||
| Operating Revenues | $390,000.00 | $861,900.00 | $1,244,318.40 | $1,034,677.80 | $562,864.72 | |||
| Operating Costs | $285,000.00 | $679,250.00 | $1,057,540.00 | $948,337.50 | $556,358.00 | |||
| Depreciation | $42,500.00 | $31,875.00 | $23,906.25 | $17,929.69 | $13,789.06 | |||
| Earnings Before Tax (EBT) | $62,500.00 | $150,775.00 | $162,872.15 | $68,410.61 | -$7,282.34 | |||
| Tax Rate (28%) | $17,500.00 | $42,217.00 | $45,604.20 | $19,154.97 | -$2,039.06 | |||
| Net Income | $45,000.00 | $108,558.00 | $117,267.95 | $49,255.64 | -$5,243.28 | |||
| Stage 3: Cash Flow Estimations | Net Working Capital = 15% of Revenues | |||||||
| Years | ||||||||
| 0 | 1 | 2 | 3 | 4 | 5 | |||
| Net Working Capital (Current Assets) | $58,500.00 | $129,285.00 | $186,647.76 | $155,201.67 | $84,429.71 | $0.00 | ||
| Change in NWC | -$58,500.00 | -$70,785.00 | -$57,362.76 | $31,446.09 | $70,771.96 | $84,429.71 | ||
| Years | ||||||||
| Cash Flow from Operations | 0 | 1 | 2 | 3 | 4 | 5 | ||
| Net Income | $45,000.00 | $108,558.00 | $117,267.95 | $49,255.64 | -$5,243.28 | |||
| Depreciation | $42,500.00 | $31,875.00 | $23,906.25 | $17,929.69 | $13,789.06 | |||
| Net Cashflow from Operations | $87,500.00 | $140,433.00 | $141,174.20 | $67,185.33 | $8,545.78 | |||
| Years | ||||||||
| Cash Flow from Investments | 0 | 1 | 2 | 3 | 4 | 5 | ||
| Machine #1 | -$170,000.00 | $30,000.00 | ||||||
| Change in Net Cash Flow (NCF) | -$58,500.00 | -$70,785.00 | -$57,362.76 | $31,446.09 | $70,771.96 | $84,429.71 | ||
| Total NCF (Invest & Ops) | -$228,500.00 | $16,715.00 | $83,070.24 | $172,620.29 | $137,957.29 | $92,975.49 | ||
| Present Value (PV) | -$228,500.00 | $15,195.45 | $68,653.09 | $129,692.18 | $94,226.69 | $57,730.46 | ||
| Cumulative Discounted Cashflows | -$228,500.00 | -$213,304.55 | -$144,651.45 | -$14,959.28 | $79,267.41 | $136,997.87 | ||
| Stage 4: Capital Analyses | Discounting Cash Flows | |||||||
| Years | Cash Flows | Cash Flows discounted to today's $$ | Cumulative Discounted CFs | |||||
| Discount Rate (r) = | 10% | 0 | -$228,500.00 | -$228,500.00 | -$228,500.00 | |||
| Weighted Average Cost of Capital = | 10% | 1 | $16,715.00 | $15,195.45 | -$213,304.55 | |||
| 2 | $83,070.24 | $68,653.09 | -$144,651.45 | |||||
| Net Present Value (NPV) = | $136,997.87 | 3 | $172,620.29 | $129,692.18 | -$14,959.28 | |||
| IRR = | 27% | 4 | $137,957.29 | $94,226.69 | $79,267.41 | |||
| Discounted Payback = | 3.16 | Years | 5 | $92,975.49 | $57,730.46 | $136,997.87 | ||
| The discount rate is typically set equal to a firm's cost of capital. This rate is used to discount future figures back to today's dollars. This is necessary when computing the NPV and discounted payback period. The weighted average costs of capital represents how much it costs a company to raise one dollar of investment capital. It is always expressed as a %. In this scenario, the cost of capital is 10%, which means it costs the company 10 cents to raise one $1 of capital. This is important when needing to interpret the internal rate of return (IRR). The IRR is always expressed as a % and refers to the expected return for every $1 invested in a project. In this case, the company expects an IRR of 33%, which means that anticipate earnes 33 cents on every $1 invested in the project. If the cost of capital is 10 center to raise a dollar of capital and the IRR is 33 cents per dollar of invested capital, then the firm stands to make 23 cents in profit for each $1 invested. This of course assumes that the financial assumptions and cash flow projections were accurate. Typically, a firm will expect a 20% to 30% IRR to consider an investment opportunity. This takes into account possible issues with the underlying assumptions. | ||||||||
Program #2
| Stage 1: Depreciation (25% reducing balance) | Salvage Value: | $ 50,000.00 | ||||||
| Years | Initial Value | Depreciation | Residual Value | |||||
| 1 | $325,000.00 | $81,250.00 | $243,750.00 | |||||
| 2 | $243,750.00 | $60,937.50 | $182,812.50 | |||||
| 3 | $182,812.50 | $45,703.13 | $137,109.38 | |||||
| 4 | $137,109.38 | $34,277.34 | $102,832.03 | |||||
| 5 | $102,832.03 | $52,832.03 | $50,000.00 | |||||
| Operating Revenues | Inflationary Adjustment to Revenues: | 2.00% | ||||||
| Years | Volume | Price per Unit of Service | Operating Revenue | |||||
| 1 | 3,500 | $135.00 | $472,500.00 | |||||
| 2 | 7,000 | $137.70 | $963,900.00 | |||||
| 3 | 9,500 | $141.14 | $1,340,853.75 | |||||
| 4 | 8,000 | $144.67 | $1,157,368.50 | |||||
| 5 | 4,200 | $148.29 | $622,808.92 | |||||
| Operating Costs | Inflationary Adjustment to Costs: | 10% | ||||||
| Years | Volume | Cost per Unit of Service | Operating Costs | |||||
| 1 | 3500 | $87.00 | $304,500.00 | You'll notice that the volume increases as the technology is implemented, reaches its' high point in year 3, and then begins a decline. Why is this? This is due to market forces such as increased competition. | ||||
| 2 | 7000 | $95.70 | $669,900.00 | |||||
| 3 | 9500 | $105.27 | $1,000,065.00 | |||||
| 4 | 8000 | $115.80 | $926,376.00 | |||||
| 5 | 4000 | $127.38 | $509,506.80 | |||||
| Stage 2 | Revenues in Excess of Expenses | Corporate Tax Rate: | 28% | [Federal & State] | ||||
| Years | ||||||||
| 1 | 2 | 3 | 4 | 5 | ||||
| Operating Revenues | $472,500.00 | $963,900.00 | $1,340,853.75 | $1,157,368.50 | $622,808.92 | |||
| Operating Costs | $304,500.00 | $669,900.00 | $1,000,065.00 | $926,376.00 | $509,506.80 | |||
| Depreciation | $81,250.00 | $60,937.50 | $45,703.13 | $34,277.34 | $52,832.03 | |||
| Earnings Before Tax (EBT) | $86,750.00 | $233,062.50 | $295,085.63 | $196,715.16 | $60,470.09 | |||
| Tax Rate (28%) | $24,290.00 | $65,257.50 | $82,623.97 | $55,080.24 | $16,931.63 | |||
| Net Income | $62,460.00 | $167,805.00 | $212,461.65 | $141,634.91 | $43,538.47 | Working capital is made up of the firm's current assets, which includes cash, short-term securities, account receivables, prepaid expenses, and inventory. The current assets are located in the balance sheet. | ||
| Stage 3: Cash Flow Estimations | Net Working Capital = 20% of Revenues | |||||||
| Years | ||||||||
| 0 | 1 | 2 | 3 | 4 | 5 | |||
| Net Working Capital (Current Assets) |
Collepals.com Plagiarism Free Papers Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers. Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS Why Hire Collepals.com writers to do your paper? Quality- We are experienced and have access to ample research materials. We write plagiarism Free Content Confidential- We never share or sell your personal information to third parties. Support-Chat with us today! We are always waiting to answer all your questions.  All Rights Reserved Terms and Conditions |