could you give me answer plea

Data Analysis is often used in Human Resource Management (HR). HR often has to deal with large databases with information such as compensation, organizational unit, skills, age, etc. These databases are often analyzed based on parameters other than financial data but analyses often incorporate financial data. In the below example, financial performance data of salespeople are analyzed to support managerial decisions with respect to the sales organization. Viewing Multiple Sheets You are going to want to complete this Sheet with the 'DATA Budgets' Sheet arranged to the right. To accomplish this, see instructions on Row 31 of the 'EXCEL Tables' Sheet. The Budget Case Below is an example of how 'sales budgets' (budgets of Revenues generated by salespeople) might be tracked. Salespeople are generally compensated by commission, which is directly linked to a salesperson's actual financial results relative to budget. Therefore, sales budgeting and tracking is a very important process within a company. The example company has 24 salespeople assigned to 6 sales offices organized into 3 regions. In Row 9 of the 'DATA Budgets' Sheet are the year-to-date sales data for this company, including: Budget The amount that the salesperson was budgeted to sell by this date Actual The amount that the salesperson actually sold by this date Variance The Variance or the Actual sales minus the Budget sales Pct Variance The Variance divided by the Budget sales In this case we are not going to use the Table capability that was presented in the 'EXCEL Tables' Sheet. Instead we are going to use the 'Subtotal' capability in the "Insert" tab to create multiple levels of subtotals in the table we will create. The 'Subtotal' capability does not work with Excel Tables. Sorting the Data To be able to use the Subtotal capability the data must be sorted in the sequence of the fields used to define the subtotal The table on 'DATA Budgets' is currently sorted by 'Salesperson' To be able to create subtotals at both the 'Region' and 'Office' levels we need to sort the data by region/office. 1 Click anywhere within the data range of the data to be sorted on the 'DATA Budgets' Sheet. Notice Excel automatically selects the full dataset. 2 Click on the 'Sort' icon on the 'Data' tab o.. WB2- Home Insert Page Layout Formulas Data Review View Connections Properties NP X Clear Get External Refresh Sort Filter Advanced Data All Edit Links DNTo be able to create subt sort the data by region/office. 1 Click anywhere within the data range of the data to be sorted on the 'DATA Budgets' Sheet. Notice Excel automatically selects the full dataset. 2 Click on the 'Sort' icon on the 'Data' tab. ... WB2-1 Home Insert Page Layout Formulas Data Review View Connections x Clear Properties Get External Refresh Sort Filter All Advanced Data Edit Links G25 XV fx 3 A 'Sort' dropdown menu will appear. Sort Add levels to sort by: My list has headers Sort On Order Color/Icon Sort by Region Blues A to Z C Then by Values A to Z Copy Options. Cancel OK 4 Next to the 'Sort by' select the highest level of the sort hierarchy, in this case 'Region'. Add a second level of the sort hierarchy by clicking on the '+' sign (bottom left above). 6Next to the 'Then by' select the second level of the sort hierarchy, in this case 'Office'. 7 If you want to alphabetize 'Salesperson' within office select 'Salesperson as the third level of the sort hierarchy. 8 Select 'OK' in the bottom right corner of the Sort window. Notice that in the 'Order' column of the Sort window you can select the sort sequence of each sort key. Sorting data is useful in many instances, particularly when doing a 'binary search' in reference functions using the MATCH or VLOOKUP functions - Company Forecasting (Workbook 8). Creating the Subtotals Transform the data on the 'DATA Budgets' Sheet as follows: 1 Click anywhere within the data and the 'Data' tab select 'Subtotal'. A 'Subtotal' pop-up window will appear. Subtotal At each change in: Region Use function: Sum Add subtotal to: Region Office Salesperson Product Replace current subtotals Page break between groups Summary below data Remove All Cancel OKRemove All Cancel OK 2 Select 'Region' in the "At each change in:' box. 3 Select 'Sum' in the "Use function:" box. 4 In the 'Add subtotal to:' box select 'Budget', 'Actual', "Variance' and 'Pct Variance'. 5 Check 'Replace current subtotals' if it is not checked and click on 'OK' at the bottom of the pop-up window. 6 Repeat this step, this time selecting 'Office' in the 'At each change in:' box and deselecting 'Replace current subtotals'. You should now have a table with subtotals by Office subordinate to subtotals by Region. Enable filtering of the table by clicking anywhere within the table and in the 'Data' tab selecting 'Filter'. Home Insert Page Layout Formulas Data Review View @ From HTML Connections Az TX Clear From Text Properties New Database Query Refresh Edit Links A + Sort All Filter Advanced Analyzing the Data You are the National Sales Manager and you need to select the best salesperson to replace a retiring regional sales manager. Who would you pick based on the highest 'Pct Variance"? Filter on 'Salesperson' and remove blanks (last entry on list) so the selection will exclude subtotals by office and region. Filter on 'Pct Variance', deselect "All" and select the entry with the largest positive value (105%). Who would you Amelia pst favorable dollar 'Variance'? Remember t Isabella er of 'Pct Variance' above. Lucas Who would you William ghest 'Actual' sales? Which way do you think would be the best criteria for selecting the best candidate? How many of the salespeople were below budget? Filter on Variance less than zero Variance Sort 2+ Ascending A+ Descending By color: None Filter By color: None Less Than And Or Choose OneWho would you pick based on the most favorable dollar 'Variance'? Remember to 'Clear Filter' the lter of 'Pct Variance' above. |:EJ Who would you :hest 'Actual' sales? Which way do - best criteria for selecting the best candidate? How many oft J -low budget? Filter on Variance less than zero. 0 Variance Sort 4 Ascending ii Descending By color: None 3 Filter By color: None 3 -0 '-' 0 And if? Or _ Choose One a I '_ Q hem; 1 I (Select All) _' ${322.63?} _' $698.50?) ' Eff? anal How many sales people in South region were below budget? Which salesperson sold the most product A? Select product A and look by salesperson. Who would you pick based on the highest 'Actual' sales? Which way do y Amelia e best criteria for selecting the best candidate? Isabella How many of the Lucas below budget? Filter on Var William Variance Sort 4+ Ascending + Descending By color: None Filter By color: None Less Than 0 And or Choose One Q. Search (Select All) $(822,637) $(698,507) $(593 803) low many sales people in South region were below budget? Which salesperson sold the most product A?Which way do you think would be the best criteria for selecting the best candidate? How many of th Highest Actual sales Filter on Var Highest Pct Variance Greatest dollar Variance 2+ Ascending A+ Descending By color: |None Filter By color: None Less Than 0 . And Choose One Q Search (Select All) $(822,637) $(698,507) L$(593 803) How many sales people in South region were below budget? Which salesperson sold the most product A? Select product A and look by salesperson. For which product were the nationwide sales behind budget? Remove the filter on 'Salesperson' so you can see regional and national totals. Filter on each 'Product' (Column E) retaining the blanks (so you can see the totals) but removing first Product 'B' and then Product 'A' and look at the 'Variance'.I f Eff mm low many sales people in South region were below budget? |Which salesperson sold the most product A? Select product A and look by salesperson. or which prod .- l - - Remove the Filter on ea - '. u. ide sales behind budget? ' so you an see regional and national totals. J retaining the blanks [so you an see the totals] but removing rst Product '3' and then Product 'A' and look at the 'Variance'. _' $698.50?) I f Eff mm How many sales people in South region were below budget? I:| Which salesperson sold the most product A? Select product A and look by salesperson. I:| For which product were the nationwide sales behind budget? Remove the lter on 'Salesperson' so you an see regional and national totals. Filter on each 'Product' [Column E] retaining the blanks [so you an see the totals] but removing rst Product '3' and then Product 'A' and look at the 'Variance'. :15 2 3 4 S 5 Ir' 8 9 O 1 2 3 4 5 E I? S SHEET ROADMAP This Sheet contains the Data and Workspace for the 'Sheet: DATA ANALYSIS Budgets' Sheet. Region Office Salespers - t Product Budget Actual Varianc Pct Variar West Seattle Amelia B $ 694,878 $ 1,087,139 $ 392,261 56% North Chicago Ann B $ 855,382 $ 862,578 $ 7,195 1% South Dallas Ava B $ 1,486,512 $ 919,310 $ (567,202) -38% South Atlanta Benjamin A $ 1,212,239 $ 1,223,259 $ 11,021 1% West LA Charlotte B $ 938,284 $ 1,175,881 $ 237,597 25% South Atlanta Elijah A $ 1,183,109 $ 944,748 $ (238,361) -20% North NYC Ellen A $ 935,792 $ 576,344 $ (359,448) -38% West Seattle Evelyn A $ 1,486,433 $ 787,925 $ (698,507) -47% West Seattle Harper A $ 1,370,820 $ 927,104 $ (443,717) -32% North NYC Harry B $ 1,354,745 $ 1,128,263 $ (226,483) -17% West LA Isabella A $ 1,330,645 $ 508,008 $ (822,637) -62% South Atlanta James B $ 882,325 $ 1,144,129 $ 261,805 30% North NYC John A $ 764,102 $ 502,166 $ (261,936) -34% North NYC Larry A $ 1,250,674 $ 656,871 $ (593,803) -47% South Dallas Logan A $ 1,065,151 $ 585,730 $ (479,421) -45% South Dallas Lucas A $ 332,711 $ 680,865 $ 348,154 105% South Dallas Mason A $ 1,334,023 $ 865,579 $ (468,444) -35% West LA Mia A $ 1,426,748 $ 1,079,497 $ (347,251) -24% North Chicago Noah A $ 723,763 $ 1,037,287 $ 313,525 43% South Atlanta Oliver B $ 1,173,955 $ 1,135,732 $ (38,223) -3% South Dallas Olivia A $ 862,124 $ 645,064 $ (217,060) -25% North Chicago Peter A $ 633,159 $ 627,673 $ (5,487) -1% West LA Sophia B $ 875,622 $ 1,096,457 $ 220,835 25% North Chicago William A $ 1,492,344 $ 1,358,742 $ (133,601) -9%EXHIBIT: Assignment Required of Pre-Screen Job Candidates for Financial Analyst position Blade Urban Air Mobility Financial Analyst Exam Congratulations on making it to the next round of candidates for the Financial Analyst position @ Blade! We've had hundreds of candidates apply, and you are one of the top candidates that remain. To evaluate your candidacy moving forward, we are requesting you submit to us an example of your analytical capabilities. You should have also received a separate excel file with data that requires your analysis. 1. The Attached data set contains anonymized data for a portion of Blade's ground-travel expenses for June - Aug 2019. The reason for each transaction is contained as an explanation on a separate tab. a. "Executive Summary" - Please submit your top 3 conclusions from the data (250 Words Max) b. "Conclusions" - Please detail your reasoning behind each of these conclusions (500 Words Max) c. "Backup" Please include whatever graphs, charts, references you deem fit (1 Page per conclusion Max) d. "Action Items" - What Action Items do you recommend to improve company performance? (500 Words Max) You can see that in addition to expecting the candidate to complete the data analysis the company expected the candidate to go further. This assignment demonstrates that being a good financial analyst requires more than just being able to process the data. It requires: Developing sound and thoughtful hypotheses Developing analyses that test these hypotheses Synthesizing the results and supporting your conclusions Presenting your conclusions in a clear and concise document or presentation Converting your conclusions into action items This course primarily focuss on the analysis, but the complementary skills are equally as important. Analyze the data in the 'DATA AN" ons. As in the prior few Data Analyses the 'DATA Blade' Sheet by creating a second window arranged vertically to the right. To accomplish this, see instruction Which employee had the highest Blair_Felts Bowie_Tam What 'Expense Description' was ti Brendan_Kelly Cal_Thompson What percent of the total trips into Callen_Morris y, NYC Suburbs) were in New York City? Conner_Clay Conrad_Grimmer DATA ANAI Emi_Suzuki GAAP DATA ANALYSIS Budgets DATA Budgets DATA ANALYSIS Blade DA Emily_AntnerIt requires: Developing sound and thoughtful hypotheses Developing analyses that test these hypotheses Synthesizing the results and supporting your conclu Presenting your conclusions in a clear and concise d Converting your conclusions into action items This course primarily focuss on the analysis, but the co Analyze the data in the 'DATA ANALYSIS Blade' Sheet As in the prior few Data Analyses, you are going to wa Analyze the data in the 'DATA ANALYSIS Blade' Sheet to answerthe following questions. As in the prior'few Data Analyses, you are going to want to complete this Sheet with the 'DATA Blade' Sheet by creating a second window arranged vertically to the right. To accomplish this, see instructions on Row 31 of the 'EXCELTa bles' Sheet. Which employee had the highest Transaction Amount per Mile? |:| what 'Expense Description' was the most frequently used? :13 What percent - ' Employee Working Late HQ Employee Misc Errand Passenger Connection between airport terminals Passenger Ground Travel Passenger Ground Travel due to Weather Issues Passenger Last Minute Ground Travel Passenger Significant Issue with passenger's flight Passenger Facing Employee Misc Errand