Exam_Review_Updated

Godwin Chacko 12/07/2023 14:32:41 Save the workbook to your Desktop or Documents (save your workbook frequently so that you will not lose any of your work). A retail chain company is asking you to perform some analysis using their inventory data. 1- On the " Inventory List " sheet: a- Add your name and today's date and time (using the date and time functions) to the footer of the sheet. Your name should appear in the left corner, date and time should appear in the center. b- Insert an empty column between columns E and F. The new column should be titled "Inventory Value". For each row, the cells in that column should display the value of "Unit Price X Inventory in Stock". c- Use the Freeze Pane to freeze the top row so all column headings are still visible when scrolling down to the end of the sheet. d- Sort the data in the sheet by Location (column A) in ascending order. e- In column E (titled "Recorder Needed"), in each cell create an IF function that will check if the "Quantity Needed" is larger than "Quantity in Stock". If it is correct, then the function should display "TRUE", if not "FALSE". f- Use conditional formatting to highlight in red all cells in column H where "Recorder Needed = TRUE". g- Change the color for the sheet's tab to green. h- Convert the data in the sheet (A1:H91) to a table. Use any of the "yellow" styles. Your table should include the "Filter Button" for the column headings. Using the Location filter to filter out "Regina". 2- Using the data in the " Inventory List" sheet. a- Create a PivotTable (in a new sheet) that displays the Sum of Inventory Value for each Item for each Location (tip: use "Location" in COLUMNS, "Items" in ROWS, "Item categories" in FILTERS). Name the sheet "Pivot Table". b- Create a Slicer based on Item Categories. c- Create a PivotChart, stacked column on a new sheet. Change the chart title to "Items Total Value". Rename the new sheet Inventory Summary . Change the tab color to "green". d- Filter the chart to include only "Automotive" and "Tools". 3- On " Analysis " sheet, in the Top Table, a- Use merge cells to merge cells B1:D1. Rotate the text by 5 degree (i.e., the right-hand end of the text should be higher than the left-hand text by 5 degrees). Change the font color to red, font size to 14 pt., font type to "Arial". Resize the columns and the row if necessary so the full text is visible. b- Format the data in cell B3:D5 as Currency with one digit after the decimal point. c- Use the SUM function to complete the calculations for the "Total" column (cells E3:E5). The values should represent the total inventory for each location. Format the data as Currency with two digits after the decimal point. d- In the "Trend" column (cells F3-F5), create sparklines that represent the inventory values across the three types of Item Categories (Tools, Major Appliances, Automation) for each Location. e- Create a 3-D Pie Chart that shows the Total (in cell E3:E5) for each of the three locations (i.e., each Location should be a slice of the pie). The title of the chart should be "Inventory by Location". The title should be displayed in "Arial Black" size 20 pt. The chart should show the data labels as percentages (make sure the labels are in a readable font). In terms of styling, change the top and bottom bevels to a new style (any style is OK) and set the Pie Explosion to 10%. Move the chart to a new sheet and name the sheet "Pie Chart". 4- On " Analysis" sheet, in the Bottom Table, a- Using the data in the "Inventory List" sheet, complete the Bottom Table by creating formulas (SUMIF function) to find the total Quantity in Stock (column E in the "Inventory List" sheet) and total Quantity Needed (column G in the Inventory List sheet) for each Item Category. To complete the rest of the table, you need to use the correct absolute referencing. Format these values as numbers (not currency) with no digits after the decimal point. b- Given that some of the existing inventory is likely to be misplaced, it is always good practice to have extra inventory at hand. Based on historical data, you estimate the inventory loss rate to be 7.3%. In cells E9:E11 calculate the adjusted inventory needed for each item category such that: Real Need = Quaintly Needed + (Quantity in Stock * Estimated Loss Rate). When referring to the Estimated Loss Rate in your formula, you must refer to cell E13, and you must use the correct absolute referring (which uses $ signs) to allow you to auto-fill the E10:E11. Round up the values to the nearest number. c- Create a 3-D clustered column chart that displays the Quantity in Stock (C9:C11), Quantity Needed (D9:D11), and Real Need (E9:E11) for each Item Category (i.e., the Item Category should be on the x-axis; for each Category there should be three columns representing Quantity in Stock, Quantity Needed and Real Need). Each column should be rotated at 50 degree on the x-axis. The title of your chart should be "Inventory Levels". You should show data labels (as data callouts that include both the Item Category and the value) and the chart legend (to the right-hand side of the chart). Move the chart to a new sheet and name the sheet "New Chart". 5- On " Analysis " sheet, a- In cell A17, create an AVERAGEIF function that calculates the average of the "Unit Price" (column D in the Inventory List sheet) for items in "Toronto" (column B). Format as currency with two digits after the decimal point. b- In cell B17, create a COUNTIF function that looks for the total number of items in the Inventory List sheet that are "Belt Sander" (in column B). Use Defined Names function in Excel to label cell B17 as "Belt Sander". c- In cell C17, create an IF function, that checks whether the total inventory value for Toronto for the three item categories (from the Top Table) is larger than that for Victoria. If true it should return "Correct", else it should return "Not Correct". d- In cell D17, use the MAX function to determine the maximum value in cells B3:D5. e- In cell E17, use the UPPER function to convert the text in cell B9 to upper case. f- In cell F17, use the LEFT function to output the first 3 letters in the word contained in cell B11. 6- Use the Excel "spelling" function to check and correct all the spelling mistakes in this instruction sheet (i.e., in this textbox). [Hint: There are 9 spelling mistakes].

Automative Major appliances Tools Total Result 0 10000 20000 30000 40000 50000 60000 70000 Location Calgary Charlottetown Edmonton Halifax Montreal Ottawa Regina Toronto Vancouver Victoria

Regina Toronto Vancouver Victoria Total Result 11096 14702 17028 12666 122031 11319 19640 5087 17744 125019 28717 16756 26432 23945 232572 51132 51098 48547 54355 479622 his shape represents a slicer. icers are supported in Excel 010 or later. the shape was modified in n earlier version of Excel, or the workbook was saved in xcel 2003 or earlier, the icer cannot be used.

Location Item Item Category Unit Price Quantity in Stock Toronto Automotive battery Automative 59 178 Toronto Car Shelter Automative 50 84 Vancouver Automotive battery Automative 59 142 Vancouver Car Shelter Automative 50 173 Montreal Automotive battery Automative 59 126 Montreal Car Shelter Automative 50 55 Calgary Automotive battery Automative 59 175 Calgary Car Shelter Automative 50 79 Ottawa Automotive battery Automative 59 39 Ottawa Car Shelter Automative 50 92 Halifax Automotive battery Automative 59 75 Halifax Car Shelter Automative 50 91 Victoria Automotive battery Automative 59 74 Victoria Car Shelter Automative 50 166 Edmonton Automotive battery Automative 59 11 Edmonton Car Shelter Automative 50 105 Charlotteto Automotive battery Automative 59 195 Charlotteto Car Shelter Automative 50 176 Toronto Frying Pan 75 189 Toronto Coffee Table 11 65 Toronto Diffuser 38 125 Vancouver Frying Pan 75 28 Vancouver Coffee Table 11 123 Vancouver Diffuser 38 43 Montreal Frying Pan 75 126 Montreal Coffee Table 11 81 Montreal Diffuser 38 24 Calgary Frying Pan 75 6 Calgary Coffee Table 11 64 Calgary Diffuser 38 53 Ottawa Frying Pan 75 153 Ottawa Coffee Table 11 181 Ottawa Diffuser 38 97 Halifax Frying Pan 75 128 Halifax Coffee Table 11 133 Halifax Diffuser 38 96 Victoria Frying Pan 75 156 Victoria Coffee Table 11 14 Victoria Diffuser 38 155 Edmonton Frying Pan 75 23 Edmonton Coffee Table 11 182 Edmonton Diffuser 38 146 Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances Major appliances

Inventory Value Quantity Needed Reorder Needed? 10502 182 True 4200 194 True 8378 173 True 8650 68 False 7434 162 True 2750 177 True for freeze j 10325 182 True 3950 200 True 2301 12 False 4600 9 False 4425 45 False 4550 184 True 4366 68 False 8300 111 False 649 142 True 5250 34 False 11505 13 False 8800 46 False 14175 125 False 715 31 False 4750 159 True 2100 106 True 1353 119 False 1634 12 False 9450 144 True 891 194 True 2900 912 56 True 450 144 True 704 88 True 2014 78 True 11475 179 True 1991 112 False 2236 3686 110 True 9600 4 False 1463 137 True 3648 22 False 11700 161 True 154 47 True 5890 104 False 1725 113 True 2002 131 False 5548 102 False

11400 25 False 242 106 True 4028 102 False 5100 53 False 7533 30 False 3990 113 True 133 146 True 1020 175 True 18135 2 False 6840 12 False 437 151 True 0 171 True 7347 115 True 8778 38 False 2128 48 False 5763 49 False 11346 4 False 6384 69 False 3610 71 False 459 17 True 9207 179 True 2451 106 True 2565 102 False 3060 69 True 16368 157 False 4902 171 True 1843 34 False 8262 12 False 10230 78 False 2109 2 False 3344 5 False 3111 119 True 16833 196 True 3648 171 True 3610 154 False 1020 40 True 15810 127 False 5130 30 False 1349 85 True

1; 34.07% 2; 36.24% 3; 29.70% Inventory by location 1 2 3

Tools Major Appliances Automative 0 500 1000 1500 2000 2500 3000 3500 4000 4500 Tools; 4090 Major Appliances; 2900 Automative; 2232 Tools; 3424 Major Appliances; 1981 Automative; 2236 Tools; 3723 Major Appliances; 3063 Automative; 2395 Inventory levels Quantity in Stock Quantity Needed Real Need