Week 8 Assignment – Capital Budget Analysis
Overview
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] 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 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 8–10 slides in length and include speaker notes with each slide.
RESOURCES:
Solution 6: Choose a project based on NPV, IRR, and payback period (linkedin.com)
Payback period in Microsoft Excel (linkedin.com)
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) $94,500.00 $192,780.00 $268,170.75 $231,473.70 $124,561.78 $0.00
Change in NWC -$94,500.00 -$98,280.00 -$75,390.75 $36,697.05 $106,911.92 $124,561.78
Years
Cash Flow from Operations 0 1 2 3 4 5
Net Income $62,460.00 $167,805.00 $212,461.65 $141,634.91 $43,538.47
Depreciation $81,250.00 $60,937.50 $45,703.13 $34,277.34 $52,832.03
Net Cashflow from Operations $143,710.00 $228,742.50 $258,164.78 $175,912.26 $96,370.50
Years
Cash Flow from Investments 0 1 2 3 4 5
Machine #2 -$325,000.00 $50,000.00
Change in Net Cash Flow (NCF) -$94,500.00 -$98,280.00 -$75,390.75 $36,697.05 $106,911.92 $124,561.78
Total NCF (Investments + Ops) -$419,500.00 $45,430.00 $153,351.75 $294,861.83 $282,824.17 $220,932.28
Present Value (PV) -$419,500.00 $41,300.00 $126,736.98 $221,534.05 $193,172.71 $137,181.57
Cumulative Discounted Cashflows -$419,500.00 -$378,200.00 -$251,463.02 -$29,928.96 $163,243.75 $300,425.32
Stage 4: Capital Analyses Discounted Cash Flows
Years Cash Flows Cash Flows discounted to today’s $$ Cumulative Discounted CFs
Discount Rate (r) = 10% 0 -$419,500.00 -$419,500.00 -$419,500.00
Weighted Average Cost of Capital = 10% 1 $45,430.00 $41,300.00 -$378,200.00
2 $153,351.75 $126,736.98 -$251,463.02
Net Present Value (NPV) = $300,425.32 3 $294,861.83 $221,534.05 -$29,928.96
IRR = 30% 4 $282,824.17 $193,172.71 $163,243.75
Discounted Payback = 3.155 Years 5 $220,932.28 $137,181.57 $300,425.32
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.