cmis1
SJBRANA
excel1.xlsxDocumentation
| New Perspectives Excel 2019 | Module 9: SAM Project 1a | |
| Mount Moreland Hospital | |
| PERFORM FINANCIAL CALCULATIONS | |
| Author: | |
| Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. | |
Loan Payments
| Mount Moreland Hospital - Neighborhood Nurse Van | |||||||
| Loan Conditions and Payments | |||||||
| Conditions | Equipped Van | ||||||
| Loan amount (pv) | $234,000 | ||||||
| Annual interest rate | 4.35% | ||||||
| Monthly interest rate (rate) | 0.36% | ||||||
| Loan period in years | 5 | ||||||
| Loan period in months (nper) | 60 | ||||||
| Monthly payment | ($4,347) | ||||||
| Start date of loan | 1/4/22 | ||||||
| Annual Principal and Cumulative Interest Payments | |||||||
| Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | |||
| Months | 1 | 13 | 25 | 37 | 49 | ||
| 12 | 24 | 36 | 48 | 60 | Total | ||
| Interest | ERROR:#NAME? | ||||||
| Principal | $0 | ||||||
| Principal remaining | $234,000 | $234,000 | $234,000 | $234,000 | $234,000 | ||
| Remaining % | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | ||
Depreciation
| Mount Moreland Hospital - Neighborhood Nurse Van | ||||||||
| Depreciation | ||||||||
| Van with Medical Equipment | ||||||||
| Long-term assets (cost) | $ 234,000 | |||||||
| Salvage value (salvage) | $ 37,440 | |||||||
| Life of asset (life) | 7 | |||||||
| Straight-Line Depreciation | ||||||||
| Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | |
| Annual depreciation | ||||||||
| Cumulative depreciation | $0 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | |
| Depreciated asset value | $234,000 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | |
| Declining Balance Depreciation | ||||||||
| Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | |
| Annual depreciation | ||||||||
| Cumulative depreciation | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
| Depreciated asset value | $234,000 | $234,000 | $234,000 | $234,000 | $234,000 | $234,000 | $234,000 | |
| Yearly depreciation allowance for the first year: | ||||||||
| Yearly depreciation allowance for the last year: |
Earnings Projections
| Mount Moreland Hospital - Neighborhood Nurse Van | ||||||
| Earnings Projections | ||||||
| Income | 2022 | 2023 | 2024 | 2025 | 2026 | |
| Municipal grants | $ 25,000 | $ 40,000 | ||||
| Federal grants | 72,000 | 58,000 | 58,000 | 55,000 | 55,000 | |
| Insurance reimbursements | 345,000 | 550,000 | ||||
| Total Revenue | $ 442,000 | $ 58,000 | $ 58,000 | $ 55,000 | $ 645,000 | |
| Expenses | ||||||
| Supplies | $ 108,000 | $ 110,000 | $ 112,000 | $ 115,000 | $ 118,000 | |
| Pharmaceuticals | 127,000 | 129,625 | 132,250 | 134,875 | 137,500 | |
| Payroll | 140,000 | |||||
| Maintenance | 2,500 | 2,500 | 2,500 | 3,000 | 3,000 | |
| Insurance | 2,800 | 2,800 | 2,800 | 3,100 | 3,100 | |
| Advertising | 10,500 | 10,500 | 10,500 | 10,500 | 10,500 | |
| Total General Expenses | $ 390,800 | $ 255,425 | $ 260,050 | $ 266,475 | $ 272,100 | |
| Initial Earnings | $ 51,200 | $ (197,425) | $ (202,050) | $ (211,475) | $ 372,900 | |
Projected Revenue
44562 44927 45292 45658 46023 442000 58000 58000 55000 645000
Income
Revenue Trend
Revenue 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 29443 33967 34268 37665 38775 36834 31995 36935 38021 38566 38775 38756 36012 37345 39552 40207 40113 39765 30599 40215 41750 42154 40205 40450 41599 42702 42998 40012 44175 44998 44765 41248 45060 45621 45987 49958 48155 48014 49109 49121 49342 45695 44992 47622 48252 49013 49211 50001 50225 50100 51225 50026 51245 50875 49765 51232 53012 53187 54675 53412
Months in Operation
Revenue
Monthly Revenue Projections
| Month | Month Number | Revenue |
| Jan 2022 | 1 | 29,443 |
| Feb 2022 | 2 | 33,967 |
| Mar 2022 | 3 | 34,268 |
| Apr 2022 | 4 | 37,665 |
| May 2022 | 5 | 38,775 |
| Jun 2022 | 6 | 36,834 |
| Jul 2022 | 7 | 31,995 |
| Aug 2022 | 8 | 36,935 |
| Sep 2022 | 9 | 38,021 |
| Oct 2022 | 10 | 38,566 |
| Nov 2022 | 11 | 38,775 |
| Dec 2022 | 12 | 38,756 |
| Jan 2023 | 13 | 36,012 |
| Feb 2023 | 14 | 37,345 |
| Mar 2023 | 15 | 39,552 |
| Apr 2023 | 16 | 40,207 |
| May 2023 | 17 | 40,113 |
| Jun 2023 | 18 | 39,765 |
| Jul 2023 | 19 | 30,599 |
| Aug 2023 | 20 | 40,215 |
| Sep 2023 | 21 | 41,750 |
| Oct 2023 | 22 | 42,154 |
| Nov 2023 | 23 | 40,205 |
| Dec 2023 | 24 | 40,450 |
| Jan 2024 | 25 | 41,599 |
| Feb 2024 | 26 | 42,702 |
| Mar 2024 | 27 | 42,998 |
| Apr 2024 | 28 | 40,012 |
| May 2024 | 29 | 44,175 |
| Jun 2024 | 30 | 44,998 |
| Jul 2024 | 31 | 44,765 |
| Aug 2024 | 32 | 41,248 |
| Sep 2024 | 33 | 45,060 |
| Oct 2024 | 34 | 45,621 |
| Nov 2024 | 35 | 45,987 |
| Dec 2024 | 36 | 49,958 |
| Jan 2025 | 37 | 48,155 |
| Feb 2025 | 38 | 48,014 |
| Mar 2025 | 39 | 49,109 |
| Apr 2025 | 40 | 49,121 |
| May 2025 | 41 | 49,342 |
| Jun 2025 | 42 | 45,695 |
| Jul 2025 | 43 | 44,992 |
| Aug 2025 | 44 | 47,622 |
| Sep 2025 | 45 | 48,252 |
| Oct 2025 | 46 | 49,013 |
| Nov 2025 | 47 | 49,211 |
| Dec 2025 | 48 | 50,001 |
| Jan 2026 | 49 | 50,225 |
| Feb 2026 | 50 | 50,100 |
| Mar 2026 | 51 | 51,225 |
| Apr 2026 | 52 | 50,026 |
| May 2026 | 53 | 51,245 |
| Jun 2026 | 54 | 50,875 |
| Jul 2026 | 55 | 49,765 |
| Aug 2026 | 56 | 51,232 |
| Sep 2026 | 57 | 53,012 |
| Oct 2026 | 58 | 53,187 |
| Nov 2026 | 59 | 54,675 |
| Dec 2026 | 60 | 53,412 |
Investment
| Mount Moreland Hospital - Neighborhood Nurse Van | |||
| Investment Returns | |||
| Investor Repayment Schedule | |||
| Payments | Net Cash Flow | ||
| Startup | $ (165,000) | $ (165,000) | |
| Year 1 | 25,000 | (140,000) | |
| Year 2 | 32,500 | (107,500) | |
| Year 3 | 35,500 | (72,000) | |
| Year 4 | 40,500 | (31,500) | |
| Year 5 | 41,500 | 10,000 | |
| Year 6 | 40,000 | 50,000 | |
| Desired rate of return | 7.30% | ||
| Present value | |||
| Net present value | $ (165,000) | ||
| Internal rate of return | |||
