Exam_Review_Updated

.xlsx

School

Toronto Metropolitan University *

*We aren’t endorsed by this school

Course

100

Subject

Industrial Engineering

Date

Jan 9, 2024

Type

xlsx

Pages

Uploaded by EarlHerring745

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

Sum - Inventory VaLocation Item Category Calgary Charlottetown Edmonton Halifax Montreal Ottawa Automative 14275 20305 5899 8975 10184 6901 3168 15670 9275 14711 11253 17152 Tools 27103 23309 27202 26173 18253 14682 Total Result 44546 59284 42376 49859 39690 38735 Major appliances Th Sli 20 If t an if t Ex sli

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

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help