Key Topics & Core Concepts

Assignment Overview & Deliverables

Blue Lake Sporting Goods is evaluating solar panel energy savings, categorizing warehouse inventory, analyzing third-quarter sales deviations, and measuring warehouse order fulfillment durations. You will apply financial functions, multi-case logical switching, modern dynamic array formulas, and time arithmetic across multiple sheets.

Step-by-Step Instructions

Step 1: Open Starter & Determine Future Value of Savings

  1. Open BlueLake-06.xlsx and save it as [your initials] BlueLakeSportingGoods.xlsx.
  2. Navigate to the Solar Install worksheet tab and select cell C8.
  3. Enter a =FV(...) formula to calculate the future value of the expected energy savings:
    • Rate: Divide the annual rate in cell C5 by 12 (monthly rate).
    • Nper: Multiply the payback years in cell C6 by 12 (total monthly periods).
    • Pmt: Select cell C7 (expected monthly savings entered as a cash outflow).
  4. Confirm the calculated future value evaluates to approximately $87,213.

Step 2: Assign Department Codes with SWITCH

  1. Switch to the Inventory worksheet tab and select cell B5.
  2. Build a SWITCH function referencing cell C5 as the expression, mapping each department code from the key in columns G:H using absolute references:
    • Match $G$5 ("Apparel") → $H$5 ("AP")
    • Match $G$6 ("Bike & Skate") → $H$6 ("BSK")
    • Match $G$7 ("Golf") → $H$7 ("GL")
    • Match $G$8 ("Winter Sports") → $H$8 ("WS")
    • Default value: "No match"
  3. Copy the formula down through row 19, preserving the existing cell borders.
  4. Increase the indent one time for the newly populated department codes in column B.
  5. In cell C14, type Running (verify that cell B14 displays No match).
  6. Select column C, right-click, and choose Hide. Change the font size of cell A1 to 14 pt.

Step 3: Calculate Variance, Standard Deviation & Sales Rank

  1. Switch to the ThirdQtr worksheet tab and select cell F6.
  2. Build a formula to calculate the absolute difference between estimated sales (D6) and actual sales (E6) using the ABS function:
    =ABS(E6-D6)
  3. Copy the formula down through row 28.
  4. In cell F30, calculate the population standard deviation of the sales variances using =STDEV.P(F6:F28).
  5. In cell F32, use RANK.EQ to determine the ranking of actual sales for the Louisville Slugger (cell E24) against all product actual sales:
    =RANK.EQ(E24, $E$6:$E$28)

Step 4: Extract Data with FILTER and Nested SORT(UNIQUE)

  1. On the ThirdQtr sheet, select cell J5 and type winter sports.
  2. In cell I6, enter a FILTER function to dynamically extract product details matching the criteria:
    =FILTER(C6:E28, B6:B28=J5)
  3. In cell B15, change the department to Winter Sports (type wi and press Enter) and observe the spill range update automatically to 5 items.
  4. In cell M6, extract an alphabetized list of unique departments by nesting SORT and UNIQUE:
    =SORT(UNIQUE(B6:B28))

Step 5: Model 24-Hour Processing Times & Midnight Transitions

  1. Switch to the OrderTimes worksheet tab.
  2. Select cell E5. Build an =IF(...) formula to calculate elapsed order processing time across midnight:
    =IF(D5<C5, D5+1, D5)-C5
  3. Format cell E5 as Time > 13:30 (24-hour time format without AM/PM).
  4. Use the Evaluate Formula tool on the Formulas tab to trace the logic on cell E5 and row 8 (where processed time occurs past midnight, causing D8<C8 to evaluate to TRUE and add 1 full day).
  5. Copy the formula from cell E5 down through row 33. Reapply cell borders as needed to keep formatting clean.
  6. Save the completed workbook as [your initials] BlueLakeSportingGoods.xlsx.
  7. Run an optional audit via the pre-flight portal at /teaching/ISMG-2050/submit.html, then upload your final workbook directly to Canvas before the cutoff.

Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated