Correct Excel Project
prudence98
KaoBadiPExcelProject2.xlsxAnalysis by
| Name: | Badi Kao | |
| Class/Section: | IFSM 201 Concepts and Applications of Information Technology | |
| Project: | Excel Project 2 | |
| Date Due: | 6/23/19 | |
Data
| Year | Quarter | Location | CarClass | Revenue | NumCars |
| 2015 | Q3 | Downtown | Economy | $805,325 | 5,577 |
| 2015 | Q3 | Airport | Economy | $734,028 | 4,922 |
| 2015 | Q4 | Airport | Economy | $640,474 | 4,278 |
| 2015 | Q4 | Downtown | Economy | $532,065 | 3,610 |
| 2015 | Q2 | Airport | Economy | $502,546 | 3,489 |
| 2015 | Q1 | Airport | Economy | $470,125 | 3,408 |
| 2015 | Q2 | Downtown | Economy | $396,848 | 2,881 |
| 2015 | Q1 | Downtown | Economy | $375,118 | 2,849 |
| 2015 | Q4 | Airport | Premium | $663,901 | 4,055 |
| 2015 | Q4 | Downtown | Premium | $624,053 | 4,016 |
| 2015 | Q3 | Downtown | Premium | $540,217 | 3,533 |
| 2015 | Q2 | Downtown | Premium | $487,629 | 3,241 |
| 2015 | Q3 | Airport | Premium | $505,403 | 3,148 |
| 2015 | Q1 | Downtown | Premium | $370,679 | 2,514 |
| 2015 | Q1 | Airport | Premium | $375,811 | 2,466 |
| 2015 | Q2 | Airport | Premium | $317,110 | 2,056 |
| 2015 | Q4 | Airport | SUV | $663,548 | 4,299 |
| 2015 | Q4 | Downtown | SUV | $623,328 | 4,264 |
| 2015 | Q3 | Downtown | SUV | $540,623 | 3,758 |
| 2015 | Q2 | Downtown | SUV | $487,843 | 3,427 |
| 2015 | Q3 | Airport | SUV | $504,888 | 3,349 |
| 2015 | Q1 | Downtown | SUV | $371,061 | 2,646 |
| 2015 | Q1 | Airport | SUV | $376,497 | 2,621 |
| 2015 | Q2 | Airport | SUV | $317,066 | 2,151 |
| 2015 | Q4 | Airport | Hybrid | $663,467 | 4,139 |
| 2015 | Q4 | Downtown | Hybrid | $623,719 | 4,109 |
| 2015 | Q3 | Downtown | Hybrid | $540,387 | 3,597 |
| 2015 | Q2 | Downtown | Hybrid | $488,036 | 3,281 |
| 2015 | Q3 | Airport | Hybrid | $505,782 | 3,236 |
| 2015 | Q1 | Downtown | Hybrid | $370,963 | 2,554 |
| 2015 | Q1 | Airport | Hybrid | $376,241 | 2,533 |
| 2015 | Q2 | Airport | Hybrid | $317,492 | 2,106 |
| 2016 | Q4 | Airport | Economy | $959,953 | 5,413 |
| 2016 | Q1 | Downtown | Economy | $751,466 | 5,080 |
| 2016 | Q4 | Downtown | Economy | $736,727 | 4,796 |
| 2016 | Q3 | Downtown | Economy | $712,259 | 4,715 |
| 2016 | Q2 | Downtown | Economy | $670,449 | 4,472 |
| 2016 | Q3 | Airport | Economy | $621,108 | 3,710 |
| 2016 | Q1 | Airport | Economy | $521,919 | 3,445 |
| 2016 | Q2 | Airport | Economy | $385,122 | 2,280 |
| 2016 | Q3 | Airport | Premium | $688,440 | 4,045 |
| 2016 | Q2 | Airport | Premium | $627,078 | 3,711 |
| 2016 | Q1 | Airport | Premium | $591,816 | 3,570 |
| 2016 | Q2 | Downtown | Premium | $523,205 | 3,247 |
| 2016 | Q4 | Airport | Premium | $565,096 | 3,228 |
| 2016 | Q3 | Downtown | Premium | $517,183 | 3,150 |
| 2016 | Q1 | Downtown | Premium | $444,247 | 2,798 |
| 2016 | Q4 | Downtown | Premium | $451,490 | 2,698 |
| 2016 | Q3 | Airport | SUV | $688,301 | 4,263 |
| 2016 | Q2 | Airport | SUV | $626,757 | 3,943 |
| 2016 | Q1 | Airport | SUV | $591,590 | 3,779 |
| 2016 | Q2 | Downtown | SUV | $523,359 | 3,400 |
| 2016 | Q4 | Airport | SUV | $565,293 | 3,381 |
| 2016 | Q3 | Downtown | SUV | $518,004 | 3,345 |
| 2016 | Q1 | Downtown | SUV | $444,127 | 2,939 |
| 2016 | Q4 | Downtown | SUV | $450,725 | 2,846 |
| 2016 | Q3 | Airport | Hybrid | $688,782 | 4,089 |
| 2016 | Q2 | Airport | Hybrid | $626,363 | 3,807 |
| 2016 | Q1 | Airport | Hybrid | $591,561 | 3,669 |
| 2016 | Q2 | Downtown | Hybrid | $523,471 | 3,320 |
| 2016 | Q4 | Airport | Hybrid | $565,829 | 3,261 |
| 2016 | Q3 | Downtown | Hybrid | $517,267 | 3,210 |
| 2016 | Q1 | Downtown | Hybrid | $444,554 | 2,867 |
| 2016 | Q4 | Downtown | Hybrid | $450,842 | 2,757 |
Initial Analysis
| Year | Quarter | Location | CarClass | Revenue | NumCars | |||
| 2015 | Q3 | Downtown | Economy | $805,325 | 5,577 | |||
| 2015 | Q3 | Airport | Economy | $734,028 | 4,922 | Row Labels | Sum of Revenue | |
| 2015 | Q4 | Airport | Economy | $640,474 | 4,278 | Airport | $17,839,387 | |
| 2015 | Q4 | Downtown | Economy | $532,065 | 3,610 | Economy | $4,835,275 | |
| 2015 | Q2 | Airport | Economy | $502,546 | 3,489 | Hybrid | $4,335,517 | |
| 2015 | Q1 | Airport | Economy | $470,125 | 3,408 | Premium | $4,334,655 | |
| 2015 | Q2 | Downtown | Economy | $396,848 | 2,881 | SUV | $4,333,940 | |
| 2015 | Q1 | Downtown | Economy | $375,118 | 2,849 | Downtown | $16,857,269 | |
| 2015 | Q4 | Airport | Premium | $663,901 | 4,055 | Economy | $4,980,257 | |
| 2015 | Q4 | Downtown | Premium | $624,053 | 4,016 | Hybrid | $3,959,239 | |
| 2015 | Q3 | Downtown | Premium | $540,217 | 3,533 | Premium | $3,958,703 | |
| 2015 | Q2 | Downtown | Premium | $487,629 | 3,241 | SUV | $3,959,070 | |
| 2015 | Q3 | Airport | Premium | $505,403 | 3,148 | Grand Total | $34,696,656 | |
| 2015 | Q1 | Downtown | Premium | $370,679 | 2,514 | |||
| 2015 | Q1 | Airport | Premium | $375,811 | 2,466 | |||
| 2015 | Q2 | Airport | Premium | $317,110 | 2,056 | |||
| 2015 | Q4 | Airport | SUV | $663,548 | 4,299 | |||
| 2015 | Q4 | Downtown | SUV | $623,328 | 4,264 | |||
| 2015 | Q3 | Downtown | SUV | $540,623 | 3,758 | |||
| 2015 | Q2 | Downtown | SUV | $487,843 | 3,427 | |||
| 2015 | Q3 | Airport | SUV | $504,888 | 3,349 | |||
| 2015 | Q1 | Downtown | SUV | $371,061 | 2,646 | |||
| 2015 | Q1 | Airport | SUV | $376,497 | 2,621 | |||
| 2015 | Q2 | Airport | SUV | $317,066 | 2,151 | |||
| 2015 | Q4 | Airport | Hybrid | $663,467 | 4,139 | |||
| 2015 | Q4 | Downtown | Hybrid | $623,719 | 4,109 | |||
| 2015 | Q3 | Downtown | Hybrid | $540,387 | 3,597 | |||
| 2015 | Q2 | Downtown | Hybrid | $488,036 | 3,281 | |||
| 2015 | Q3 | Airport | Hybrid | $505,782 | 3,236 | |||
| 2015 | Q1 | Downtown | Hybrid | $370,963 | 2,554 | |||
| 2015 | Q1 | Airport | Hybrid | $376,241 | 2,533 | |||
| 2015 | Q2 | Airport | Hybrid | $317,492 | 2,106 | |||
| 2016 | Q4 | Airport | Economy | $959,953 | 5,413 | |||
| 2016 | Q1 | Downtown | Economy | $751,466 | 5,080 | |||
| 2016 | Q4 | Downtown | Economy | $736,727 | 4,796 | Question 1 | ||
| 2016 | Q3 | Downtown | Economy | $712,259 | 4,715 | |||
| 2016 | Q2 | Downtown | Economy | $670,449 | 4,472 | The Airport location provided the most revenues and the amount generated was $17,839,387 | ||
| 2016 | Q3 | Airport | Economy | $621,108 | 3,710 | |||
| 2016 | Q1 | Airport | Economy | $521,919 | 3,445 | |||
| 2016 | Q2 | Airport | Economy | $385,122 | 2,280 | |||
| 2016 | Q3 | Airport | Premium | $688,440 | 4,045 | |||
| 2016 | Q2 | Airport | Premium | $627,078 | 3,711 | |||
| 2016 | Q1 | Airport | Premium | $591,816 | 3,570 | Question 2 | ||
| 2016 | Q2 | Downtown | Premium | $523,205 | 3,247 | |||
| 2016 | Q4 | Airport | Premium | $565,096 | 3,228 | The Economy class at the Downtown location provided the highest revenue for the two years combined. The revenue was $4,980,257 | ||
| 2016 | Q3 | Downtown | Premium | $517,183 | 3,150 | |||
| 2016 | Q1 | Downtown | Premium | $444,247 | 2,798 | |||
| 2016 | Q4 | Downtown | Premium | $451,490 | 2,698 | |||
| 2016 | Q3 | Airport | SUV | $688,301 | 4,263 | |||
| 2016 | Q2 | Airport | SUV | $626,757 | 3,943 | |||
| 2016 | Q1 | Airport | SUV | $591,590 | 3,779 | |||
| 2016 | Q2 | Downtown | SUV | $523,359 | 3,400 | |||
| 2016 | Q4 | Airport | SUV | $565,293 | 3,381 | |||
| 2016 | Q3 | Downtown | SUV | $518,004 | 3,345 | |||
| 2016 | Q1 | Downtown | SUV | $444,127 | 2,939 | |||
| 2016 | Q4 | Downtown | SUV | $450,725 | 2,846 | |||
| 2016 | Q3 | Airport | Hybrid | $688,782 | 4,089 | |||
| 2016 | Q2 | Airport | Hybrid | $626,363 | 3,807 | |||
| 2016 | Q1 | Airport | Hybrid | $591,561 | 3,669 | |||
| 2016 | Q2 | Downtown | Hybrid | $523,471 | 3,320 | |||
| 2016 | Q4 | Airport | Hybrid | $565,829 | 3,261 | |||
| 2016 | Q3 | Downtown | Hybrid | $517,267 | 3,210 | |||
| 2016 | Q1 | Downtown | Hybrid | $444,554 | 2,867 | |||
| 2016 | Q4 | Downtown | Hybrid | $450,842 | 2,757 |
Total revenue for CarClass by location
Total Economy Hybrid Premium SUV Economy Hybrid Premium SUV Airport Downtown 4835275 4335517 4334655 4333940 4980257 3959239 3958703 3959070
Profit Analysis
| Year | Quarter | Location | CarClass | Revenue | NumCars | Expenses | Profit | ProfitperCar | Hybrid Expense per Car | 100 | ||||
| 2015 | Q3 | Downtown | Economy | $805,325 | 5,577 | $622,059 | $183,266 | $33 | Non-Hybrid Expense per Car | 50 | ||||
| 2015 | Q3 | Airport | Economy | $734,028 | 4,922 | $484,522 | $249,506 | $51 | ||||||
| 2015 | Q4 | Airport | Economy | $640,474 | 4,278 | $366,026 | $274,448 | $64 | ||||||
| 2015 | Q4 | Downtown | Economy | $532,065 | 3,610 | $260,642 | $271,423 | $75 | Sum of Profit | Column Labels | ||||
| 2015 | Q2 | Airport | Economy | $502,546 | 3,489 | $243,462 | $259,084 | $74 | Row Labels | Airport | Downtown | Grand Total | ||
| 2015 | Q1 | Airport | Economy | $470,125 | 3,408 | $232,289 | $237,836 | $70 | Economy | $2,306,354 | $1,948,457 | $4,254,811 | ||
| 2015 | Q2 | Downtown | Economy | $396,848 | 2,881 | $166,003 | $230,845 | $80 | Hybrid | $2,460,253 | $2,274,091 | $4,734,344 | ||
| 2015 | Q1 | Downtown | Economy | $375,118 | 2,849 | $162,336 | $212,782 | $75 | Premium | $2,535,462 | $2,338,540 | $4,874,002 | ||
| 2015 | Q4 | Airport | Premium | $663,901 | 4,055 | $328,861 | $335,041 | $83 | SUV | $2,321,419 | $2,148,343 | $4,469,761 | ||
| 2015 | Q4 | Downtown | Premium | $624,053 | 4,016 | $322,565 | $301,488 | $75 | Grand Total | $9,623,488 | $8,709,430 | $18,332,918 | ||
| 2015 | Q3 | Downtown | Premium | $540,217 | 3,533 | $249,642 | $290,575 | $82 | ||||||
| 2015 | Q2 | Downtown | Premium | $487,629 | 3,241 | $210,082 | $277,547 | $86 | ||||||
| 2015 | Q3 | Airport | Premium | $505,403 | 3,148 | $198,198 | $307,205 | $98 | ||||||
| 2015 | Q1 | Downtown | Premium | $370,679 | 2,514 | $126,404 | $244,275 | $97 | ||||||
| 2015 | Q1 | Airport | Premium | $375,811 | 2,466 | $121,623 | $254,188 | $103 | Average of Profit | Column Labels | ||||
| 2015 | Q2 | Airport | Premium | $317,110 | 2,056 | $84,543 | $232,567 | $113 | Row Labels | Airport | Downtown | Grand Total | ||
| 2015 | Q4 | Airport | SUV | $663,548 | 4,299 | $369,628 | $293,920 | $68 | Economy | $288,294 | $243,557 | $265,926 | ||
| 2015 | Q4 | Downtown | SUV | $623,328 | 4,264 | $363,634 | $259,694 | $61 | Q1 | $261,197 | $224,060 | $242,629 | ||
| 2015 | Q3 | Downtown | SUV | $540,623 | 3,758 | $282,451 | $258,172 | $69 | Q2 | $270,119 | $250,659 | $260,389 | ||
| 2015 | Q2 | Downtown | SUV | $487,843 | 3,427 | $234,887 | $252,956 | $74 | Q3 | $297,666 | $225,450 | $261,558 | ||
| 2015 | Q3 | Airport | SUV | $504,888 | 3,349 | $224,316 | $280,572 | $84 | Q4 | $324,195 | $274,059 | $299,127 | ||
| 2015 | Q1 | Downtown | SUV | $371,061 | 2,646 | $140,026 | $231,035 | $87 | Hybrid | $307,532 | $284,261 | $295,897 | ||
| 2015 | Q1 | Airport | SUV | $376,497 | 2,621 | $137,393 | $239,104 | $91 | Q1 | $285,125 | $260,332 | $272,728 | ||
| 2015 | Q2 | Airport | SUV | $317,066 | 2,151 | $92,536 | $224,530 | $104 | Q2 | $282,643 | $287,880 | $285,261 | ||
| 2015 | Q4 | Airport | Hybrid | $663,467 | 4,139 | $342,626 | $320,841 | $78 | Q3 | $325,366 | $296,402 | $310,884 | ||
| 2015 | Q4 | Downtown | Hybrid | $623,719 | 4,109 | $337,678 | $286,041 | $70 | Q4 | $336,994 | $292,431 | $314,712 | ||
| 2015 | Q3 | Downtown | Hybrid | $540,387 | 3,597 | $258,768 | $281,619 | $78 | Premium | $316,933 | $292,318 | $304,625 | ||
| 2015 | Q2 | Downtown | Hybrid | $488,036 | 3,281 | $215,299 | $272,737 | $83 | Q1 | $295,553 | $265,973 | $280,763 | ||
| 2015 | Q3 | Airport | Hybrid | $505,782 | 3,236 | $209,434 | $296,348 | $92 | Q2 | $292,107 | $294,946 | $293,527 | ||
| 2015 | Q1 | Downtown | Hybrid | $370,963 | 2,554 | $130,458 | $240,505 | $94 | Q3 | $334,202 | $304,654 | $319,428 | ||
| 2015 | Q1 | Airport | Hybrid | $376,241 | 2,533 | $128,322 | $247,919 | $98 | Q4 | $345,868 | $303,697 | $324,783 | ||
| 2015 | Q2 | Airport | Hybrid | $317,492 | 2,106 | $88,705 | $228,787 | $109 | SUV | $290,177 | $268,543 | $279,360 | ||
| 2016 | Q4 | Airport | Economy | $959,953 | 5,413 | $586,011 | $373,942 | $69 | Q1 | $272,539 | $251,204 | $261,871 | ||
| 2016 | Q1 | Downtown | Economy | $751,466 | 5,080 | $516,128 | $235,338 | $46 | Q2 | $270,171 | $272,558 | $271,364 | ||
| 2016 | Q4 | Downtown | Economy | $736,727 | 4,796 | $460,032 | $276,695 | $58 | Q3 | $302,705 | $276,198 | $289,451 | ||
| 2016 | Q3 | Downtown | Economy | $712,259 | 4,715 | $444,625 | $267,635 | $57 | Q4 | $315,295 | $274,212 | $294,754 | ||
| 2016 | Q2 | Downtown | Economy | $670,449 | 4,472 | $399,976 | $270,473 | $60 | Grand Total | $300,734 | $272,170 | $286,452 | ||
| 2016 | Q3 | Airport | Economy | $621,108 | 3,710 | $275,282 | $345,826 | $93 | ||||||
| 2016 | Q1 | Airport | Economy | $521,919 | 3,445 | $237,361 | $284,559 | $83 | Question 1. | |||||
| 2016 | Q2 | Airport | Economy | $385,122 | 2,280 | $103,968 | $281,154 | $123 | The Airport location produces the highest profit and the figure is $9,623,488 and the Economy class produced the least profit and the figure is $4,254,811 | |||||
| 2016 | Q3 | Airport | Premium | $688,440 | 4,045 | $327,241 | $361,200 | $89 | ||||||
| 2016 | Q2 | Airport | Premium | $627,078 | 3,711 | $275,430 | $351,648 | $95 | ||||||
| 2016 | Q1 | Airport | Premium | $591,816 | 3,570 | $254,898 | $336,918 | $94 | ||||||
| 2016 | Q2 | Downtown | Premium | $523,205 | 3,247 | $210,860 | $312,345 | $96 | Question 2 | |||||
| 2016 | Q4 | Airport | Premium | $565,096 | 3,228 | $208,400 | $356,696 | $111 | The Airport and Premium carclass produced the higherst quarterly profit with average profit of $316,933 while the Downtown and Economy carclass is the least profitable with average profit of $243,557 | |||||
| 2016 | Q3 | Downtown | Premium | $517,183 | 3,150 | $198,450 | $318,733 | $101 | ||||||
| 2016 | Q1 | Downtown | Premium | $444,247 | 2,798 | $156,576 | $287,671 | $103 | ||||||
| 2016 | Q4 | Downtown | Premium | $451,490 | 2,698 | $145,584 | $305,906 | $113 | ||||||
| 2016 | Q3 | Airport | SUV | $688,301 | 4,263 | $363,463 | $324,838 | $76 | ||||||
| 2016 | Q2 | Airport | SUV | $626,757 | 3,943 | $310,945 | $315,812 | $80 | ||||||
| 2016 | Q1 | Airport | SUV | $591,590 | 3,779 | $285,617 | $305,973 | $81 | ||||||
| 2016 | Q2 | Downtown | SUV | $523,359 | 3,400 | $231,200 | $292,159 | $86 | ||||||
| 2016 | Q4 | Airport | SUV | $565,293 | 3,381 | $228,623 | $336,670 | $100 | ||||||
| 2016 | Q3 | Downtown | SUV | $518,004 | 3,345 | $223,781 | $294,224 | $88 | ||||||
| 2016 | Q1 | Downtown | SUV | $444,127 | 2,939 | $172,754 | $271,373 | $92 | ||||||
| 2016 | Q4 | Downtown | SUV | $450,725 | 2,846 | $161,994 | $288,731 | $101 | ||||||
| 2016 | Q3 | Airport | Hybrid | $688,782 | 4,089 | $334,398 | $354,384 | $87 | ||||||
| 2016 | Q2 | Airport | Hybrid | $626,363 | 3,807 | $289,865 | $336,498 | $88 | ||||||
| 2016 | Q1 | Airport | Hybrid | $591,561 | 3,669 | $269,231 | $322,330 | $88 | ||||||
| 2016 | Q2 | Downtown | Hybrid | $523,471 | 3,320 | $220,448 | $303,023 | $91 | ||||||
| 2016 | Q4 | Airport | Hybrid | $565,829 | 3,261 | $212,682 | $353,147 | $108 | ||||||
| 2016 | Q3 | Downtown | Hybrid | $517,267 | 3,210 | $206,082 | $311,185 | $97 | ||||||
| 2016 | Q1 | Downtown | Hybrid | $444,554 | 2,867 | $164,394 | $280,160 | $98 | ||||||
| 2016 | Q4 | Downtown | Hybrid | $450,842 | 2,757 | $152,021 | $298,821 | $108 |
