highlighted in green and negative values will be highlighted in red. Apply conditional formatting for the net annual benefits, break-even totals, and NPV. If you entered the correct formulas and applied conditional formatting as directed, your Break-Even, NPV, and IRR Calculations section should match the figure below. Answer the Questions Your last task for the tutorial is to answer the four questions at the bottom of the spreadsheet, which are: - Based on the variables provided, in what year will the project break even? (Enter the year) - Based on the target rate of return of 15%, should Surgical Associates go forward with this project? (Enter Yes or No) - Should Surgical Associates go forward with the project if the profit increase per additional surgery is $525 ? (Enter Yes or No) - What is the NPV if the annual license fees are decreased by $400 per surgeon? (Enter the NPV value from cell (27) Scenario Information You are interning at Surgical Associates, which is a group of 18 surgery centers providing outpatient surgical services in five metropolitan areas. Surgical Associates is thinking about implementing a practice management system in all of their centers to improve the efficiency of their operations. Because of your Excel skills learned during your college coursework, you have been asked to work with an administrator to create a break-even analysis that will heip Surgical Associates decide if implementing this particular system will be financially beneficial to the group. Costs - Surgical Associates consists of 18 surgery centers and the plan is to install the practice management system in every center on the following schedule: two centers in 2023, three centers in 2024, five centers in 2025, and eight centers in 2026. - There is a one-time installation cost of $195,000 per center in the year of installation. - The system's hardware and software will require maintenance and upgrades. The estimated annual total for those expenses is $36,500 per center with a system installed. There is also an annual system license fee of $2,900 per surgeon and the number of new licenses each year will be as follows: 8 in 2023, 11 in 2024, 21 in 2025, and 34 in 2026. Benefits - The system will improve efficiencies in managing the surgery suites so that each center will be able to perform one additional surgery each day. - Surgery centers are open weekdays for a totat of 255 days each year. - Each additional surgery will increase profit by an average of $520. - Surgical Associates will also benefit from federal meaningful use payments designed to incentivize the adoption of health information systems. - Each surgeon will be eligible for $4,500 in annual meaningful use payments for working at a center with a practice management system. Calculatiens The break-even analysis will cover a 4-year period from 2023-2026. For each year: - Caleulate the system costs and benefits. - Cakulate the net benefiss of the system and the break-even totals for the system. Break-even totals are simply the accumulated net benefits. The project breaks even when the accumulated net benefits value is positive. For the total 4 -year period: - Caiculate the net present value (NPV) of the investment using a rate of 15%. - Calculate the internal rate of retum (IRR). Break-Even Structure Your Break-fiven Analysis will contain the following sections: - Variables this section is where all fixed values given to you in the instructions are stored. - Cestas this section uses the variables related to system costs to calculate the total costs that will be required to implement and operate the switem. - Benefits this section uses the variables related to system benefits to calculate the total benefits anticipated froen the implementation of the system. fx A C D E F Break Even Analysis for Surgical Associates Practice Management System Varbbles 2023 2024 2025 2026 Number of sumery centers with installations each year Number of new system licenses each year One-time installation cost per surgery center Kardware/software maintenance and uperades per center Annual system license fee per surgeon Annual meaningful use payments per surgeon Profit increase per additional surgery Surgery center business days per year NPV Rate 12 13 Costs 2023 2024 2025 2026 \( 1 4 \longdiv { \text { System installation costs } } \) 15 Hardware/soltware maintenance and upgrades 15 Ucense Fee per physician 17 Total Annual Costs 18 19 Benefes 20 Addisonal surgery revenue 2023 2024 2025 2026 21 Meaninctul use parments to surgeons 22 Total Annual Benefits 23 24 Break-Even, NPV, and ish Cakulations 25 Net Arinual Benefits 26 Break-Even Totals (Accoumulated Net Benefils) Net Present Value (5OV) Intemal Rate of Hetium (IRP) Question fased on the vahables provided, in what year will the propet break 31 even? Based on the target rate of retum of 15\%, shoudd Surgical Associates so 32 forwart with this projed? Should Surgcal Associates go forwird with the project if to profin 3y increate per addedonal surgery le 5sas? 36 wreon? 2023 2024 2025 2026 - greak-Even, NPV, and IRR Cakubtions: this section calculates simple break-even totak, net present value, and the internal rate of return ior the project that will indieate the relative financial value of going forward wh the project. You could use the previous information to construct a spreadsheet break-even analysis, but instead I would like you to use the template, Break-Even_Tutorial_Template_Spring2023.x/sx, included with this walkthrough and the instructions so that everyone is working from the same initial structure. Complete the Template Start filing in the template at the top and work down. You can format the cell values immediately or wait until the end, but the example figures that follow will show the formatted versions. All counts (centers, licenses, business days) should be in the Number format with no decimal places. AH monetary values should be in the Accounting format with no decimal places. The NPV rate should be in the Percentage format with no decimal places and the LRR value should be in the Percentage format with two decimal places. Enter the variables provided in the Scenario above in the The system installation couts are a product of the number of surgery centers with installations each year and the one-time installation cost per center. Because the values of many of the variables are included one time in column B, but are used in columns C-F, you must use absolute referencing when referring to those variables in your formulss so that you can create a formula once for column C and then copy it to columns D,E, and F, in cell C14, enter =C3*5aS5 and then copy that formule to 014, E14, and F14. Unike the installation costs, the hardware/software maintenance and upgrades costs apply every year a system is in operation. Therefore, you must use a running total of centers with systems installed snd multiple that by the hardware/vaftware maintenance and upcrades per center. A running total of centers with systems that can be copied troen cell to cell is possible with the SUM function. Specificaliv, if you use the function = SUM (SCS3.C3) as part of the feamula in cell C15, the copled formulas will maintain the locked reference to C3 as the starting cell of the SUM fusctien, but shift the end cell to the current year, which will include all centers up to that year. The Fis: The annual system license fee also applies in each year a system is in operation, but it will be based on the running formula to D16, F16, and $16. The tast cott calcubtios is the total annual costs. This is simply the sum of all couts for each year. Enter the fermulas in fows 14 through 17 , vour Costs section should match the ficure below. The additional surgery revenue is a product of the running total of surgery centers with systems, the profit increase per additional surgery, and the business days per year. In cell C20, enter the formula -SUM(\$C\$3:C3)*\$B\$9*\$B\$10 and then copy that formula to D20, E20, and F20. Meaningful use payments is a product of the number of surgeons working in offices with systems, which is based on a running total of system licenses, and the annual meaningful use payments per surgeon. In cell C21, enter =SUM(\$C\$4:C4) $ S\$8 and then copy that formula to D21, E21, and F21. Now use the Sum function in cell C22 to sum the benefits the way you did for the costs. If you entered the correct formulas in rows 20 through 22 , your Benefits section should match the figure below. The net annual benefits are simply the total annual benefits minus the total annual costs for each year. in cell C25, enter =C22-C17 and then copy that formula to D25, E25, and F25. The break-even totals are the running total of the net benefits so you can use the same type of formula you have been using to calculate running totals. Enter that formula in cell C26 and then copy the formula to D26, E26, and F26. Typically, projects will have higher startup costs with growing benefits that eventually make up for those high Initial costs. The break-even totals simply tell you if the total benefits for the project ever exceed the total costs and the break-even year is the year in which the break-even total becomes positive for the first time. Net present value (NPV) and the internal rate of return (IRR) are based on the premise that a dollar today is more valuable than a dollar tomorrow because the dollar today can be used to earn more money. Therefore, NPV and IaR calculations discount the value of future dollars to produce a current value of a multi-period investment. NPV and ikh are calculated once for an entire project and not for every year. NPV requires a desired rate of return and produces a dollar value for the project that is based on the net annual benefits. A positive NPV indicates the project will produce a higher rate of return that the desired rate, while a negathe NPV indicates the project will produce a lower rate of return that the desired rate. In C27 enter, aNPViB 11.02525]. MR does not require a desired rate of return tike the NPV, because it produces the actual rate of return based on the set annual benefits. In C28 enter, =(RR(C25:F25). When values can be either positive or negative, conditional formatting enables a faster visual ascessment of those values. Select the cell or cells vou want to conditionally format and then select the Conditional formatting option. Conditional formarting is an option in the middie of the Home tab of the Excel Ribbon. There are a number of conditional formatting options. We will use the first one, which is Highlight Cell Rules. Under that option, relect Greater than.i, enter 0 as the reference value, select the option Green Fill with Dark Green Text, and then click OK. Now so back to the Conditional Formatting menu and this time select Less than..., enter 0 as the reference value, select the option Red Fal with Dark Red Text, and the click OK. Now positive values in those formatted cells will be highlighted in green and negative values will be highlighted in red. Apply conditional formatting for the net annual benefits, break-even totals, and NPV. If you entered the correct formulas and applied conditional formatting as directed, your Break-Even, NPV, and IRR Calculations section should match the figure below. Answer the Questions Your last task for the tutorial is to answer the four questions at the bottom of the spreadsheet, which are: - Based on the variables provided, in what year will the project break even? (Enter the year) - Based on the target rate of return of 15%, should Surgical Associates go forward with this project? (Enter Yes or No) - Should Surgical Associates go forward with the project if the profit increase per additional surgery is $525 ? (Enter Yes or No) - What is the NPV if the annual license fees are decreased by $400 per surgeon? (Enter the NPV value from cell (27) Scenario Information You are interning at Surgical Associates, which is a group of 18 surgery centers providing outpatient surgical services in five metropolitan areas. Surgical Associates is thinking about implementing a practice management system in all of their centers to improve the efficiency of their operations. Because of your Excel skills learned during your college coursework, you have been asked to work with an administrator to create a break-even analysis that will heip Surgical Associates decide if implementing this particular system will be financially beneficial to the group. Costs - Surgical Associates consists of 18 surgery centers and the plan is to install the practice management system in every center on the following schedule: two centers in 2023, three centers in 2024, five centers in 2025, and eight centers in 2026. - There is a one-time installation cost of $195,000 per center in the year of installation. - The system's hardware and software will require maintenance and upgrades. The estimated annual total for those expenses is $36,500 per center with a system installed. There is also an annual system license fee of $2,900 per surgeon and the number of new licenses each year will be as follows: 8 in 2023, 11 in 2024, 21 in 2025, and 34 in 2026. Benefits - The system will improve efficiencies in managing the surgery suites so that each center will be able to perform one additional surgery each day. - Surgery centers are open weekdays for a totat of 255 days each year. - Each additional surgery will increase profit by an average of $520. - Surgical Associates will also benefit from federal meaningful use payments designed to incentivize the adoption of health information systems. - Each surgeon will be eligible for $4,500 in annual meaningful use payments for working at a center with a practice management system. Calculatiens The break-even analysis will cover a 4-year period from 2023-2026. For each year: - Caleulate the system costs and benefits. - Cakulate the net benefiss of the system and the break-even totals for the system. Break-even totals are simply the accumulated net benefits. The project breaks even when the accumulated net benefits value is positive. For the total 4 -year period: - Caiculate the net present value (NPV) of the investment using a rate of 15%. - Calculate the internal rate of retum (IRR). Break-Even Structure Your Break-fiven Analysis will contain the following sections: - Variables this section is where all fixed values given to you in the instructions are stored. - Cestas this section uses the variables related to system costs to calculate the total costs that will be required to implement and operate the switem. - Benefits this section uses the variables related to system benefits to calculate the total benefits anticipated froen the implementation of the system. fx A C D E F Break Even Analysis for Surgical Associates Practice Management System Varbbles 2023 2024 2025 2026 Number of sumery centers with installations each year Number of new system licenses each year One-time installation cost per surgery center Kardware/software maintenance and uperades per center Annual system license fee per surgeon Annual meaningful use payments per surgeon Profit increase per additional surgery Surgery center business days per year NPV Rate 12 13 Costs 2023 2024 2025 2026 \( 1 4 \longdiv { \text { System installation costs } } \) 15 Hardware/soltware maintenance and upgrades 15 Ucense Fee per physician 17 Total Annual Costs 18 19 Benefes 20 Addisonal surgery revenue 2023 2024 2025 2026 21 Meaninctul use parments to surgeons 22 Total Annual Benefits 23 24 Break-Even, NPV, and ish Cakulations 25 Net Arinual Benefits 26 Break-Even Totals (Accoumulated Net Benefils) Net Present Value (5OV) Intemal Rate of Hetium (IRP) Question fased on the vahables provided, in what year will the propet break 31 even? Based on the target rate of retum of 15\%, shoudd Surgical Associates so 32 forwart with this projed? Should Surgcal Associates go forwird with the project if to profin 3y increate per addedonal surgery le 5sas? 36 wreon? 2023 2024 2025 2026 - greak-Even, NPV, and IRR Cakubtions: this section calculates simple break-even totak, net present value, and the internal rate of return ior the project that will indieate the relative financial value of going forward wh the project. You could use the previous information to construct a spreadsheet break-even analysis, but instead I would like you to use the template, Break-Even_Tutorial_Template_Spring2023.x/sx, included with this walkthrough and the instructions so that everyone is working from the same initial structure. Complete the Template Start filing in the template at the top and work down. You can format the cell values immediately or wait until the end, but the example figures that follow will show the formatted versions. All counts (centers, licenses, business days) should be in the Number format with no decimal places. AH monetary values should be in the Accounting format with no decimal places. The NPV rate should be in the Percentage format with no decimal places and the LRR value should be in the Percentage format with two decimal places. Enter the variables provided in the Scenario above in the The system installation couts are a product of the number of surgery centers with installations each year and the one-time installation cost per center. Because the values of many of the variables are included one time in column B, but are used in columns C-F, you must use absolute referencing when referring to those variables in your formulss so that you can create a formula once for column C and then copy it to columns D,E, and F, in cell C14, enter =C3*5aS5 and then copy that formule to 014, E14, and F14. Unike the installation costs, the hardware/software maintenance and upgrades costs apply every year a system is in operation. Therefore, you must use a running total of centers with systems installed snd multiple that by the hardware/vaftware maintenance and upcrades per center. A running total of centers with systems that can be copied troen cell to cell is possible with the SUM function. Specificaliv, if you use the function = SUM (SCS3.C3) as part of the feamula in cell C15, the copled formulas will maintain the locked reference to C3 as the starting cell of the SUM fusctien, but shift the end cell to the current year, which will include all centers up to that year. The Fis: The annual system license fee also applies in each year a system is in operation, but it will be based on the running formula to D16, F16, and $16. The tast cott calcubtios is the total annual costs. This is simply the sum of all couts for each year. Enter the fermulas in fows 14 through 17 , vour Costs section should match the ficure below. The additional surgery revenue is a product of the running total of surgery centers with systems, the profit increase per additional surgery, and the business days per year. In cell C20, enter the formula -SUM(\$C\$3:C3)*\$B\$9*\$B\$10 and then copy that formula to D20, E20, and F20. Meaningful use payments is a product of the number of surgeons working in offices with systems, which is based on a running total of system licenses, and the annual meaningful use payments per surgeon. In cell C21, enter =SUM(\$C\$4:C4) $ S\$8 and then copy that formula to D21, E21, and F21. Now use the Sum function in cell C22 to sum the benefits the way you did for the costs. If you entered the correct formulas in rows 20 through 22 , your Benefits section should match the figure below. The net annual benefits are simply the total annual benefits minus the total annual costs for each year. in cell C25, enter =C22-C17 and then copy that formula to D25, E25, and F25. The break-even totals are the running total of the net benefits so you can use the same type of formula you have been using to calculate running totals. Enter that formula in cell C26 and then copy the formula to D26, E26, and F26. Typically, projects will have higher startup costs with growing benefits that eventually make up for those high Initial costs. The break-even totals simply tell you if the total benefits for the project ever exceed the total costs and the break-even year is the year in which the break-even total becomes positive for the first time. Net present value (NPV) and the internal rate of return (IRR) are based on the premise that a dollar today is more valuable than a dollar tomorrow because the dollar today can be used to earn more money. Therefore, NPV and IaR calculations discount the value of future dollars to produce a current value of a multi-period investment. NPV and ikh are calculated once for an entire project and not for every year. NPV requires a desired rate of return and produces a dollar value for the project that is based on the net annual benefits. A positive NPV indicates the project will produce a higher rate of return that the desired rate, while a negathe NPV indicates the project will produce a lower rate of return that the desired rate. In C27 enter, aNPViB 11.02525]. MR does not require a desired rate of return tike the NPV, because it produces the actual rate of return based on the set annual benefits. In C28 enter, =(RR(C25:F25). When values can be either positive or negative, conditional formatting enables a faster visual ascessment of those values. Select the cell or cells vou want to conditionally format and then select the Conditional formatting option. Conditional formarting is an option in the middie of the Home tab of the Excel Ribbon. There are a number of conditional formatting options. We will use the first one, which is Highlight Cell Rules. Under that option, relect Greater than.i, enter 0 as the reference value, select the option Green Fill with Dark Green Text, and then click OK. Now so back to the Conditional Formatting menu and this time select Less than..., enter 0 as the reference value, select the option Red Fal with Dark Red Text, and the click OK. Now positive values in those formatted cells will be