Need help with the questions on the last picture. 1-6 please

Background. As a BPA intern, you are assigned to the Swedish Hospital's finance department. Currently the nonprofit is planning its general budget for the coming year that includes operating and flexible budgets. The capital budget was passed last year so it needs not to be revised. Swedish is noted for its three fine programs: oncology (cancer), cardiac (heart) and rhinoplasty (nose jobs). Revenues: The hospital managers have projected 300 incoming patients next year including 105 oncology, 100 cardiac, and 95 rhinoplasty patients. The average charge, or list price, for oncology patients is $80,000. Cardiac patients will be charged an average of $50,000, and rhinoplasty will charge $30,000 per patient. However, the charges often are not the actual amount ultimately received. Assume that private insurance companies will pay the full charge or list price. However, Medicare and Medicaid rates will pay as follows: oncology patients - $40,000, cardiac patients - $25,000, and rhinoplasty patients - $15,000. Self-pay patients are expected to pay the full charge, but generally 20% of self-pay charges become a bad debt. Note that bad debts are treated as expense in health care. They may not be shown as a reduction in revenue: first, the full charge for self-pay patients is shown as revenue, and then bad debts are reflected as an expense. No payment for charity care is ever received, so charity care is not shown as a revenue or expense. The payer mix is as follows (note you can round up the patients or not - it is your choice): Private insurance Medicare/Medicaid Self-Pay Charity Oncology 40% 40% 10% 10% Cardiac 20% 50% 20% 10% Rhinoplasty 10% 30% 50% 10% Gift shop is projected to earn $12,000 for the current year and is expected to remain the same. The hospital has an endowment that brings in an additional $50,000 in interest income per annum (per year). Expenditures: The hospital expects to employ workers in the following departments: Oncology Cardiology Rhinoplasty Total Expenditures: The hospital expects to employ workers in the following departments: Oncology Cardiology Rhinoplasty Total Managers 500,000 400,000 500,000 1,400,000 Staff 3,200,000 2,000,000 300,000 5,400,000 Total 3,700,000 2,400,000 800,000 6,800,000 Supplies are to be purchased throughout the year for every the departments as follows: Total Oncology 560,000 Cardiology 380,000 Rhinoplasty 320,000 Total 1,260,000 Assume that all supply use varies with the number of patients served. Swedish hospital currently pays quarterly rent on its buildings and equipment of $200,000 per year or $50,000 per quarter. Rent is expected to be unchanged next year. Flexible budget The hospital usually prepares a flexible budget as part of its annual master budget to assess the likely impact of patient volume variations on revenues and expenses. The salaries of managers are fixed costs (=they do not change with the patient volume). The staff salaries are variable costs (expenses) in all areas except in the administration department, where they are fixed. All salaries are paid in equal amounts each month. Variable salaries are paid in direct proportion to patient volume. Supplies vary in direct proportion to patient volume. The lask: 1. Calculate patient revenue on accrual basis for the coming year. Subdivide revenue by program, and within each program subdivide it by type of taxpayer. Prepare a revenue budget based on all of the sources of revenue. 2. Calculate an expense budget on accrual basis for the coming year. The expenses should show main categories such as salaries and supplies. 3. Estimate budget balance as Revenues - Expenses. 4. Present the entire budget in one professionally formatted table. Write a one-page single-spaced memo for your budget committee describing that budget. Make sure the memo is readable, clear and on point. In that memo, address budgeted revenues, expenditures and the budget balance. Dedicate one paragraph to each section. 5. In Excel or in Tableau, prepare one chart of your choice to illustrate your memo, and include it on the memo page. 6. On a separate page, prepare a flexible budget table assuming patient volumes are 5 and 10 percent higher and 5 and 10 percent lower than expected (in building scenarios, you can round up the number of patients or not - your choice). The columns in the flexible budget table should include 90%, 95%, 100%, 105%, and 110% patient enrollments. Background. As a BPA intern, you are assigned to the Swedish Hospital's finance department. Currently the nonprofit is planning its general budget for the coming year that includes operating and flexible budgets. The capital budget was passed last year so it needs not to be revised. Swedish is noted for its three fine programs: oncology (cancer), cardiac (heart) and rhinoplasty (nose jobs). Revenues: The hospital managers have projected 300 incoming patients next year including 105 oncology, 100 cardiac, and 95 rhinoplasty patients. The average charge, or list price, for oncology patients is $80,000. Cardiac patients will be charged an average of $50,000, and rhinoplasty will charge $30,000 per patient. However, the charges often are not the actual amount ultimately received. Assume that private insurance companies will pay the full charge or list price. However, Medicare and Medicaid rates will pay as follows: oncology patients - $40,000, cardiac patients - $25,000, and rhinoplasty patients - $15,000. Self-pay patients are expected to pay the full charge, but generally 20% of self-pay charges become a bad debt. Note that bad debts are treated as expense in health care. They may not be shown as a reduction in revenue: first, the full charge for self-pay patients is shown as revenue, and then bad debts are reflected as an expense. No payment for charity care is ever received, so charity care is not shown as a revenue or expense. The payer mix is as follows (note you can round up the patients or not - it is your choice): Private insurance Medicare/Medicaid Self-Pay Charity Oncology 40% 40% 10% 10% Cardiac 20% 50% 20% 10% Rhinoplasty 10% 30% 50% 10% Gift shop is projected to earn $12,000 for the current year and is expected to remain the same. The hospital has an endowment that brings in an additional $50,000 in interest income per annum (per year). Expenditures: The hospital expects to employ workers in the following departments: Oncology Cardiology Rhinoplasty Total Expenditures: The hospital expects to employ workers in the following departments: Oncology Cardiology Rhinoplasty Total Managers 500,000 400,000 500,000 1,400,000 Staff 3,200,000 2,000,000 300,000 5,400,000 Total 3,700,000 2,400,000 800,000 6,800,000 Supplies are to be purchased throughout the year for every the departments as follows: Total Oncology 560,000 Cardiology 380,000 Rhinoplasty 320,000 Total 1,260,000 Assume that all supply use varies with the number of patients served. Swedish hospital currently pays quarterly rent on its buildings and equipment of $200,000 per year or $50,000 per quarter. Rent is expected to be unchanged next year. Flexible budget The hospital usually prepares a flexible budget as part of its annual master budget to assess the likely impact of patient volume variations on revenues and expenses. The salaries of managers are fixed costs (=they do not change with the patient volume). The staff salaries are variable costs (expenses) in all areas except in the administration department, where they are fixed. All salaries are paid in equal amounts each month. Variable salaries are paid in direct proportion to patient volume. Supplies vary in direct proportion to patient volume. The lask: 1. Calculate patient revenue on accrual basis for the coming year. Subdivide revenue by program, and within each program subdivide it by type of taxpayer. Prepare a revenue budget based on all of the sources of revenue. 2. Calculate an expense budget on accrual basis for the coming year. The expenses should show main categories such as salaries and supplies. 3. Estimate budget balance as Revenues - Expenses. 4. Present the entire budget in one professionally formatted table. Write a one-page single-spaced memo for your budget committee describing that budget. Make sure the memo is readable, clear and on point. In that memo, address budgeted revenues, expenditures and the budget balance. Dedicate one paragraph to each section. 5. In Excel or in Tableau, prepare one chart of your choice to illustrate your memo, and include it on the memo page. 6. On a separate page, prepare a flexible budget table assuming patient volumes are 5 and 10 percent higher and 5 and 10 percent lower than expected (in building scenarios, you can round up the number of patients or not - your choice). The columns in the flexible budget table should include 90%, 95%, 100%, 105%, and 110% patient enrollments