Please help, show work through calculations/ functions/ formulas in excel

2.5

PROJECTED REVENUES AND COSTS PER DEPARTMENT Revenues \begin{tabular}{|lr|} \hline Inpatient Services & 18,500,000 \\ \hline Outpatient Services & 32,600,000 \\ \hline Clinic Services & 6,250,000 \\ \hline Total revenues & 57,350,000 \\ \hline Direct Costs & \\ \hline Inpatient Services & 9,275,000 \\ \hline Outpatient Services & 18,250,000 \\ \hline Clinic Services & 4,500,000 \\ \hline Total costs & 32,025,000 \\ \hline \end{tabular} Service Department Costs \begin{tabular}{|l|r|} \hline Financial Services & 4,750,000 \\ \hline Facilities & 8,500,000 \\ \hline Housekeeping & 2,750,000 \\ \hline General Administration & 3,500,000 \\ \hline Total overhead costs & 19,500,000 \\ \hline Total Costs & 51,525,000 \\ \hline Projected Profit & 5,825,000 \\ \hline \end{tabular} Management considered various cost drivers and made the determiniation to use the following as the most relevant for each service department: a. Use Exhibit 6.6 as a guideto complete this table and calculate the allocation rates. Be sure to use Excel functions/formulas to show your work. \begin{tabular}{|l|c|c|c|c|} \hline Department & Cost Pool Amount & Cost Driver & Total Utilization & Allocation Rate \\ \hline Financial Services & & & & \\ \hline Facilities & & & & \\ \hline Housekeeping & General Administration & & & \\ \hline \end{tabular} b. Use Exhibit 6.7 as a guide to complete the indirect cost allocations to each patient service area. c. Using Exhibit 6.8 as a guide, complete the P\&L projections below