ICA6-I

In-Class Assignment #6 Goldyland Furniture has sent you its regional inventory information in a few different spreadsheets: Northeast ( NE ), Southeast ( SE ), and Midwest ( MW ), as well as an update for Midwest ( MW UPDATE ). Save all 4 of these spreadsheets to a folder locally on your PC. Saving on the cloud may make this more difficult. The company operates a few stores of different sizes, and likes to have inventory based on store size. 1. Starting with the NorthEast , use the Summary sheet to get a good idea of the current state of inventory in the stores. In the Total column C, beginning in cell C4, use a 3D formula to SUM each category across states ’ sheets in the region. For example, SUM cell C4 in NJ through C4 in WV to get the square footage for the entire region. Fill that formula for each row in column C — remember to use shift while selecting through the worksheets to allow for a reference through worksheets. 2. On the NorthEast workbook Summary sheet, view the formulas in D4 through H17. With =INDIRECT, we can reference a cell that has sheet or file information in it, to direct us to a cell or a sheet. This is a powerful formula! Pretty cool stuff. 3. Create a copy of the Summary sheet into both the Southeast and Midwest workbooks. You will have to make a few modifications. Ensure you have the correct 3D formula in column C to sum each category (sheet names have changed!). Also, you will have to rename the column headers in row 3 to align with the new state names (new sheets!), otherwise the =INDIRECT formula will not work. 4. The ordering manager for the Northeast is a University of Wisconsin alumnus – because of this, you suspect some of the product ordering has been incorrect (again). To determine if the Badger alum is messing up, create a copy of the Summary worksheet in the NE workbook and name this new sheet ‘SqFt to Item’. Hang with me on this step! a. Modify your formulas in the D5:H17 to be a ratio of square footage to item. Cell D5 should have the formula =D$4/INDIRECT(D$3&”!”&$A5). Square feet divided by number of items. Consider what this is representing. For example, a 10,000 square foot store might want 100 barstools, whereas a 30,000 square foot store might want 300. Both would be 100:1 ratios. b. Bigger stores need more inventory, and smaller stores need less. The numbers you have now indicate the number of square feet there are in a store for each item. c. Once you have written the formula, you will notice there is some variability, so find 2-3 of the worst instances of inventory management in the Northeast. Are there too many or too few barstools? Highlight and fill the cells yellow to indicate the state and category that has too much or too little inventory. 5. On the NorthEast worksheet named “Linked Inventory” , create links to each of the summary pages for the three regions. Link the summary totals from column C of each summary page, starting with the square footage cell. A link is created exactly how you would reference another cell: just start with = and select the cell from the other workbook. You will make active workbook links to other Excel files, as well as the summary worksheet from this file. You will need to change from an absolute reference to a relative one to drag the formulas down.