Key Topics & Core Concepts

Assignment Overview & Deliverables

Central Sierra Insurance calculates agent bonus percentages based on commissions earned and aggregates totals by branch office. You will establish named ranges, construct lookup and logical functions, apply conditional summary formulas, and format the financial ledger for management review.

Step-by-Step Instructions

Step 1: Open and Save Starter File

  1. Download and open CentralSierra-02.xlsx in Excel. Click Enable Editing if prompted.
  2. Save the workbook as [your initials]CentralSierraIns.xlsx using File > Save As.
  3. Review the data structure across the Commissions, By Policy, and Tables worksheet tabs.

Step 2: Create Defined Named Ranges

  1. Navigate to the Tables worksheet.
  2. Select the cell range A1:B6.
  3. On the Formulas tab, in the Defined Names group, click Create from Selection.
  4. Ensure Top row is checked and click OK to generate the Commission_Level and Bonus_Rate ranges.

Step 3: Construct Lookup and Base Compensation Formulas

  1. Switch to the Commissions worksheet.
  2. In cell F5, create a lookup formula to determine the agent's bonus rate based on earned commissions in cell E5:
    =XLOOKUP(E5, Commission_Level, Bonus_Rate, , -1)
    (Alternatively, use: =VLOOKUP(E5, Tables!$A$2:$B$6, 2, TRUE)).
  3. In cell G5, calculate the base bonus dollar amount by multiplying earned commission by the bonus rate:
    =E5*F5.
  4. Copy both formulas down through row 23 using the Fill Handle.

Step 4: Implement Logical Bonus Tests & Totals

  1. In cell H5, build an =IF(...) function to evaluate bonus qualification thresholds.
  2. In cell I5, calculate Total Compensation by summing Base Bonus and Additional Bonus:
    =G5+H5 or =SUM(G5:H5).
  3. Replicate both formulas down the remaining rows of the table.

Step 5: Pre-Flight Self-Check & Canvas Submission

  1. Apply standard Accounting format with currency symbols to top rows and summary total rows. Apply Comma Style to intermediate rows.
  2. Audit all calculations to ensure there are no formula error flags (e.g., #N/A, #REF!, #VALUE!).
  3. Test your workbook in the autograder pre-flight portal at /teaching/ISMG-2050/submit.html.
  4. Upload your finalized [your initials]CentralSierraIns.xlsx file directly to Canvas before the cutoff.

Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated