Answer :
Sure! Let's work through the problem step by step and complete the table using the provided data and formulas.
### Step-by-Step Solution
#### (a) and (b)
- Copy the sheet twice and rename the copies as "Data I" and "Data 2".
#### (c)
- In the "Data I" sheet, insert a new column before the "Particular" column.
#### (d)
- Insert a new row under the headings for any required adjustments.
#### (e)
- Complete the table for "Data I" by calculating the values for Payroll, Insurance, Total Cost, and the 2012 TOTAL for each category.
Below is the filled table with the given data:
[tex]\[ \begin{array}{|c|c|c|c|c|c|c|} \hline \multirow{2}{}{Particular} & \multirow{2}{}{2011 Budget Total} & \multicolumn{4}{|c|}{Year 2012} & \multirow{2}{*}{\begin{tabular}{l} 2012 \\ TOTAL \end{tabular}} \\ \cline{3-6} & & \begin{tabular}{l} Quarter \\ 1 \end{tabular} & \begin{tabular}{l} Quarter \\ 2 \end{tabular} & \begin{tabular}{l} Quarter \\ 3 \end{tabular} & \begin{tabular}{l} Quarter \\ 4 \end{tabular} & \\ \hline Guest Service & 8500 & 3572 & 3602 & 3632 & 3662 & 14468 \\ \hline Operations & 12506 & 4206 & 4503 & 4800 & 5097 & 18606 \\ \hline Marketing & 15012 & 3274 & 3452 & 3630 & 3808 & 14164 \\ \hline Management & 26245 & 5502 & 6207 & 6812 & 7817 & 26338 \\ \hline Payroll & - & 16554 & 17764 & 18874 & 20384 & 73576 \\ \hline Health & 652 & 170 & 185 & 199 & 214 & 768 \\ \hline Fire & 846 & 280 & 280 & 280 & 280 & 1120 \\ \hline Flood & 627 & 185 & 165 & 165 & 165 & 680 \\ \hline Insurance & - & 635 & 630 & 644 & 659 & 2568 \\ \hline Administration & 1602 & 458 & 46250 & 466 & 471 & 47645 \\ \hline Training & 215 & 57 & 80 & 103 & 126 & 366 \\ \hline Total Cost & - & 17704 & 64724 & 20087 & 21640 & 124155 \\ \hline \end{array} \][/tex]
### Explanation of Calculations
1. Payroll Calculation for Each Quarter (2012)
[tex]\[ \begin{aligned} \text{Q1:} & \ 3572 + 4206 + 3274 + 5502 = 16554 \\ \text{Q2:} & \ 3602 + 4503 + 3452 + 6207 = 17764 \\ \text{Q3:} & \ 3632 + 4800 + 3630 + 6812 = 18874 \\ \text{Q4:} & \ 3662 + 5097 + 3808 + 7817 = 20384 \\ \text{Total Payroll (2012)}: & \ 16554 + 17764 + 18874 + 20384 = 73576 \end{aligned} \][/tex]
2. Insurance Calculation for Each Quarter (2012)
[tex]\[ \begin{aligned} \text{Q1:} & \ 170 + 280 + 185 = 635 \\ \text{Q2:} & \ 185 + 280 + 165 = 630 \\ \text{Q3:} & \ 199 + 280 + 165 = 644 \\ \text{Q4:} & \ 214 + 280 + 165 = 659 \\ \text{Total Insurance (2012)}: & \ 635 + 630 + 644 + 659 = 2568 \end{aligned} \][/tex]
3. Total Cost Calculation for Each Quarter (2012)
[tex]\[ \begin{aligned} \text{Q1:} & \ 16554 + 635 + 458 + 57 = 17704 \\ \text{Q2:} & \ 17764 + 630 + 46250 + 80 = 64724 \\ \text{Q3:} & \ 18874 + 644 + 466 + 103 = 20087 \\ \text{Q4:} & \ 20384 + 659 + 471 + 126 = 21640 \\ \text{Total Cost (2012)}: & \ 17704 + 64724 + 20087 + 21640 = 124155 \end{aligned} \][/tex]
This completes all the required calculations and table updates for "Data I." For "Data 2," you would follow similar steps based on what the next portion of your task requires, potentially using these calculations.
### Step-by-Step Solution
#### (a) and (b)
- Copy the sheet twice and rename the copies as "Data I" and "Data 2".
#### (c)
- In the "Data I" sheet, insert a new column before the "Particular" column.
#### (d)
- Insert a new row under the headings for any required adjustments.
#### (e)
- Complete the table for "Data I" by calculating the values for Payroll, Insurance, Total Cost, and the 2012 TOTAL for each category.
Below is the filled table with the given data:
[tex]\[ \begin{array}{|c|c|c|c|c|c|c|} \hline \multirow{2}{}{Particular} & \multirow{2}{}{2011 Budget Total} & \multicolumn{4}{|c|}{Year 2012} & \multirow{2}{*}{\begin{tabular}{l} 2012 \\ TOTAL \end{tabular}} \\ \cline{3-6} & & \begin{tabular}{l} Quarter \\ 1 \end{tabular} & \begin{tabular}{l} Quarter \\ 2 \end{tabular} & \begin{tabular}{l} Quarter \\ 3 \end{tabular} & \begin{tabular}{l} Quarter \\ 4 \end{tabular} & \\ \hline Guest Service & 8500 & 3572 & 3602 & 3632 & 3662 & 14468 \\ \hline Operations & 12506 & 4206 & 4503 & 4800 & 5097 & 18606 \\ \hline Marketing & 15012 & 3274 & 3452 & 3630 & 3808 & 14164 \\ \hline Management & 26245 & 5502 & 6207 & 6812 & 7817 & 26338 \\ \hline Payroll & - & 16554 & 17764 & 18874 & 20384 & 73576 \\ \hline Health & 652 & 170 & 185 & 199 & 214 & 768 \\ \hline Fire & 846 & 280 & 280 & 280 & 280 & 1120 \\ \hline Flood & 627 & 185 & 165 & 165 & 165 & 680 \\ \hline Insurance & - & 635 & 630 & 644 & 659 & 2568 \\ \hline Administration & 1602 & 458 & 46250 & 466 & 471 & 47645 \\ \hline Training & 215 & 57 & 80 & 103 & 126 & 366 \\ \hline Total Cost & - & 17704 & 64724 & 20087 & 21640 & 124155 \\ \hline \end{array} \][/tex]
### Explanation of Calculations
1. Payroll Calculation for Each Quarter (2012)
[tex]\[ \begin{aligned} \text{Q1:} & \ 3572 + 4206 + 3274 + 5502 = 16554 \\ \text{Q2:} & \ 3602 + 4503 + 3452 + 6207 = 17764 \\ \text{Q3:} & \ 3632 + 4800 + 3630 + 6812 = 18874 \\ \text{Q4:} & \ 3662 + 5097 + 3808 + 7817 = 20384 \\ \text{Total Payroll (2012)}: & \ 16554 + 17764 + 18874 + 20384 = 73576 \end{aligned} \][/tex]
2. Insurance Calculation for Each Quarter (2012)
[tex]\[ \begin{aligned} \text{Q1:} & \ 170 + 280 + 185 = 635 \\ \text{Q2:} & \ 185 + 280 + 165 = 630 \\ \text{Q3:} & \ 199 + 280 + 165 = 644 \\ \text{Q4:} & \ 214 + 280 + 165 = 659 \\ \text{Total Insurance (2012)}: & \ 635 + 630 + 644 + 659 = 2568 \end{aligned} \][/tex]
3. Total Cost Calculation for Each Quarter (2012)
[tex]\[ \begin{aligned} \text{Q1:} & \ 16554 + 635 + 458 + 57 = 17704 \\ \text{Q2:} & \ 17764 + 630 + 46250 + 80 = 64724 \\ \text{Q3:} & \ 18874 + 644 + 466 + 103 = 20087 \\ \text{Q4:} & \ 20384 + 659 + 471 + 126 = 21640 \\ \text{Total Cost (2012)}: & \ 17704 + 64724 + 20087 + 21640 = 124155 \end{aligned} \][/tex]
This completes all the required calculations and table updates for "Data I." For "Data 2," you would follow similar steps based on what the next portion of your task requires, potentially using these calculations.
