Flexible budget example in healthcare

Use the Excel spreadsheet located in this Week’s Books and Resources to convert the static budget presented in Column C to a Flexible Budget. Put the Flexible budget in Column E. In Column F, explain how you converted each line item. Remember the relationship of various costs to volumes.

Length: 1 completed static Excel budget on the template provided

Using the data in Column C, Static Budget Develop the Cost/Volume relationship which will be used to prepare a Flexible Budget in Column E, based on the new volumes reported in Column E. ABC Hospital Annual Budget FY 2018 Static Budget Patient Days 20,000.00 Revenue Per Day $7,000 Net Patient Revenue Expenses Salaries Fixed Variable Semi-Fixed $140,000,000 Adjust 2% or each 5% change in volume Total Salaries Benefits $65,000,000.00 25% of Salaries Supplies Fixed Variable Semi-Fixed Total Suppplies $16,250,000.00 $2,000,000.00 $12,500,000.00 $1,500,000.00 Adjust 3% for each 5% change in volume Interest Fixed Variable Semi-Fixed $16,000,000.00 $10,000,000.00 Total Interest Expense Depreciation Fixed Variable Semi-Fixed $25,000,000.00 $15,000,000.00 $25,000,000.00 $10,000,000.00 $7,500,000.00 Adjust 10% for every 20% change in volume $7,500,000.00 Total Depreciation Expense Insurance Fixed Variable Semi-Fixed $15,000,000.00 $4,000,000.00 Adjust 10% for every 20% change in volume $2,000,000.00 Total Insurance Expense $6,000,000.00 General Fixed Variable Semi-Fixed $5,000,000.00 $2,500,000.00 $2,500,000.00 Total General Expense Total Expenses Excess of Revenue over Expenses Adjust 3% for each 5% change in volume $10,000,000.00 $138,250,000.00 $1,750,000.00 Flexible Budget 25,000.00 $7,000.00 Explanti