I need to create a 5 year cash flow for the statement above. I included the cash flow chart I created by I am stuck on interest paid/earned. Please help!

TEAM 1: LOCAL HOSPITAL LAB PROPOSAL Paste PROGRAM Local Hospital Lab START DATE January 1st CAPITAL INVESTMENTS BASE FUNDING FUTURE FUNDING CAPITAL FUNDS REQUIRED: Year 1 Year 2 Year 3 Year 4 395,000 $ Year 5 Equipment Medical Equipment Non-Medical Construction Renovation 1 Furnishings Information Systems Contingency Total Capital Funds Required 350,000 $ 365,000 $ 385,000 $ 415,000 135,000 $ 25,000 $ 80,000 $ 100,000 30,000 50,000 10,000 540,000 100,000 $ 20,000 $ 60,000 $ 15,000 S 125,000 $ 30,000 70,000 25,000 $ 635,000 145,000 30,000 90,000 45,000 725,000 35,000 S 560,000 670,000 INCREMENTAL STATISTICCAL FORECAST PATIENT PROCEDURE VOLUME Specimens Year 1 Year 2 Year 3 Year 4 Year 5 57,500 59,225 61,002 61,002 62,832 64,717 64,717 Total Specimen Volume 57,500 59.225 62.832 ESTIMATED NET REVENUE PER PROCEDURE 19 S 20 S Specimens 18 19 20 FTES Lab Technologist Manager Scheduling/Reception Total FTES 3.00 3.00 3.00 3.00 3.00 1,00 1,00 1,00 1,00 1,00 2.00 2.00 2.00 2.00 2.00 6,00 6,00 6,00 6,00 6,00 INCREMENTAL PROJECTED FINANCIAL STATEMENTS NET REVENUE Year 1 Year 2 Year 3 Year 4 Year 5 1,098,032 1,164,902 MRI Net Revenue Total Net Revenue 1,035,000 1,035,000 1,098,032 1,164,902 1,235,844 s 1,235,844 1,311,107 1,311,107 SALARIES & BENEFITS: Year 1 Year 2 Year 3 Year 4 Year 5 Hourly Salary Hourly Salary Lab Technologist 200,533 $30.00 187,200 193,752 207,552 214,816 Manager Scheduling/Reception Benefits (20 % of Salaries) $60,00 124,800 129,168 133,689 138,368 143,211 Hourly Salary $30.00 124,800 87,360 129,168 133,689 138,368 143,211 96,858 90,418 93,582 561,493 100,248 601,486 S Total Salaries & Benefits 524,160 542.506 581.146 OTHER EXPENSES: Per Unit Cost Medical Supplies Other Supplies $4.00 $1.00 230,000 57,500 236,900 59.225 244,007 61,002 251,327 62,832 27,318 49,173 10,927 81,955 3,825 87,000F S 258,867 64,717 Per Unit Cost Purchased Services 25,000 45,000 10,000 75,000 3,500 87,000 533,000 25,750 46,350 10,300 77,250 26,523 47,741 10,609 79,568 3,713 87,000 560,161 28,138 Marketing Costs Licenses, Taxes & Other 50,648 11,255 84.413 3,939 Bldg & Equipment Rent Utilities 3,605 87,000 Depreciation and Amortization Total Expenses 87,000 $ 546,380 $ 574,356 588.977 TOTAL SALARIES & EXPENSES 1,190,463 1,057,160 $1,088,886 1,121,655 $1,155,502 CONTRIBUTION MARGIN S S (22,160) 9.146 43,247 80,342 120,644 IIndi rect Expenses NET INCOME (22,160) 9,146 43,247 80,342 120,644 Add: Depreciation 87.000 87,000 87,000 F 87,000 87,000 CASH FLOW BEFORE CAPITAL INVESTMENT 64,840.00 s 96,145.90 $130,246.92 $167,342.32 S 207.644.45 Less: Initial & Incremental Capital Investment 635,000 670,000 F 540,000 560,000 F 725,000 CASH FLOW AFTER CAPITAL INVESTMENT (475,160) (463,854) (504,753) (502,658) $ (517,356) CAPITAL SUMMARY SCHEDULE Year Year 2 Year 3 Year 4 Year 5 Total Capital Required Less: Funded from Current Reserves TOTAL FUNDED VIA CAPITAL REQUEST 560,000S 635,000S 540,000 670,000 725,000 670,000 560,000 540,000 635,000 725,000 NET PRESENT VALUE ANALYSIS 6 % NPV Variance to investment Discounted Present Value of Cash Flows $ 543,812 $ 2,770,262 (2,226,450) Present Value of Capital Investments NPV Variance to Investment6% NPV Variance to investment Discounted 8% Present Value of Cash Flows 510,182 $ 2,667,693 (2,157,511) Present Value of Capital Investments NPV Variance to Investment @ 8% NPV Variance to investment Discounted 10% Present Value of Cash Flows 479,489 $ 2,572,450 (2,092,961) Present Value of Capital Investments 10 % NPV Variance to Investment INTERNAL RATE OF RETURN (IRR) & ANNUAL RETURN ON INVESTMENT (ROI) IRR #NUM! ROI Current Year 2 Year 3 Year 4 Year 5 670,000 S 80,342 167,342 Capital Investment 560,000 9,146 s 96,146 540,000 635,000 43,247 725,000 Net Income (22,160) 64,840 120,644 Cash Flow 130,247 207,644 3.85% 6,63% Annual ROI (based on Net Income) Annual ROI (based on Cash Flow) -4.10% 0.83% 2.49% 3.34% 12.01% 8.74% 7,51% 6,96% PAYBACK PERIOD TOTAL CAPITAL REQUIRED Current Year 2 Year 3 Year 4 Year 5 Initial & Incremental Capital Investment Cash Flows 540,000 560,000 96,146 $ 635,000 130,247 $ 670,000 725,000 207,644 64,840 167.342 $ 2.00 0.95 Payback Period (years, months) 2 Year(s); 11 Month(s) NET INCOME BREAKEVEN ANALYSIS Current Year 2 Year 3 Year 4 Year 5 |Average Net Revenue per Unit (patient, visit, etc.) Average Variable Cost per Unit Unit Contribution Margin 19 $ 18 19 20 20 5 $ 5 $ 14 $ 13 14 14 15 Fixed Expenses 639.660 658,861 678.729 672.550 692,889 Breakeven Poi nt (# of units) 49.205 48.660 48,150 47,218 46,717 Fort Myers Lab Facility 5-Year Cash Flow For the Year Ending 12/31/19 12/31/20 12/31/21 12/31/22 12/31/23 Cash at Beginning of Year 540,000 560,000 635,000 670,000 725,000 Cash at End of Year Cash flows from operating activites: 2019 2020 2021 2021 2022 Cash received from services 1,164,902 1,035,000 1,098,032 1,235,844 1,311,107 Other operations Cash paid for (236,000) (258,868 Medical Supplies Other Supplies Employees and suppliers Interest paid (230,000) (57,500) (524,160) (244,008) (251,328) (59,225) (61,002) (524,160) (62,832) (64,717) (524,160) (524,160) (524,160) Interest earned Net Cash Flow from Operations 223,340 278,647 335,732 397,524 463,362 Cash flows investing activities Cash paid for Purchase of property and equipment (485,000) (15,000 (60,000) (540,000) (25,000) (70,000) (555,000) (35,000 (480,000) (590,000) (10,000) (45,000) Contingency (90,000) Securities purchases (50,000) (80,000) (670,000) (725,000) Net Cash Flow from Investing Activities (540,000) (560,000) (635,000) Cash flows from financing activities Repayment of long-term debt Net Cash Flow from Financing Activities C 0 0 0 C Net increase (decrease) in cash an equivalents 811,660 Cash and equivalents, beginning of year Cash and equivalents, end of year (316,660) (261,638) Net Cash Flow (281,353) (299,268) (272,476) TEAM 1: LOCAL HOSPITAL LAB PROPOSAL Paste PROGRAM Local Hospital Lab START DATE January 1st CAPITAL INVESTMENTS BASE FUNDING FUTURE FUNDING CAPITAL FUNDS REQUIRED: Year 1 Year 2 Year 3 Year 4 395,000 $ Year 5 Equipment Medical Equipment Non-Medical Construction Renovation 1 Furnishings Information Systems Contingency Total Capital Funds Required 350,000 $ 365,000 $ 385,000 $ 415,000 135,000 $ 25,000 $ 80,000 $ 100,000 30,000 50,000 10,000 540,000 100,000 $ 20,000 $ 60,000 $ 15,000 S 125,000 $ 30,000 70,000 25,000 $ 635,000 145,000 30,000 90,000 45,000 725,000 35,000 S 560,000 670,000 INCREMENTAL STATISTICCAL FORECAST PATIENT PROCEDURE VOLUME Specimens Year 1 Year 2 Year 3 Year 4 Year 5 57,500 59,225 61,002 61,002 62,832 64,717 64,717 Total Specimen Volume 57,500 59.225 62.832 ESTIMATED NET REVENUE PER PROCEDURE 19 S 20 S Specimens 18 19 20 FTES Lab Technologist Manager Scheduling/Reception Total FTES 3.00 3.00 3.00 3.00 3.00 1,00 1,00 1,00 1,00 1,00 2.00 2.00 2.00 2.00 2.00 6,00 6,00 6,00 6,00 6,00 INCREMENTAL PROJECTED FINANCIAL STATEMENTS NET REVENUE Year 1 Year 2 Year 3 Year 4 Year 5 1,098,032 1,164,902 MRI Net Revenue Total Net Revenue 1,035,000 1,035,000 1,098,032 1,164,902 1,235,844 s 1,235,844 1,311,107 1,311,107 SALARIES & BENEFITS: Year 1 Year 2 Year 3 Year 4 Year 5 Hourly Salary Hourly Salary Lab Technologist 200,533 $30.00 187,200 193,752 207,552 214,816 Manager Scheduling/Reception Benefits (20 % of Salaries) $60,00 124,800 129,168 133,689 138,368 143,211 Hourly Salary $30.00 124,800 87,360 129,168 133,689 138,368 143,211 96,858 90,418 93,582 561,493 100,248 601,486 S Total Salaries & Benefits 524,160 542.506 581.146 OTHER EXPENSES: Per Unit Cost Medical Supplies Other Supplies $4.00 $1.00 230,000 57,500 236,900 59.225 244,007 61,002 251,327 62,832 27,318 49,173 10,927 81,955 3,825 87,000F S 258,867 64,717 Per Unit Cost Purchased Services 25,000 45,000 10,000 75,000 3,500 87,000 533,000 25,750 46,350 10,300 77,250 26,523 47,741 10,609 79,568 3,713 87,000 560,161 28,138 Marketing Costs Licenses, Taxes & Other 50,648 11,255 84.413 3,939 Bldg & Equipment Rent Utilities 3,605 87,000 Depreciation and Amortization Total Expenses 87,000 $ 546,380 $ 574,356 588.977 TOTAL SALARIES & EXPENSES 1,190,463 1,057,160 $1,088,886 1,121,655 $1,155,502 CONTRIBUTION MARGIN S S (22,160) 9.146 43,247 80,342 120,644 IIndi rect Expenses NET INCOME (22,160) 9,146 43,247 80,342 120,644 Add: Depreciation 87.000 87,000 87,000 F 87,000 87,000 CASH FLOW BEFORE CAPITAL INVESTMENT 64,840.00 s 96,145.90 $130,246.92 $167,342.32 S 207.644.45 Less: Initial & Incremental Capital Investment 635,000 670,000 F 540,000 560,000 F 725,000 CASH FLOW AFTER CAPITAL INVESTMENT (475,160) (463,854) (504,753) (502,658) $ (517,356) CAPITAL SUMMARY SCHEDULE Year Year 2 Year 3 Year 4 Year 5 Total Capital Required Less: Funded from Current Reserves TOTAL FUNDED VIA CAPITAL REQUEST 560,000S 635,000S 540,000 670,000 725,000 670,000 560,000 540,000 635,000 725,000 NET PRESENT VALUE ANALYSIS 6 % NPV Variance to investment Discounted Present Value of Cash Flows $ 543,812 $ 2,770,262 (2,226,450) Present Value of Capital Investments NPV Variance to Investment6% NPV Variance to investment Discounted 8% Present Value of Cash Flows 510,182 $ 2,667,693 (2,157,511) Present Value of Capital Investments NPV Variance to Investment @ 8% NPV Variance to investment Discounted 10% Present Value of Cash Flows 479,489 $ 2,572,450 (2,092,961) Present Value of Capital Investments 10 % NPV Variance to Investment INTERNAL RATE OF RETURN (IRR) & ANNUAL RETURN ON INVESTMENT (ROI) IRR #NUM! ROI Current Year 2 Year 3 Year 4 Year 5 670,000 S 80,342 167,342 Capital Investment 560,000 9,146 s 96,146 540,000 635,000 43,247 725,000 Net Income (22,160) 64,840 120,644 Cash Flow 130,247 207,644 3.85% 6,63% Annual ROI (based on Net Income) Annual ROI (based on Cash Flow) -4.10% 0.83% 2.49% 3.34% 12.01% 8.74% 7,51% 6,96% PAYBACK PERIOD TOTAL CAPITAL REQUIRED Current Year 2 Year 3 Year 4 Year 5 Initial & Incremental Capital Investment Cash Flows 540,000 560,000 96,146 $ 635,000 130,247 $ 670,000 725,000 207,644 64,840 167.342 $ 2.00 0.95 Payback Period (years, months) 2 Year(s); 11 Month(s) NET INCOME BREAKEVEN ANALYSIS Current Year 2 Year 3 Year 4 Year 5 |Average Net Revenue per Unit (patient, visit, etc.) Average Variable Cost per Unit Unit Contribution Margin 19 $ 18 19 20 20 5 $ 5 $ 14 $ 13 14 14 15 Fixed Expenses 639.660 658,861 678.729 672.550 692,889 Breakeven Poi nt (# of units) 49.205 48.660 48,150 47,218 46,717 Fort Myers Lab Facility 5-Year Cash Flow For the Year Ending 12/31/19 12/31/20 12/31/21 12/31/22 12/31/23 Cash at Beginning of Year 540,000 560,000 635,000 670,000 725,000 Cash at End of Year Cash flows from operating activites: 2019 2020 2021 2021 2022 Cash received from services 1,164,902 1,035,000 1,098,032 1,235,844 1,311,107 Other operations Cash paid for (236,000) (258,868 Medical Supplies Other Supplies Employees and suppliers Interest paid (230,000) (57,500) (524,160) (244,008) (251,328) (59,225) (61,002) (524,160) (62,832) (64,717) (524,160) (524,160) (524,160) Interest earned Net Cash Flow from Operations 223,340 278,647 335,732 397,524 463,362 Cash flows investing activities Cash paid for Purchase of property and equipment (485,000) (15,000 (60,000) (540,000) (25,000) (70,000) (555,000) (35,000 (480,000) (590,000) (10,000) (45,000) Contingency (90,000) Securities purchases (50,000) (80,000) (670,000) (725,000) Net Cash Flow from Investing Activities (540,000) (560,000) (635,000) Cash flows from financing activities Repayment of long-term debt Net Cash Flow from Financing Activities C 0 0 0 C Net increase (decrease) in cash an equivalents 811,660 Cash and equivalents, beginning of year Cash and equivalents, end of year (316,660) (261,638) Net Cash Flow (281,353) (299,268) (272,476)