Scribd Logo
Upload

Welcome to Scribd!

  • 68 views

    NP EX19 9a JinruiDong 2

    Uploaded by

    Ike Dong
    - The document provides financial projections and calculations for a neighborhood nurse van operated by Mount Moreland Hospital, including a loan to purchase the van, depreciation schedules, projected revenue and expenses, and investment returns. - A $234,000 loan at 4.35% interest over 5 years was used to purchase the van, with monthly payments of $4,347 and interest totaling $26,791 over the life of the loan. - The van is depreciated over 7 years using both straight-line and declining balance methods. - Projected revenue increases annually from $442,000 in 2022 to $645,000 in 2026, while expenses

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    NP EX19 9a JinruiDong 2

    Uploaded by

    Ike Dong
    68 views9 pages
    - The document provides financial projections and calculations for a neighborhood nurse van operated by Mount Moreland Hospital, including a loan to purchase the van, depreciation schedules, projected revenue and expenses, and investment returns. - A $234,000 loan at 4.35% interest over 5 years was used to purchase the van, with monthly payments of $4,347 and interest totaling $26,791 over the life of the loan. - The van is depreciated over 7 years using both straight-line and declining balance methods. - Projected revenue increases annually from $442,000 in 2022 to $645,000 in 2026, while expenses

    Original Description:

    CTCT excel assignment

    Original Title

    NP_EX19_9a_JinruiDong_2

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    - The document provides financial projections and calculations for a neighborhood nurse van operated by Mount Moreland Hospital, including a loan to purchase the van, depreciation schedules, projected revenue and expenses, and investment returns. - A $234,000 loan at 4.35% interest over 5 years was used to purchase the van, with monthly payments of $4,347 and interest totaling $26,791 over the life of the loan. - The van is depreciated over 7 years using both straight-line and declining balance methods. - Projected revenue increases annually from $442,000 in 2022 to $645,000 in 2026, while expenses

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    68 views9 pages

    NP EX19 9a JinruiDong 2

    Uploaded by

    Ike Dong
    - The document provides financial projections and calculations for a neighborhood nurse van operated by Mount Moreland Hospital, including a loan to purchase the van, depreciation schedules, projected revenue and expenses, and investment returns. - A $234,000 loan at 4.35% interest over 5 years was used to purchase the van, with monthly payments of $4,347 and interest totaling $26,791 over the life of the loan. - The van is depreciated over 7 years using both straight-line and declining balance methods. - Projected revenue increases annually from $442,000 in 2022 to $645,000 in 2026, while expenses

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    You are on page 1of 9

    New Perspectives Excel 2019 | Module 9: SAM Project 1a

    Mount Moreland Hospital


    PERFORM FINANCIAL CALCULATIONS

    Author: Jinrui Dong

    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.
    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/2022

    Annual Principal and Cumulative Interest Payments


    Year 1 Year 2 Year 3 Year 4
    1 13 25 37
    Months

    12 24 36 48
    Interest ($9,332) ($7,431) ($5,446) ($3,373)
    Principal ($42,826) ($44,727) ($46,712) ($48,785)
    Principal remaining $191,174 $146,447 $99,735 $50,950
    Remaining % 81.7% 62.6% 42.6% 21.8%
    n

    Year 5
    49

    60 Total
    ($1,208) ($26,791)
    ($50,950) ($234,000)
    ($0)
    0.0%
    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 $28,080 $28,080 $28,080 $28,080 $28,080 $28,080 $28,080
    Cumulative depreciation $28,080 $56,160 $84,240 $112,320 $140,400 $168,480 $196,560
    Depreciated asset value $205,920 $177,840 $149,760 $121,680 $93,600 $65,520 $37,440

    Declining Balance Depreciation


    Year 1 2 3 4 5 6 7
    Annual depreciation $53,820 $41,441 $31,910 $24,571 $18,919 $14,568 $11,217
    Cumulative depreciation $53,820 $95,261 $127,171 $151,742 $170,661 $185,229 $196,446
    Depreciated asset value $180,180 $138,739 $106,829 $82,258 $63,339 $48,771 $37,554

    Yearly depreciation allowance for the first year: $49,140


    Yearly depreciation allowance for the last year: $7,020
    Mount Moreland Hospital - Neighborhood Nurse Van
    Earnings Projections

    Income 2022 2023 2024 2025 2026


    Municipal grants $ 25,000 $ 28,750 $ 32,500 $ 36,250 $ 40,000 Projected Revenue
    Federal grants 72,000 58,000 58,000 55,000 55,000 $700,000
    Insurance reimbursements 345,000 387,664 435,603 489,471 550,000
    Total Revenue $ 442,000 $ 474,414 $ 526,103 $ 580,721 $ 645,000 $600,000

    $500,000
    Expenses
    Supplies $ 108,000 $ 110,000 $ 112,000 $ 115,000 $ 118,000 $400,000
    Pharmaceuticals 127,000 129,625 132,250 134,875 137,500

    Income
    Payroll 140,000 147,000 154,350 162,068 170,171 $300,000
    Maintenance 2,500 2,500 2,500 3,000 3,000
    $200,000
    Insurance 2,800 2,800 2,800 3,100 3,100
    Advertising 10,500 10,500 10,500 10,500 10,500 $100,000
    Total General Expenses $ 390,800 $ 402,425 $ 414,400 $ 428,543 $ 442,271
    $-
    2022 2023 2024 2025 2026
    Initial Earnings $ 51,200 $ 71,989 $ 111,703 $ 152,178 $ 202,729

    Revenue Trend
    $60,000

    $50,000

    $40,000
    Revenue

    $30,000

    $20,000

    $10,000

    $0
    0 10 20 30 40 50 60

    Months in Operation
    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
    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 $ 166,204
    Net present value $ 1,204
    Internal rate of return 7.52%

    You might also like