* The last image is the data for the INVENTORY COUNT.

Instructions: Excel Modeling Tutorial 1.2 Instructions: Excel Modeling Tutorial 1.2 Joe's Original BBQ wants an inventory comparison of their on-file records and the stores' actual inventory counts for the merchandise they sell (men's, women's and children's shirts, three varieties of each). Joe has hired you to create an excel spreadsheet for this comparison. Each of the stores have compiled their records for you (Salt Lake, New Orleans and Morro Bay are the three locations). Joe wants you to create a model to display the data for each of the stores and the on-file records, as well as the actual store totals and the difference from the actual totals and the on- file records. He specifically needs a total of each main category (men, women and children clothing) as well as a grand total of inventory sold. b) Make a new worksheet. Rename to "Inventory Count 11.17". Move to be the first sheet (far left) This worksheet will show all the inventory counts, compared to the records. c) Set up the model to display the information as in the preferred layout above. d) Use VLOOKUP to get the Inventory Counts from the Location Data and Inventory Records tabs to the Inventory Count tab. e) Calculate the Total Actual count from the three locations f) Calculate the Difference between the Inventory Records and the actual Inventory Counts for each location. Add conditional formatting if it makes sense to (be sure to explain this in the memo) Add any other formatting, making this model unique. (Options: change colors/fonts, etc.) Open a new Excel file (note, there is not a specific file to use...now you will create your own). Save as Joes BBQ_Model_FirstLast.xlsx. Using the information above, and the data files, create a re-usable model to compare each store's inventory counts to the main store records. Joe wants a layout similar to this, but there is no expectation on coloring, etc. Joe's Original BBQ Inventory Comparison 11/30/17 Actualinventory Records Salt Lake Morro Bay New Orleans Total Actual Difference 143 35 134 2533 21 1956 290 80 273 2. Notice if there are any errors in the data (Hint: Check New Orleans). a) Make a copy of the Inventory Count tab** Right click on the worksheet name, then click "Move/Copy", be sure "Create a Copy" is selected. (Do not just copy/paste the data!) b) Name the new sheet "Inventory Count 11.17 (adj.)" c) Update the Adjusted tab to reflect the correction(s) 55 AZ 90 15 122 87 21 79 Men Fleece Top XX123 Men Lycra Top XX 134 Men Cotton Top XX 156 Total Men Women Fleece Top xx523 Women Lycra Top XX 534 Women Cotton Top xX555 Total Women Child Fleece Top XX 723 Child Lycra Top XX 734 Child Cotton Top XX 756 Total Child Grand Total 44 243 109 112 58 118 78 241 39 35110 16 30 22585152462 461 205 3 10 976 3. Open a new word file. Using a Memo format, write a memo to Joe: Briefly explain the work you did, including any corrections to the data Add a short interpretation of the data/differences Explain any special formatting/information Joe needs to know 454 997 21 Save both files (as JoesBBQ Model FirstLast and JoesBBQ Memo FirstLast) and upload both to Canvas. 1. Download the data from each of the locations. Move all data into your new Excel file. a) There are four separate data files - three of them are the data from the locations, and then Joe's master records are given too. The data from all four need to be in the same workbook, but each in their own tab. Move the data the way that works best, some will require more adjusting than others. (Hint: Make a list for the New Orleans data, then copy into Excel. Use "Text to Columns" feature (separated by a space) to convert the data to columns, instead of all in one column) Label each tab as appropriate Morro Bay Inventory YY Child Fleece Top XX723 Child Lycra Top XX734 Child Cotton Top XX756 Women Fleece Top XX523 Women Lycra Top XX534 Women Cotton Top XX556 Men Fleece Top XX123 Men Lycra Top XX134 Men Cotton Top XX156 16 New Orleans Men Fleece Top XX123 36000 Men Lycra Top XX134 25 Men Cotton Top XX156 19 Women Fleece Top XX523 44 Women Lycra Top XX534 21 Women Cotton Top XX556 13 Child Fleece Top XX723 39 Child Lycra Top XX734 35 Child Cotton Top XX 756 16 Shirt Count from Salt Lake City Salt Lake XX123 Men Fleece Top Men Lycra Top Men Cotton Top Women Fleece Top Women Lycra Top Women Cotton Top Child Fleece Top Child Lycra Top Child Cotton Top XX134 XX156 XX523 XX534 XX556 XX723 XX734 XX756 Units Men Fleece Top XX123 Men Lycra Top XX134 Men Cotton Top XX156 Women Fleece Top XX523 Women Lycra Top XX534 Women Cotton Top XX556 Child Fleece Top XX723 Child Lycra Top XX734 Child Cotton Top XX756 44 109 Instructions: Excel Modeling Tutorial 1.2 Instructions: Excel Modeling Tutorial 1.2 Joe's Original BBQ wants an inventory comparison of their on-file records and the stores' actual inventory counts for the merchandise they sell (men's, women's and children's shirts, three varieties of each). Joe has hired you to create an excel spreadsheet for this comparison. Each of the stores have compiled their records for you (Salt Lake, New Orleans and Morro Bay are the three locations). Joe wants you to create a model to display the data for each of the stores and the on-file records, as well as the actual store totals and the difference from the actual totals and the on- file records. He specifically needs a total of each main category (men, women and children clothing) as well as a grand total of inventory sold. b) Make a new worksheet. Rename to "Inventory Count 11.17". Move to be the first sheet (far left) This worksheet will show all the inventory counts, compared to the records. c) Set up the model to display the information as in the preferred layout above. d) Use VLOOKUP to get the Inventory Counts from the Location Data and Inventory Records tabs to the Inventory Count tab. e) Calculate the Total Actual count from the three locations f) Calculate the Difference between the Inventory Records and the actual Inventory Counts for each location. Add conditional formatting if it makes sense to (be sure to explain this in the memo) Add any other formatting, making this model unique. (Options: change colors/fonts, etc.) Open a new Excel file (note, there is not a specific file to use...now you will create your own). Save as Joes BBQ_Model_FirstLast.xlsx. Using the information above, and the data files, create a re-usable model to compare each store's inventory counts to the main store records. Joe wants a layout similar to this, but there is no expectation on coloring, etc. Joe's Original BBQ Inventory Comparison 11/30/17 Actualinventory Records Salt Lake Morro Bay New Orleans Total Actual Difference 143 35 134 2533 21 1956 290 80 273 2. Notice if there are any errors in the data (Hint: Check New Orleans). a) Make a copy of the Inventory Count tab** Right click on the worksheet name, then click "Move/Copy", be sure "Create a Copy" is selected. (Do not just copy/paste the data!) b) Name the new sheet "Inventory Count 11.17 (adj.)" c) Update the Adjusted tab to reflect the correction(s) 55 AZ 90 15 122 87 21 79 Men Fleece Top XX123 Men Lycra Top XX 134 Men Cotton Top XX 156 Total Men Women Fleece Top xx523 Women Lycra Top XX 534 Women Cotton Top xX555 Total Women Child Fleece Top XX 723 Child Lycra Top XX 734 Child Cotton Top XX 756 Total Child Grand Total 44 243 109 112 58 118 78 241 39 35110 16 30 22585152462 461 205 3 10 976 3. Open a new word file. Using a Memo format, write a memo to Joe: Briefly explain the work you did, including any corrections to the data Add a short interpretation of the data/differences Explain any special formatting/information Joe needs to know 454 997 21 Save both files (as JoesBBQ Model FirstLast and JoesBBQ Memo FirstLast) and upload both to Canvas. 1. Download the data from each of the locations. Move all data into your new Excel file. a) There are four separate data files - three of them are the data from the locations, and then Joe's master records are given too. The data from all four need to be in the same workbook, but each in their own tab. Move the data the way that works best, some will require more adjusting than others. (Hint: Make a list for the New Orleans data, then copy into Excel. Use "Text to Columns" feature (separated by a space) to convert the data to columns, instead of all in one column) Label each tab as appropriate Morro Bay Inventory YY Child Fleece Top XX723 Child Lycra Top XX734 Child Cotton Top XX756 Women Fleece Top XX523 Women Lycra Top XX534 Women Cotton Top XX556 Men Fleece Top XX123 Men Lycra Top XX134 Men Cotton Top XX156 16 New Orleans Men Fleece Top XX123 36000 Men Lycra Top XX134 25 Men Cotton Top XX156 19 Women Fleece Top XX523 44 Women Lycra Top XX534 21 Women Cotton Top XX556 13 Child Fleece Top XX723 39 Child Lycra Top XX734 35 Child Cotton Top XX 756 16 Shirt Count from Salt Lake City Salt Lake XX123 Men Fleece Top Men Lycra Top Men Cotton Top Women Fleece Top Women Lycra Top Women Cotton Top Child Fleece Top Child Lycra Top Child Cotton Top XX134 XX156 XX523 XX534 XX556 XX723 XX734 XX756 Units Men Fleece Top XX123 Men Lycra Top XX134 Men Cotton Top XX156 Women Fleece Top XX523 Women Lycra Top XX534 Women Cotton Top XX556 Child Fleece Top XX723 Child Lycra Top XX734 Child Cotton Top XX756 44 109