Assignment: Budgeting Select an organization; either Metropolis Health System from the Chapter 28 Case Study or one of the organizations presented in either Chapter 27 or one of the three Mini-Case Studies in Chapters 29-31. 1. Using the organization selected, create a budget for the next fiscal year. Set out the details of all assumptions you needed in order to build this budget. Use the Checklist for Building a Budget (Exhibit 15-2) and critique your own budget. In short, you will submit an excel sheet for your budget (as shown at the bottom of these examples) with "Actual", "Budget", and "Variance". In addition, provided a list of any assumptions that were made or found. Below is an example: Budget assumptions for this exercise include both inpatient and outpatient revenue and expense. Assumptions are as follows: As to the initial budget: The budget anticipated 30,000 inpatient days this year at an average of $650 revenue per day. - Inpatient expenses were budgeted at $600 per patient day. - The budget anticipated 10,000 outpatient visits this year at an average of $400 revenue per visit. - Outpatient expenses were budgeted at $380 per visit. - As to the actual results: - Assume that only 27,000 , or 90 percent, of the inpatient days are going to actually be achieved for the year. - The average revenue of $650 per day will be achieved for these 27,000 inpatient days. - The outpatient visits will actually amount to 110 percent, or 11,000 for the year. - The average revenue of $400 per visit will be achieved for these 1,100 visits. - Further assume that, due to the heroic efforts of the Chief Financial Officer, the actual inpatient expenses will amount to $16,100,000 and the actual outpatient expenses will amount to $4,000,000. - Required 1. Set up three worksheets that follow the format of those in Example 13A. However, in each of your worksheets make two lines for Revenue; label one as Revenue-Inpatient and the other Revenue-Outpatient. Add a Revenue Subtotal line. Likewise, make two lines for Expense; label one as Expense-Inpatient and the other Expense-Outpatient. Add an Expense Subtotal line. 2. Using the new assumptions, complete the first worksheet for "As Budgeted." 3. Using the new assumptions, complete the second worksheet for "Actual." 4. Using the new assumptions, complete the third worksheet for "Static Budget Variance." SOLUTION: Your initial budget assumptions were as follows: Assume the budget anticipated 30,000 inpatient days this year at an average of $650 revenue per day, or $19,500,000. Further assume that inpatient expenses were budgeted at $600 per patient day, or $18,000,000. Also assume the budget anticipated 10,000 outpatient visits this year at an average of $400 revenue per visit, or $4,000,000. Further assume that outpatient expenses were budgeted at $380 per visit, or $3,800,000. The budget worksheet would look like this: Now assume that only 27,000 , or 90 percent, of the patient days are going to actually be achieved for the year. The average revenue of $650 per day will be achieved for these 27,000 days (thus 27,000 times 650 equals 17,550,000 ). Also assume that outpatient visits will actually amount to 110 percent, or 11,000 for the year. The average revenue of $400 per visit will be achieved for these 11,000 visits (thus 11,000 times 400 equals 4,400,000 ). Further assume that, due to the heroic efforts of the Chief Financial Officer, the actual inpatient expenses will amount to $16,100,000 and the actual outpatient expenses will amount to $4,000,000. The actual results would look like this: Since the budgeted revenues and expenses still reflect the original expectations of 30,000 inpatient days and 10,000 outpatient visits, the budget report would look like this: Note: The negative effect of the $1,550,000 net drop in revenue is offset by the greater effect of the $1,700,000 net drop in expenses, "* resulting in a positive net effect of $150,000. CHAPTER 29 Mini-Case Study 1: Proposal to Add a Retail Pharmacy to a Hospital in the Metropolis Health System Sample General Hospital belongs to the Metropolis Health System. The new chief financial officer (CFO) at Sample Hospital has been attempting to find new sources of badly needed revenue for the facility. Consequently, the CFO is preparing a proposal to add a retail pharmacy within the hospital itself. If the proposal is accepted, this would generate a new revenue stream. The CFO has prepared four exhibits, all of which appear at the end of this case study. Exhibit 29-1, a three-year retail pharmacy profitability analysis, is the primary document. It is supported by Exhibit 29-2, the retail pharmacy proposal assumptions. The profitability analysis is further supported by Exhibit 29-3, a year 1 monthly income statement detail. Finally, Exhibit 29-4 presents the supporting year 1 monthly cash flow detail and assumptions. When the controller reviewed the exhibits, she asked how the working capital of $49,789 was derived. The CFO explained that it represents 3 months of departmental expense. He also explained that the cost of drugs purchased for the first 60 days was offset by these purchases' accounts payable cycle, so the net effect was 0. In essence, the vendors were financing the drug purchases. Thus, the working capital reconciled as follows: The controller also noticed on Exhibit 29-4 that the cost of renovations to the building is estimated at $80,000 and equipment purchases are estimated at $50,000 for a total capital expenditure of $130,000. The building renovations are depreciated on a straight-line basis over a useful life of 15 years, whereas the equipment purchases are depreciated on a straight-line basis over a useful life of 5 years. The required capital is proposed to be obtained from hospital sources, and no borrowing would be necessary. In addition, the total capital expenditure is projected to be retrieved through operating cash flows before the end of year 1 . Fvhihit oo_1 Camnle Faneral Hncnital z_Vear Dotail Dharmary Exhibit 29-2 Sample General Hospital Retail Pharmacy Proposal Courtesy of Resource Group, Ltd, Dallas, Texas. Courtesy of Kesource Group, Ltd., Dallas, I exas. Cash Flow Assignment: Budgeting Select an organization; either Metropolis Health System from the Chapter 28 Case Study or one of the organizations presented in either Chapter 27 or one of the three Mini-Case Studies in Chapters 29-31. 1. Using the organization selected, create a budget for the next fiscal year. Set out the details of all assumptions you needed in order to build this budget. Use the Checklist for Building a Budget (Exhibit 15-2) and critique your own budget. In short, you will submit an excel sheet for your budget (as shown at the bottom of these examples) with "Actual", "Budget", and "Variance". In addition, provided a list of any assumptions that were made or found. Below is an example: Budget assumptions for this exercise include both inpatient and outpatient revenue and expense. Assumptions are as follows: As to the initial budget: The budget anticipated 30,000 inpatient days this year at an average of $650 revenue per day. - Inpatient expenses were budgeted at $600 per patient day. - The budget anticipated 10,000 outpatient visits this year at an average of $400 revenue per visit. - Outpatient expenses were budgeted at $380 per visit. - As to the actual results: - Assume that only 27,000 , or 90 percent, of the inpatient days are going to actually be achieved for the year. - The average revenue of $650 per day will be achieved for these 27,000 inpatient days. - The outpatient visits will actually amount to 110 percent, or 11,000 for the year. - The average revenue of $400 per visit will be achieved for these 1,100 visits. - Further assume that, due to the heroic efforts of the Chief Financial Officer, the actual inpatient expenses will amount to $16,100,000 and the actual outpatient expenses will amount to $4,000,000. - Required 1. Set up three worksheets that follow the format of those in Example 13A. However, in each of your worksheets make two lines for Revenue; label one as Revenue-Inpatient and the other Revenue-Outpatient. Add a Revenue Subtotal line. Likewise, make two lines for Expense; label one as Expense-Inpatient and the other Expense-Outpatient. Add an Expense Subtotal line. 2. Using the new assumptions, complete the first worksheet for "As Budgeted." 3. Using the new assumptions, complete the second worksheet for "Actual." 4. Using the new assumptions, complete the third worksheet for "Static Budget Variance." SOLUTION: Your initial budget assumptions were as follows: Assume the budget anticipated 30,000 inpatient days this year at an average of $650 revenue per day, or $19,500,000. Further assume that inpatient expenses were budgeted at $600 per patient day, or $18,000,000. Also assume the budget anticipated 10,000 outpatient visits this year at an average of $400 revenue per visit, or $4,000,000. Further assume that outpatient expenses were budgeted at $380 per visit, or $3,800,000. The budget worksheet would look like this: Now assume that only 27,000 , or 90 percent, of the patient days are going to actually be achieved for the year. The average revenue of $650 per day will be achieved for these 27,000 days (thus 27,000 times 650 equals 17,550,000 ). Also assume that outpatient visits will actually amount to 110 percent, or 11,000 for the year. The average revenue of $400 per visit will be achieved for these 11,000 visits (thus 11,000 times 400 equals 4,400,000 ). Further assume that, due to the heroic efforts of the Chief Financial Officer, the actual inpatient expenses will amount to $16,100,000 and the actual outpatient expenses will amount to $4,000,000. The actual results would look like this: Since the budgeted revenues and expenses still reflect the original expectations of 30,000 inpatient days and 10,000 outpatient visits, the budget report would look like this: Note: The negative effect of the $1,550,000 net drop in revenue is offset by the greater effect of the $1,700,000 net drop in expenses, "* resulting in a positive net effect of $150,000. CHAPTER 29 Mini-Case Study 1: Proposal to Add a Retail Pharmacy to a Hospital in the Metropolis Health System Sample General Hospital belongs to the Metropolis Health System. The new chief financial officer (CFO) at Sample Hospital has been attempting to find new sources of badly needed revenue for the facility. Consequently, the CFO is preparing a proposal to add a retail pharmacy within the hospital itself. If the proposal is accepted, this would generate a new revenue stream. The CFO has prepared four exhibits, all of which appear at the end of this case study. Exhibit 29-1, a three-year retail pharmacy profitability analysis, is the primary document. It is supported by Exhibit 29-2, the retail pharmacy proposal assumptions. The profitability analysis is further supported by Exhibit 29-3, a year 1 monthly income statement detail. Finally, Exhibit 29-4 presents the supporting year 1 monthly cash flow detail and assumptions. When the controller reviewed the exhibits, she asked how the working capital of $49,789 was derived. The CFO explained that it represents 3 months of departmental expense. He also explained that the cost of drugs purchased for the first 60 days was offset by these purchases' accounts payable cycle, so the net effect was 0. In essence, the vendors were financing the drug purchases. Thus, the working capital reconciled as follows: The controller also noticed on Exhibit 29-4 that the cost of renovations to the building is estimated at $80,000 and equipment purchases are estimated at $50,000 for a total capital expenditure of $130,000. The building renovations are depreciated on a straight-line basis over a useful life of 15 years, whereas the equipment purchases are depreciated on a straight-line basis over a useful life of 5 years. The required capital is proposed to be obtained from hospital sources, and no borrowing would be necessary. In addition, the total capital expenditure is projected to be retrieved through operating cash flows before the end of year 1 . Fvhihit oo_1 Camnle Faneral Hncnital z_Vear Dotail Dharmary Exhibit 29-2 Sample General Hospital Retail Pharmacy Proposal Courtesy of Resource Group, Ltd, Dallas, Texas. Courtesy of Kesource Group, Ltd., Dallas, I exas. Cash Flow