File Requirements
- Starter File: Download 📥 starter_data_wk3.xlsx
- Target Worksheet: Complete all work in the
CostAnalysissheet. - Output: Save the completed workbook as
[your_initials]Practice04.xlsx.
Lab Overview
In this exercise, you'll calculate budget vs. actual variance for a departmental ledger spanning 360+ line items. You'll write formulas that subtract Actual from Budgeted values, propagate them across every row of data, and summarize the results with SUM and AVERAGE. No Flash Fill or absolute references — just clean relative referencing.
Step-by-Step Instructions
Step 1: Open and Save As
- Open
starter_data_wk3.xlsxand click Enable Editing. - Save file as
[your_initials]Practice04.xlsx. - Navigate to the
CostAnalysisworksheet.
Step 2: Write the Variance Formula in F1
- Click cell
F1. The header should read Variance ($). - In cell
F2, enter the formula:=D2-E2 - This subtracts Budgeted (col D) from Actual (col E) for the first data row.
Step 3: Propagate Across All Data Rows
- After entering the formula in
F2, use the fill handle (small square at the cell's bottom-right corner) to drag down to row361. - Alternatively, select
F2:F361, then press Ctrl + D (Fill Down). - Every row from 2 through 361 should now show a variance value.
Step 4: Add Summary Calculations
- In cell
F362, enter the formula:=SUM(F2:F361) - In cell
F363, enter the formula:=AVERAGE(F2:F361) - These summarize total and average variance across all departments.
Step 5: Finalize and Save
- Reset active selection to cell
A1(Ctrl + Home). - Save workbook and verify calculations against the autograder.
Key Skills Practiced
- Variance calculation (Budgeted − Actual) using relative references
- Formula propagation across 360+ rows via fill handle or Ctrl+D
- SUM and AVERAGE aggregation over a data range
Refer to Canvas for official submission cutoff and penalty policies.