Please provide the answers in excel with the formulas and explanation.

The administrator of Pearland Nursing Home is very aware of needing to keep his cost down since he just negotiated a new arrangement with a large insurance company that will pay him a fixed amount per patient day.  Listed below are budgeted and actual expenses for the previous month.

Actual patient days were 30,000 compared to budgeted patient days of 24,000.

Pearland Nursing Home 

Budget and Actual Expense

3.1. Determine the total variance associated with the planned and actual expenses.
3.2. Prepare a flexible budget of expense at 30,000 patient days.
3.3. Determine the “Spending Variance” which is defined as Actual costs less costs budgeted at actual volume. 

Budgeted Costs @ 24,000 Actual Costs @ 30,000 Budgeted Cost Per Unit Patient Days Patient Days Pharmacy Costs Variable $100,000 $4.167 $140,000 Misc Supplies Costs $56,000 $2.333 $67,500 Variable Fixed Overhead Costs $708,000 $29.50 $780,000 Total $864,0000 $36.00 $987,500