Problem $2(30$ Points$)$

You were given Table $1,$ below. Recreate Table $2$ in an Excel spreadsheet and complete the yellow highlighted areas by creating excel formulas to calculate the expense differences due to volume and cost variances. Is the overage due primarily to volume or cost increases?

$\backslash$table$[[,2022,2022,,],[$Expense$,\frac{YTD}{\text{A}\text{c}\text{t}\text{u}\text{a}\text{l}{?}_{(})},()$ Budget $,$$ Diff,$\%$ Diff$],[$Salaries and Wages,$$175,200,$$$169,000,$$$6,200,3\mathrm{.}7\%],[$Fringe Benefits,$69,200,68,000,1,200,1\mathrm{.}8\%],[$Supplies$,126,000,124,000,2,000,1\mathrm{.}6\%],[$Depreciation$,25,000,24,000,1,000,4\mathrm{.}2\%],[$Interest$,8,400,9,000,-600,-6\mathrm{.}7\%],[$Other$,109,300,110,000,-700,-0\mathrm{.}6\%],[$Total Expenses,$$513,100,$$$504,000,$$$9,100,1\mathrm{.}8\%],[,,,,],[$Patient days,$47,700,47,200,500,1\mathrm{.}1\%]]$

Table $1$

$\backslash$table$[[,2022,2022,,,$Volume,Cost,$],[$Expense$,\backslash$table$[[$YTD$],[$Actual$]],\backslash$table$[[$YTD$],[$Budget$]],$Diff,$\%$Diff,Driven,Driven,Check$],[$Salaries and Wages,$$175,200,$$$169,000,$$$6,200,3\mathrm{.}7\%,,,],[$Fringe Benefits,$69,200,68,000,$$$1,200,1\mathrm{.}8\%,,,],[$Supplies$,126,000,124,000,$$$2,000,1\mathrm{.}6\%,,,],[$Depreciation$,25,000,24,000,$$$1,000,4\mathrm{.}2\%,,,],[$Interest$,8,400,9,000,($$$600),-6\mathrm{.}7\%,,,],[$Other$,109,300,110,000,($$$700),-0\mathrm{.}6\%,,,],[$Total Expenses,$$513,100,$$$504,000,$$$9,100,1\mathrm{.}8\%,,,]]$

Table $2$

Problem $2(30$ Points$)$

You were given Table $1,$ below. Recreate Table $2$ in an Excel spreadsheet and complete the yellow highlighted areas by creating excel formulas to calculate the expense differences due to volume and cost variances. Is the overage due primarily to volume or cost increases?

$\backslash$table$[[,2022,2022,,],[$Expense$,\frac{YTD}{\text{A}\text{c}\text{t}\text{u}\text{a}\text{l}{?}_{(})},()$ Budget $,$$ Diff,$\%$ Diff$],[$Salaries and Wages,$$175,200,$$$169,000,$$$6,200,3\mathrm{.}7\%],[$Fringe Benefits,$69,200,68,000,1,200,1\mathrm{.}8\%],[$Supplies$,126,000,124,000,2,000,1\mathrm{.}6\%],[$Depreciation$,25,000,24,000,1,000,4\mathrm{.}2\%],[$Interest$,8,400,9,000,-600,-6\mathrm{.}7\%],[$Other$,109,300,110,000,-700,-0\mathrm{.}6\%],[$Total Expenses,$$513,100,$$$504,000,$$$9,100,1\mathrm{.}8\%],[,,,,],[$Patient days,$47,700,47,200,500,1\mathrm{.}1\%]]$

Table $1$

$\backslash$table$[[,2022,2022,,,$Volume,Cost,$],[$Expense$,\backslash$table$[[$YTD$],[$Actual$]],\backslash$table$[[$YTD$],[$Budget$]],$Diff,$\%$Diff,Driven,Driven,Check$],[$Salaries and Wages,$$175,200,$$$169,000,$$$6,200,3\mathrm{.}7\%,,,],[$Fringe Benefits,$69,200,68,000,$$$1,200,1\mathrm{.}8\%,,,],[$Supplies$,126,000,124,000,$$$2,000,1\mathrm{.}6\%,,,],[$Depreciation$,25,000,24,000,$$$1,000,4\mathrm{.}2\%,,,],[$Interest$,8,400,9,000,($$$600),-6\mathrm{.}7\%,,,],[$Other$,109,300,110,000,($$$700),-0\mathrm{.}6\%,,,],[$Total Expenses,$$513,100,$$$504,000,$$$9,100,1\mathrm{.}8\%,,,]]$

Table $2$