Summary\t\u00a0\tCreate a Microsoft Excel file with four worksheets that provides extensive use of Excel capabilities including creating a pivot table, graphing, and written analysis and recommendations in support of a business enterprise.\t\u00a0\tA large rental car company has two metropolitan locations, one at the airport and another centrally located in downtown. It has been operating since 2015 and each location summarizes its car rental revenue quarterly. Both locations rent two classes of cars: economy and premium. Rental revenue is maintained separately for the two classes of rental vehicles.\t\u00a0\tThe data for this case resides in the file Excel Project 2-Data.txt and can be downloaded by clicking on the Assignments tab, then on the data tile name. It is a text file with the file type .txt).\t\u00a0\tDo not create your own data. You must use the data provided and only the data provided.\t\u00a0\tRequirement\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 a.\u00a0\u00a0 Open Excel and save a blank worksheet with the\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 following name:\t1 \ufffd\u00a0\u00a0\u00a0\u00a0 “Student’s First InitialLast Name Excel Project 2″\ufffd\t \ufffd \ufffd\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 Example: JSmith Excel Project 2\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 b.\u00a0 Set Page Layout to Landscape\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\t2 \ufffd\u00a0\u00a0\u00a0\u00a0 Change the name of the worksheet to Analysis by.\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 In the Analysis by worksheet, enter the four labels\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 vertically in column A in the following order: Name:,\t3 \ufffd \ufffd \ufffd \ufffd\u00a0 Class\/Section:, Project:, Date Due: with a row between\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 each label, please note the colon, \ufffd\u00a0\u00a0 : \ufffd \ufffd , after each label.\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 It may be necessary to adjust the column width so the\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 four labels are clearly visible.\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 a.\u00a0 In the Analysis by worksheet, with all entries in\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 column C, enter the appropriate values for Name,\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 Class and Section, Project, Date Due.\t4 \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 b.\u00a0 It may be necessary to adjust the column width so\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 the four labels are clearly visible.\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 Create new worksheets:\u00a0 Data, Initial Analysis, Profit\t5 \ufffd \ufffd \ufffd \ufffd\u00a0 Analysis. Upon completion, there should be Analysis\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 by as well as the three newly created worksheets.\t \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0 Delete any other worksheets.\t\u00a0\t\u00a0\t\u00a0\t\u00a0\t \ufffd \ufffd\u00a0 If necessary, reorder the four worksheets so they are in\t6 \ufffd\u00a0\u00a0\u00a0\u00a0 the following order: Analysis by, Data, Initial \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd\u00a0 Analysis, Profit Analysis. \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\t \ufffd \ufffd\u00a0 In the Data worksheet, import the text file Excel\t \ufffd \ufffd\u00a0 Project 2-Data.txt. \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd\u00a0 Though the intent is to import the text file into the Data\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 worksheet, sometimes when text data is imported into\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 a worksheet, a new worksheet is created.\u00a0 If this\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 happens, delete the blank Data worksheet, and then\t \ufffd \ufffd\u00a0\t7 \ufffd\u00a0\u00a0\u00a0\u00a0 rename the new worksheet which HAS the recently\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 imported data as “Data.”\ufffd\u00a0 It may be necessary to\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 change Revenue to Currency format with NO decimal\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 points and to change NumCars to Number format with\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 NO decimal points, because of the import operation.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd\u00a0 This may or may not occur, but in case it does it needs\t \ufffd \ufffd\u00a0 to be corrected. \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 a.\u00a0 In the Data worksheet, create an Excel table with\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\t \ufffd \ufffd\u00a0 the recently imported data.\u00a0 Pick a style with the\t \ufffd \ufffd\u00a0 styles group to format the table. The style \ufffd should\t \ufffd \ufffd\u00a0 highlight the field names in the first row. \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd\u00a0 b.\u00a0 Ensure NO blank spaces are part of the specified\t \ufffd \ufffd\u00a0 data range. Insure the table has headers. \ufffd\u00a0\u00a0 Insure\t8 \ufffd\u00a0\u00a0\u00a0\u00a0 that Header Row and Banded Rows are selected in\t \ufffd \ufffd\u00a0 the Table Style Options Group Box.\u00a0 Do NOT check\t \ufffd \ufffd\u00a0 the Total Row. \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\t \ufffd \ufffd\u00a0 c.\u00a0 Delete all 2017 data.\u00a0 There should be one row for\t \ufffd \ufffd\u00a0 titles and thirty-two rows of data after the 2017 data\t \ufffd \ufffd\u00a0 is deleted. \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\u00a0\t \ufffd \ufffd\u00a0 d.\u00a0 There should be no column or data truncation.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 In the Data worksheet, select the table entire table\t \ufffd \ufffd\u00a0 (data and headers) using a mouse. \ufffd \ufffd \ufffd\u00a0\u00a0\t9 \ufffd\u00a0\u00a0\u00a0\u00a0 a.\u00a0 Copy the table to the Initial Analysis worksheet.\t \ufffd \ufffd\u00a0 The upper left hand corner of the header\/data\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 should be in cell A1. \ufffd \ufffd\u00a0\u00a0\u00a0\u00a0\t \ufffd \ufffd\u00a0 b.\u00a0 Adjust columns widths if necessary to insure all\t \ufffd \ufffd\u00a0 data and field names are readable. \ufffd \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 a.\u00a0 Copy the entire table (column headings and data) to\t \ufffd \ufffd\u00a0 the Initial Analysis worksheet.\u00a0 Some column\t10 \ufffd\u00a0\u00a0 adjustments may be necessary to insure the field\t \ufffd \ufffd\u00a0 names as well as the data is all readable. \ufffd \ufffd \ufffd\u00a0\u00a0\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\t \ufffd \ufffd\u00a0 b.\u00a0 Format all data, both text and numerical content, as\t \ufffd \ufffd\u00a0 Arial 10 normal \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd\u00a0\t11 \ufffd\u00a0\u00a0 a.\u00a0 In the Initial Analysis worksheet, using the table\t \ufffd \ufffd\u00a0 just copied there, create a Pivot Table. \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd \ufffd\u00a0\u00a0\t\u00a0\tb.\u00a0 It should be located a few columns to the right of\t \ufffd \ufffd\u00a0 the data and begin in Row 1.\u00a0 It should provide the\t \ufffd \ufffd\u00a0 combined revenue for Economy as well as Premium\t \ufffd \ufffd\u00a0 class cars, and display the total revenue for both\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 Airport and Downtown locations, and include a total\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 of all revenue for each location and each vehicle\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 type.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 a.\u00a0 In the Initial Analysis worksheet, create a graph\t \ufffd \ufffd\u00a0 that shows the total revenue for both vehicle types\t \ufffd \ufffd\u00a0 at each location.\t \ufffd \ufffd\u00a0 b.\u00a0 The upper left corner of the graph should be one or\t12 \ufffd\u00a0\u00a0 two rows below the above referenced pivot table\t \ufffd \ufffd\u00a0 and left aligned with the pivot table.\t \ufffd \ufffd\u00a0 c.\u00a0 Insure the location and type of car is clearly\t \ufffd \ufffd\u00a0 indicated in the graph.\u00a0 Only use 2015 and 2016\t \ufffd \ufffd\u00a0 data.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 In the Initial Analysis worksheet, Question 1: Which\t \ufffd \ufffd\u00a0 location, for 2015 and 2016 combined, produced the\t \ufffd \ufffd\u00a0 most revenue?\u00a0 How much revenue was it?\t13 \ufffd\u00a0\u00a0\t \ufffd \ufffd\u00a0 Label your response Question 1\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 a.\u00a0 From the Data worksheet, copy the entire table\t \ufffd \ufffd\u00a0 (column headings and data) to the Profit Analysis\t14 \ufffd\u00a0\u00a0 worksheet.\t \ufffd \ufffd\u00a0 b.\u00a0 Some column adjustments may be necessary to\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 insure the field names as well as the data is all\t \ufffd \ufffd\u00a0 readable.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 In the Profit Analysis worksheet, in the column to the\t15 \ufffd\u00a0\u00a0 immediate right of NumCars label, create a label called\t \ufffd \ufffd\u00a0 Overhead.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 a.\u00a0 In the Profit Analysis worksheet, in the first cell\t16 \ufffd\u00a0\u00a0 only under the Overhead label, calculate the\t \ufffd \ufffd\u00a0 overhead that corresponds to the number of\t \ufffd \ufffd\u00a0 NumCars (number of cars rented).\t \ufffd \ufffd\u00a0 b.\u00a0 Use an IF statement in the calculation to determine\t \ufffd \ufffd\u00a0 whether to use $50 or $75 times the NumCars so\t \ufffd \ufffd\u00a0 that the Overhead value represents the total\t \ufffd \ufffd\u00a0 Overhead costs for the Number of Cars (NumCars)\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 rented.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 Overhead costs include maintenance, cleaning, and\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 administrative expenses associated with a car rental.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 The Overhead for one rental transaction is $50 for an\t \ufffd \ufffd\u00a0 economy car and $75 for a premium car.\u00a0 This\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 Overhead cost includes maintenance, cleaning, and\t \ufffd \ufffd\u00a0 administrative costs for each car rental.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t17 \ufffd\u00a0\u00a0\t \ufffd \ufffd\u00a0 In the Profit Analysis worksheet, copy the formula to\t \ufffd \ufffd\u00a0 all the cells below in the column.\t \ufffd \ufffd\u00a0 Adjust the column width if necessary for legibility.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 In the Profit Analysis worksheet, in the next column to\t18 \ufffd\u00a0\u00a0 the right, create a label called Profit.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 In the Profit Analysis worksheet, profit represents the\t \ufffd \ufffd\u00a0 difference between Revenue and Overhead (Revenue\t \ufffd \ufffd\u00a0 “\ufffd Overhead).\u00a0 Profit is for all cars rented during the\t \ufffd \ufffd\u00a0 quarter.\t19 \ufffd\u00a0\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 a.\u00a0 In the first cell below Profit, calculate the profit,\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 then copy the formula for all cells in that column.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 b.\u00a0 Adjust the column width if necessary for legibility.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 In the Profit Analysis worksheet, in the next column to\t20 \ufffd\u00a0\u00a0 the right, create a label called Profit per Car\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0 a.\u00a0 In the Profit Analysis worksheet, in the next cell\t21 \ufffd\u00a0\u00a0 down under the Profit per Car label, calculate the\t \ufffd \ufffd\u00a0 profit per car rented.\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t \ufffd \ufffd\u00a0\t22\tIn the Profit Analysis worksheet, create a Pivot Table\tthat provides the total profit for Economy as well as\tPremium class cars rented as well as totals by location.\tInclude a grand total profit for all cars rented.\u00a0 Only use\t2015 and 2016 data.\u00a0 Position the upper left corner of\t\u00a0\tthe Pivot Table in Row A two columns to the right of the\t\u00a0\ttable in the Profit Analysis worksheet.\t\u00a0\t\u00a0\t23\tIn the Profit Analysis worksheet, create a Pivot Table\tthat shows the Average Profit per car, averaged by Car\tclass and averaged by location.\u00a0 Only use 2015 and\t2016 data. Position the upper left corner to align with\tthe pivot table above and two rows below the pivot\ttable above. The column and row categories should\t\u00a0\tmatch those of the above pivot table.<\/p>\n","protected":false},"excerpt":{"rendered":"
Summary \u00a0 Create a Microsoft Excel file with four worksheets that provides extensive use of Excel capabilities including creating a pivot table, graphing, and written analysis and recommendations in support of a business enterprise. \u00a0 A large rental car company Read More …<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-224812","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/224812","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=224812"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/224812\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=224812"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=224812"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=224812"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}