Build the real method-comparison workbook and verify all four algorithms
Build the Multi-Sheet Inventory Method Workbook
Open the student workbook and build method logic sheet-by-sheet. This sprint is assessed on algorithm correctness and method-switch outputs, not formatting polish.
Download unit07-lesson05-student.xlsx
Workbook tabs: Inputs, FIFO, LIFO, SpecificID, WeightedAverage, Outputs. Build each tab in order.
Formulas are shown as text so you can read the structure clearly before building in Excel.
Set shared totals once. Every method reads from these values.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Field | Value | Formula / Source | Purpose | Check |
| 2 | UnitsSold | 15 | =SUM(Sales[Qty]) | Shared sold units | Matches Sales table |
| 3 | TotalUnits | 40 | =SUM(Purchases[Qty]) | Shared available units | Matches Purchases table |
| 4 | GAFS | 800 | =SUM(Purchases[LotTotal]) | Total available cost | Used by every method |
| 5 | SelectedMethod | FIFO | Data Validation List | Drives output display | FIFO/LIFO/Specific ID/WA |
| 6 | Lot L1 | 10 @ 18 | =Qty*UnitCost | Purchases row | 180 |
| 7 | Lot L2 | 20 @ 20 | =Qty*UnitCost | Purchases row | 400 |
| 8 | Lot L3 | 10 @ 22 | =Qty*UnitCost | Purchases row | 220 |
| 9 | |||||
| 10 |
Oldest-to-newest consume logic with FIFO helper columns.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | LotID | Qty | UnitCost | FIFO CumQty | FIFO Used | FIFO Cost |
| 2 | L1 | 10 | 18 | =SUM($B$2:B2) | =MAX(0,MIN(B2,UnitsSold-(D2-B2))) | =E2*C2 |
| 3 | L2 | 20 | 20 | =SUM($B$2:B3) | =MAX(0,MIN(B3,UnitsSold-(D3-B3))) | =E3*C3 |
| 4 | L3 | 10 | 22 | =SUM($B$2:B4) | =MAX(0,MIN(B4,UnitsSold-(D4-B4))) | =E4*C4 |
| 5 | ||||||
| 6 | FIFO COGS | 280 | =SUM(FIFO_Cost_Column) | FIFO EI | 520 | =GAFS-FIFO_COGS |
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 |
Same consume formula as FIFO, but lots are reversed first.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | LotID (Newest→Oldest) | Qty | UnitCost | LIFO CumQty | LIFO Used | LIFO Cost |
| 2 | L3 | 10 | 22 | =SUM($B$2:B2) | =MAX(0,MIN(B2,UnitsSold-(D2-B2))) | =E2*C2 |
| 3 | L2 | 20 | 20 | =SUM($B$2:B3) | =MAX(0,MIN(B3,UnitsSold-(D3-B3))) | =E3*C3 |
| 4 | L1 | 10 | 18 | =SUM($B$2:B4) | =MAX(0,MIN(B4,UnitsSold-(D4-B4))) | =E4*C4 |
| 5 | ||||||
| 6 | LIFO COGS | 320 | =SUM(LIFO_Cost_Column) | LIFO EI | 480 | =GAFS-LIFO_COGS |
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 |
Sales lot tags drive exact cost lookups.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sale Row | LotID | Qty | Lookup Cost | Line Cost | Meaning |
| 2 | 1 | L1 | 5 | =XLOOKUP(B2,Purchases[LotID],Purchases[UnitCost]) | =C2*D2 | 5 units from lot L1 |
| 3 | 2 | L3 | 10 | =XLOOKUP(B3,Purchases[LotID],Purchases[UnitCost]) | =C3*D3 | 10 units from lot L3 |
| 4 | ||||||
| 5 | Specific ID COGS | 310 | =SUM(Line_Cost_Column) | Specific ID EI | 490 | =GAFS-SpecificID_COGS |
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 |
One blended period rate drives both COGS and ending inventory.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Metric | Value | Formula | Meaning | Check |
| 2 | WA Rate | 20 | =GAFS/TotalUnits | Blended period cost per unit | 800/40 = 20 |
| 3 | WA COGS | 300 | =UnitsSold*WA_Rate | Cost assigned to sold units | 15*20 = 300 |
| 4 | WA EI | 500 | =(TotalUnits-UnitsSold)*WA_Rate | Cost assigned to unsold units | 25*20 = 500 |
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
Summary table plus selector lookups displays the chosen method.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Method | COGS | Ending Inventory | Balance Check | Display Logic |
| 2 | FIFO | 280 | 520 | =B2+C2 | Summary row |
| 3 | LIFO | 320 | 480 | =B3+C3 | Summary row |
| 4 | Specific ID | 310 | 490 | =B4+C4 | Summary row |
| 5 | Weighted Average | 300 | 500 | =B5+C5 | Summary row |
| 6 | |||||
| 7 | Display COGS | =XLOOKUP(SelectedMethod,A2:A5,B2:B5) | Display EI | =XLOOKUP(SelectedMethod,A2:A5,C2:C5) | Selector output panel |
| 8 | |||||
| 9 | |||||
| 10 |
Block 1: Inputs Tab
- Confirm Purchases and Sales tables are complete.
- Calculate UnitsSold, TotalUnits, and GAFS.
- Create selector dropdown with FIFO, LIFO, Specific ID, Weighted Average.
Checkpoint: shared totals match your source rows.
Block 2: FIFO + LIFO Tabs
- Build FIFO cumulative, used units, and cost columns (oldest-to-newest).
- Build LIFO helper table in reversed lot order and apply same consume pattern.
Checkpoint: FIFO COGS 280 / EI 520 and LIFO COGS 320 / EI 480 (sample data).
Block 3: SpecificID + WeightedAverage Tabs
- Specific ID: lookup lot cost by Sales[LotID], compute line costs, sum COGS.
- Weighted Average: compute WA rate, WA COGS, and WA ending inventory.
Checkpoint: Specific ID COGS 310 / EI 490 and WA COGS 300 / EI 500 (sample data).
Block 4: Outputs Tab + Method Switch
- Create one summary row per method with COGS and ending inventory.
- Use selector-driven lookups to display current method outputs.
- Add method-level balance checks where COGS + EI = GAFS.
Checkpoint: selector updates display values without editing formulas.
- Inputs, FIFO, LIFO, SpecificID, WeightedAverage, and Outputs tabs are complete.
- All four methods return correct COGS and ending inventory from the same data.
- Output selector displays method-specific COGS and ending inventory correctly.
- Each method passes COGS + Ending Inventory = GAFS.
- You can explain each tab's algorithm in plain language.
- Use clear names and structured references.
- Add validation and error handling as part of normal workbook quality.
- Keep each tab readable for fast audit and handoff.
If your workbook breaks, compare tab structure with the teacher reference: