pivot tables
rayjay
Scenario21.xlsxScenario Question
| Civlian Pay Budget | Budget in $s | ||
| Primary Care Dept. | $ 1,400,000.00 | ||
| Inpatient Care Dept. | $ 1,400,000.00 | ||
| Orthopedics Dept. | $ 300,000.00 | ||
| Keller Hospital (total) ? | |||
| The Keller Army Hospital's Department level civilian pay budgets given prior to the start of the Fiscal Year (FY) by the Budget Officer are above (represented in cells B2:B4). Please provide the total in Cell B5. | |||
| What is the cumulative budget (complete cell B5)? | Your Response: | ||
| The department chief's projection (their spend plan) for the pay periods in the FY are represented in cells B2:D27 of the worksheet titled "Data Set". | |||
| Utilizing a pivot table, create a graphical representation (a combo bar graph is suggested) for the Primary Care Department Chief to display to her what her civilian pay will be relative to straight line by pay period (under or over budget). | |||
| Based on your analysis, what are your recommendation(s) to the CFO and Primary Care Department Chief? (provide in the space (cells) below) | |||
Data Set
| Pay Period | Primary Care Dept. | Inpatient Care Dept. | Orthopedics Dept. |
| Pay Period 1 | $ 55,763.83 | $ 59,391.33 | $ 11,739.64 |
| Pay Period 2 | $ 56,602.91 | $ 55,053.07 | $ 11,709.64 |
| Pay Period 3 | $ 56,715.72 | $ 58,523.28 | $ 11,768.45 |
| Pay Period 4 | $ 58,572.57 | $ 58,339.38 | $ 11,346.79 |
| Pay Period 5 | $ 59,035.16 | $ 55,259.13 | $ 11,689.39 |
| Pay Period 6 | $ 60,026.99 | $ 56,615.29 | $ 11,583.97 |
| Pay Period 7 | $ 59,807.90 | $ 62,736.80 | $ 11,768.44 |
| Pay Period 8 | $ 61,724.48 | $ 64,525.98 | $ 11,910.78 |
| Pay Period 9 | $ 61,724.46 | $ 56,678.70 | $ 11,991.47 |
| Pay Period 10 | $ 61,687.23 | $ 56,580.97 | $ 11,991.50 |
| Pay Period 11 | $ 62,603.14 | $ 59,728.27 | $ 11,991.48 |
| Pay Period 12 | $ 61,297.56 | $ 55,909.73 | $ 11,856.97 |
| Pay Period 13 | $ 60,643.85 | $ 60,981.51 | $ 12,098.78 |
| Pay Period 14 | $ 57,010.15 | $ 56,389.59 | $ 12,098.79 |
| Pay Period 15 | $ 59,395.15 | $ 53,665.61 | $ 12,071.89 |
| Pay Period 16 | $ 59,334.55 | $ 58,682.45 | $ 12,098.76 |
| Pay Period 17 | $ 58,734.25 | $ 57,478.36 | $ 11,991.17 |
| Pay Period 18 | $ 57,839.22 | $ 55,811.87 | $ 12,098.80 |
| Pay Period 19 | $ 56,348.76 | $ 61,963.19 | $ 13,563.30 |
| Pay Period 20 | $ 55,763.83 | $ 59,391.33 | $ 11,739.64 |
| Pay Period 21 | $ 56,602.91 | $ 55,053.07 | $ 11,709.64 |
| Pay Period 22 | $ 56,715.72 | $ 58,523.28 | $ 11,768.45 |
| Pay Period 23 | $ 58,572.57 | $ 58,339.38 | $ 11,346.79 |
| Pay Period 24 | $ 59,035.16 | $ 55,259.13 | $ 11,689.39 |
| Pay Period 25 | $ 60,026.99 | $ 56,615.29 | $ 11,583.97 |
| Pay Period 26 | $ 59,807.90 | $ 62,736.80 | $ 11,768.44 |
