Final practice

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 01:10:24 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 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- 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 "Quanity Needed" is larger than "Quantity in Stock". If it is correct, then the function should display "TRUE", if not "FALSE". f-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 ). 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 .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 loctions 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 teh data in the "Inventory List" sheet, complete the Bottom Table by find the total Quantity in Stock (column E in the "Inventory List" sheet) and total Quantity Needed 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: . 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 Quatntity 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 theer 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 ltters 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].

Godwin Chacko 12/07/2023 01:10:24 Location Item Item Category Unit Price Quantity in Stock Calgary Automotive battery Automative 59 175 Calgary Car Shelter Automative 50 79 Calgary Frying Pan 75 6 Calgary Coffee Table 11 64 Calgary Diffuser 38 53 Calgary Hammer Drill Tools 51 113 Calgary Belt Sander Tools 93 122 Calgary Circular Saw Tools 57 112 Calgary Wrench Set Tools 19 190 Charlotteto Automotive battery Automative 59 195 Charlotteto Car Shelter Automative 50 176 Charlotteto Frying Pan 75 152 Charlotteto Coffee Table 11 22 Charlotteto Diffuser 38 106 Charlotteto Hammer Drill Tools 51 20 Charlotteto Belt Sander Tools 93 170 Charlotteto Circular Saw Tools 57 90 Charlotteto Wrench Set Tools 19 71 Edmonton Automotive battery Automative 59 11 Edmonton Car Shelter Automative 50 105 Edmonton Frying Pan 75 23 Edmonton Coffee Table 11 182 Edmonton Diffuser 38 146 Edmonton Hammer Drill Tools 51 61 Edmonton Belt Sander Tools 93 181 Edmonton Circular Saw Tools 57 64 Edmonton Wrench Set Tools 19 190 Halifax Automotive battery Automative 59 75 Halifax Car Shelter Automative 50 91 Halifax Frying Pan 75 128 Halifax Coffee Table 11 133 Halifax Diffuser 38 96 Halifax Hammer Drill Tools 51 60 Halifax Belt Sander Tools 93 176 Halifax Circular Saw Tools 57 86 Halifax Wrench Set Tools 19 97 Montreal Automotive battery Automative 59 126 Montreal Car Shelter Automative 50 55 Montreal Frying Pan 75 126 Montreal Coffee Table 11 81 Montreal Diffuser 38 24 Montreal Hammer Drill Tools 51 0 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

Godwin Chacko 12/07/2023 01:10:24 Montreal Belt Sander Tools 93 79 Montreal Circular Saw Tools 57 154 Montreal Wrench Set Tools 19 112 Ottawa Automotive battery Automative 59 39 Ottawa Car Shelter Automative 50 92 Ottawa Frying Pan 75 153 Ottawa Coffee Table 11 181 Ottawa Diffuser 38 97 Ottawa Hammer Drill Tools 51 9 Ottawa Belt Sander Tools 93 99 Ottawa Circular Saw Tools 57 43 Ottawa Wrench Set Tools 19 135 Regina Automotive battery Automative 59 144 Regina Car Shelter Automative 50 52 Regina Frying Pan 75 124 Regina Coffee Table 11 73 Regina Diffuser 38 32 Regina Hammer Drill Tools 51 61 Regina Belt Sander Tools 93 136 Regina Circular Saw Tools 57 162 Regina Wrench Set Tools 19 196 Toronto Automotive battery Automative 59 178 Toronto Car Shelter Automative 50 84 Toronto Frying Pan 75 189 Toronto Coffee Table 11 65 Toronto Diffuser 38 125 Toronto Hammer Drill Tools 51 100 Toronto Belt Sander Tools 93 81 Toronto Circular Saw Tools 57 70 Toronto Wrench Set Tools 19 7 Vancouver Automotive battery Automative 59 142 Vancouver Car Shelter Automative 50 173 Vancouver Frying Pan 75 28 Vancouver Coffee Table 11 123 Vancouver Diffuser 38 43 Vancouver Hammer Drill Tools 51 20 Vancouver Belt Sander Tools 93 195 Vancouver Circular Saw Tools 57 120 Vancouver Wrench Set Tools 19 23 Victoria Automotive battery Automative 59 74 Victoria Car Shelter Automative 50 166 Victoria Frying Pan 75 156 Victoria Coffee Table 11 14 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

Godwin Chacko 12/07/2023 01:10:24 Victoria Diffuser 38 155 Victoria Hammer Drill Tools 51 162 Victoria Belt Sander Tools 93 110 Victoria Circular Saw Tools 57 37 Victoria Wrench Set Tools 19 176 Major appliances

Godwin Chacko 12/07/2023 01:10:24 Inventory value Quantity Needed Reorder Needed? 10325 182 TRUE 3950 200 TRUE 450 144 TRUE 704 88 TRUE 2014 78 TRUE 5763 49 FALSE 11346 4 FALSE 6384 69 FALSE 3610 71 FALSE 11505 13 FALSE 8800 46 FALSE 11400 25 FALSE 242 106 TRUE 4028 102 FALSE 1020 40 TRUE 15810 127 FALSE 5130 30 FALSE 1349 85 TRUE 649 142 TRUE 5250 34 FALSE 1725 113 TRUE 2002 131 FALSE 5548 102 FALSE 3111 119 TRUE 16833 196 TRUE 3648 171 TRUE 3610 154 FALSE 4425 45 FALSE 4550 184 TRUE 9600 4 FALSE 1463 137 TRUE 3648 22 FALSE 3060 69 TRUE 16368 157 FALSE 4902 171 TRUE 1843 34 FALSE 7434 162 TRUE 2750 177 TRUE 9450 144 TRUE 891 194 TRUE 912 56 TRUE 0 171 TRUE

Godwin Chacko 12/07/2023 01:10:24 7347 115 TRUE 8778 38 FALSE 2128 48 FALSE 2301 12 FALSE 4600 9 FALSE 11475 179 TRUE 1991 112 FALSE 3686 110 TRUE 459 17 TRUE 9207 179 TRUE 2451 106 TRUE 2565 102 FALSE 8496 176 TRUE 2600 58 TRUE 9300 149 TRUE 803 153 TRUE 1216 24 FALSE 3111 4 FALSE 12648 82 FALSE 9234 63 FALSE 3724 174 FALSE 10502 159 FALSE 4200 194 TRUE 14175 125 FALSE 715 31 FALSE 4750 159 TRUE 5100 53 FALSE 7533 30 FALSE 3990 113 TRUE 133 146 TRUE 8378 173 TRUE 8650 68 FALSE 2100 106 TRUE 1353 119 FALSE 1634 12 FALSE 1020 175 TRUE 18135 2 FALSE 6840 12 FALSE 437 151 TRUE 4366 68 FALSE 8300 111 FALSE 11700 161 TRUE 154 47 TRUE

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

Godwin Chacko 12/07/2023 01:10:24 5890 104 FALSE 8262 12 FALSE 10230 78 FALSE 2109 2 FALSE 3344 5 FALSE

Sum of Inventory Value for Each Item and Location Top Table Location Tools Automative Total Trend Toronto 16756 19640 14702 Victoria 23945 17744 12666 Calgary 27103 3168 14275 Item Category Quantity in Stock Quantity Needed Real Need Bottom Table Tools Automative Estimated Loss Rate 7.3% AVERAGEIF COUNTIF IF Function MAX UPPER LEFT 11.8 Major Appliances Major Appliances

Final practice

Related Documents