excel question 4,2
Bayhawks22
Week4ExcelAssignment2.xlsxInstructions
| On Quarterly Budget Tab |
| Insert Column after "Department" and name "Department #" |
| Insert Column after "Total Budget" and name "Total Budget %" |
| Calculate Total Budget and Total Budget % |
| Insert Row at the bottom of the table and name it "Total" |
| Calculate Totals for Q1 Budget, Q2 Budget, Q3 Budget, Q4 Budget and Total Budget |
| Find "Department #" using Department # tab |
| Sort data by Region and descending by Total Budget ($) |
| Questions to Answer- use formulas |
| Total Budget for South |
| Total Budget for Accounting |
| Total Budget for North and Legal |
| Number of time Total Budget is over $1,500,000 |
| Pivot Tables to Create- make sure to format #'s as currency |
| On a separate tab named Pivot Tables create: |
| Pivot Table to show Total Budget ($) by Department, sort in descending order |
| Pivot Table to show Total Budget ($) for each Region by Department |
| Pivot Table to show Quarterly Budget (in $) for each Manager |
| Pivot Table to show Quarterly Budget for each Manager as $ and as % of total |
| Pivot Table to show Total Budget (in $) by Department with Region filter (use filter to show Pacific Northwest and Canada) |
| Charts to Create |
| On a separate tab named Charts create: |
| Create Pie Chart to show allocation of Total Budget (%) by Region |
| Create Bar Chart to show Total Budget ($) by Department |
| Create Stacked Bar Chart to show Total Budget by Region by Department |
Quarterly Budget
| Region | Department | Manager | Q1 Budget | Q2 Budget | Q3 Budget | Q4 Budget | Total Budget |
| Mid-Atlantic | Legal | Jose Palmer | $ 925,264 | $ 1,321,805 | $ 1,101,505 | $ 1,057,444 | |
| Canada | Legal | Audrey Washington | $ 538,475 | $ 769,250 | $ 641,041 | $ 615,400 | |
| North | Logistics | Samantha Allen | $ 303,142 | $ 433,061 | $ 360,884 | $ 346,448 | |
| Mid-Atlantic | Customer Service | Katherine Nichols | $ 595,143 | $ 850,204 | $ 708,504 | $ 680,163 | |
| Pacific Northwest | Quality Asurance | David Gordon | $ 235,115 | $ 335,879 | $ 279,899 | $ 268,703 | |
| Mid-Atlantic | Operational | Lillian Hart | $ 619,374 | $ 884,820 | $ 737,350 | $ 707,856 | |
| Mid-Atlantic | Sales | Claire Peterson | $ 497,962 | $ 711,374 | $ 592,812 | $ 569,099 | |
| East | Inventory | Hannah Porter | $ 639,135 | $ 913,050 | $ 760,875 | $ 730,440 | |
| Mid-Atlantic | Services | Victoria Gomez | $ 340,560 | $ 486,515 | $ 405,429 | $ 389,212 | |
| South | Accounting | Brooke Bailey | $ 146,516 | $ 209,309 | $ 174,424 | $ 167,447 | |
| Mid-Atlantic | Insurance | Layla Green | $ 306,372 | $ 437,674 | $ 364,729 | $ 350,139 | |
| North | Sales | Alex Cox | $ 613,647 | $ 876,638 | $ 730,532 | $ 701,311 | |
| North | Business Development | Genesis Mills | $ 776,949 | $ 1,109,927 | $ 924,939 | $ 887,941 | |
| South | Business Development | Isaac Chavez | $ 195,328 | $ 279,040 | $ 232,533 | $ 223,232 | |
| Pacific Northwest | Services | Adam Howard | $ 547,956 | $ 782,794 | $ 652,328 | $ 626,235 | |
| South | Quality Asurance | Evelyn Burns | $ 348,796 | $ 498,280 | $ 415,233 | $ 398,624 | |
| Mid-Atlantic | Business Development | Jack Black | $ 135,276 | $ 193,252 | $ 161,043 | $ 154,602 | |
| Pacific Northwest | Customer Service | Wyatt Harris | $ 942,333 | $ 1,346,189 | $ 1,121,825 | $ 1,076,952 | |
| Mid-Atlantic | Marketing | Maria Cook | $ 292,111 | $ 417,302 | $ 347,751 | $ 333,841 | |
| North | IT | Gianna Freeman | $ 768,774 | $ 1,098,249 | $ 915,207 | $ 878,599 | |
| East | Market Development | Thomas Rivera | $ 520,399 | $ 743,428 | $ 619,523 | $ 594,742 | |
| Canada | Licenses | Peyton Powell | $ 1,023,503 | $ 1,462,147 | $ 1,218,456 | $ 1,169,717 | |
| South | Licenses | Eli Marshall | $ 188,282 | $ 268,975 | $ 224,146 | $ 215,180 | |
| Canada | Management | Joseph Jordan | $ 434,682 | $ 620,974 | $ 517,479 | $ 496,779 | |
| Mid-Atlantic | Management | Savannah Stone | $ 767,288 | $ 1,096,126 | $ 913,438 | $ 876,901 | |
| East | Research & Development | Adam Cox | $ 882,337 | $ 1,260,481 | $ 1,050,401 | $ 1,008,385 | |
| Southwest | Marketing | Kaylee Hart | $ 371,121 | $ 530,174 | $ 441,811 | $ 424,139 | |
| Southwest | Supply Chain | Mackenzie Griffin | $ 145,823 | $ 208,318 | $ 173,598 | $ 166,654 | |
| East | IT | Nathaniel Kennedy | $ 159,402 | $ 227,718 | $ 189,765 | $ 182,174 | |
| Pacific Northwest | IT | Riley James | $ 655,771 | $ 936,816 | $ 780,680 | $ 749,453 | |
| East | Legal | Jordan Morgan | $ 686,540 | $ 980,771 | $ 817,310 | $ 784,617 | |
| South | Finance | Faith Green | $ 249,804 | $ 356,863 | $ 297,386 | $ 285,490 | |
| Southwest | Services | Jasmine Cox | $ 422,561 | $ 603,659 | $ 503,049 | $ 482,927 | |
| Southwest | Operational | Gabriella West | $ 413,998 | $ 591,425 | $ 492,855 | $ 473,140 | |
| Canada | Purchasing | Landon Garcia | $ 459,752 | $ 656,789 | $ 547,324 | $ 525,431 | |
| Pacific Northwest | Inventory | Chloe Sanchez | $ 1,012,767 | $ 1,446,810 | $ 1,205,675 | $ 1,157,448 | |
| East | Sales | Anna Greene | $ 492,830 | $ 704,043 | $ 586,703 | $ 563,234 | |
| Canada | Business Development | Aaron Myers | $ 723,198 | $ 1,033,140 | $ 860,950 | $ 826,512 | |
| Pacific Northwest | Marketing | Kyle Palmer | $ 365,075 | $ 521,536 | $ 434,614 | $ 417,229 | |
| South | Legal | Brooklyn Coleman | $ 544,427 | $ 777,752 | $ 648,127 | $ 622,202 | |
| Canada | Operational | Samuel Gardner | $ 542,063 | $ 774,375 | $ 645,313 | $ 619,500 | |
| North | Research & Development | Katelyn Marshall | $ 803,339 | $ 1,147,627 | $ 956,356 | $ 918,102 | |
| East | Customer Service | Cole Lee | $ 436,229 | $ 623,184 | $ 519,320 | $ 498,547 | |
Department #
| Department | Department # |
| Accounting | 100100 |
| Business Development | 100200 |
| Customer Service | 100300 |
| Finance | 100400 |
| Insurance | 100500 |
| Inventory | 100600 |
| IT | 100700 |
| Legal | 100800 |
| Licenses | 100900 |
| Logistics | 101000 |
| Management | 101100 |
| Market Development | 101200 |
| Marketing | 101300 |
| Operational | 101400 |
| Purchasing | 101500 |
| Quality Asurance | 101600 |
| Research & Development | 101700 |
| Sales | 101800 |
| Services | 101900 |
| Supply Chain | 102000 |
