Lesson 1, Topic 1
In Progress

Week 1 – Build

Week 1 — Build | Multi-Workbook Budget Roll-up | MO-211 Expert
Week 1 of 9 · Build · Framework Summative

Build — Multi-Workbook Budget Roll-up

The Project — Multi-Workbook Budget Roll-up

📋 Project Brief

Four department managers maintain their own budget workbooks. You're the analyst building a master roll-up that aggregates all four into a single dashboard — without breaking the source files, and without manually typing any numbers.

The master workbook must:

  • Pull each department's monthly budget from the corresponding source workbook using external references (one cell per department × 12 months = at minimum 48 linked cells; you can scale up)
  • Total each department's annual budget using a normal in-workbook SUM formula
  • Total the company-wide annual budget across all four departments
  • Include a Dashboard sheet that summarizes department totals in a small results table — built entirely from references, no typed numbers
  • Pass a break-and-repair drill: when you rename one source file, Edit Links should detect it and you should fix it with Change Source

Plus the workbook itself must have:

  • Workbook Properties set (Title + Author) — carryover habit from Associate
  • Sheet tabs renamed and colored
  • Freeze Top Row on every data sheet
  • An Audit sheet that lists each external reference (which cell, which source, which sheet, which source cell)

Final output: Submit the .xlsx and an exported .pdf (Entire workbook, all sheets as pages).

Required Deliverables — Three Files

Week 1 has three required deliverables across the three chunks. Submit each one to its own LMS assignment.

Deliverable 1 (from Practice)

Linking Lab

The five-exercise lab workbook with Observations, Edit Links Notes, and Trust Center Notes.

Filename: lastname-firstname_week01_linking-lab.xlsx

Deliverable 2 (from Build)

Budget Roll-up Master

The multi-workbook master with Dashboard, Detail, Audit sheets. Plus an exported PDF.

Filenames:
lastname-firstname_week01_budget-rollup-master.xlsx
lastname-firstname_week01_budget-rollup-master.pdf

Deliverable 3 (from Wrap)

Reflection

150–250 word written reflection on the multi-workbook design experience.

Filename: lastname-firstname_week01_reflection.xlsx

Starter Files (Provided by Your Teacher)

Your teacher provides four department budget workbooks. Each has the same structure: a Budget sheet with twelve monthly amounts in cells B5 through B16, plus a department name in cell B2. Do not modify these source files — they're shared department data.

FilenameDepartmentWhat's Inside
budget-marketing.xlsxMarketingBudget sheet: B5:B16 = monthly amounts (Jan–Dec)
budget-engineering.xlsxEngineeringSame structure, different numbers
budget-sales.xlsxSalesSame structure, different numbers
budget-operations.xlsxOperationsSame structure, different numbers
⚠️ Save all five files (your master + the four sources) in the same folder. External references work with any folder structure, but keeping them together for Week 1 reduces broken-link risk. We'll explore split folder structures in Week 3.

Step-by-Step — Build the Roll-up

Phase A — Set Up the Master Workbook
  1. Create a new blank workbook. Save immediately as lastname-firstname_week01_budget-rollup-master.xlsx in the same folder as the four department files.
  2. Set Workbook Properties (File → Info → Properties → Show All Properties). Title: "Multi-Workbook Budget Roll-up — [Your Name]"; Author: your name. Same habit as always.
  3. Add three more worksheets (you'll have Sheet1 + 3 more = 4 sheets). Rename and color:
    Dashboard Detail Audit Notes
  4. Open all four source workbooks (File → Open). You'll need them open while you build the external references — point-and-click is much faster than typing.
Phase B — Build the Detail Sheet (Where Every Link Lives)
  1. Click the Detail tab. A1: "Department Budgets — Monthly Detail" (bold, font size 16).
  2. Build the structure in rows 3–4:
    • A3: blank (will hold "Month"); B3: "Marketing"; C3: "Engineering"; D3: "Sales"; E3: "Operations"; F3: "Total"
    • A4:A15: type the twelve month names (January through December)
    Bold row 3 with a fill color. Freeze Top Row.
  3. In cell B4 (Marketing, January), type =. Switch to budget-marketing.xlsx (View → Switch Windows). Click Budget!B5. Press Enter. Cell B4 now contains a Marketing external reference.
  4. Click B4 again and look at the Formula Bar — confirm it shows ='[budget-marketing.xlsx]Budget'!$B$5. Pro habit from the Lesson: verify every link by inspecting the Formula Bar.
  5. Now the tricky part — making the formula copyable down the column. Click B4. In the Formula Bar, change $B$5 to $B5 (remove the dollar sign in front of 5 — the row number should be relative). Press Enter. Now the row can shift when copied.
  6. Copy B4 down to B15. Click B4, hover the small green square at the cell's bottom-right corner, drag down to B15. (Or Ctrl+C on B4, select B5:B15, Ctrl+V.) Each cell should now reference a different row in the source — B5 references Budget!$B6, B6 references Budget!$B7, etc.
  7. Repeat steps 7–10 for columns C, D, and E, pointing each at the matching source (Engineering, Sales, Operations). Each column should end up with 12 external references pulling sequential months.
  8. In F4, build a normal SUM: =SUM(B4:E4). This is an in-workbook formula — no external reference, just a regular row total. Copy down to F15.
  9. In row 16, build a column SUM for each department: B16 = =SUM(B4:B15). Copy across to F16. This row is each department's annual total + the grand company total in F16.
  10. Bold row 16 (the totals) and add a top border (Home → Font → Borders → Top Border). Visual separator.
📌 Why the dollar-sign change matters. When you point-and-click an external reference, Excel writes it as fully absolute by default: $B$5. That works for one cell. For a column of formulas pointing at sequential rows, you need the row reference to be relative ($B5) so it shifts when you copy down. This is the "F4 toggle" skill — pressing F4 in a formula cycles through absolute/mixed/relative reference forms.
Phase C — Build the Dashboard Sheet
  1. Click the Dashboard tab. A1: "Company Budget Roll-up — Annual Summary" (bold, font size 18, fill color).
  2. Row 3 headers: A3 "Department"; B3 "Annual Budget"; C3 "% of Total". Bold + fill color.
  3. Department rows. Rows 4–7, column A: type "Marketing", "Engineering", "Sales", "Operations". Column B: pull each annual total from your Detail sheet, not from the source files. So B4 should be =Detail!B16, B5 = =Detail!C16, etc.
  4. Total row. A8: "Company Total" (bold). B8: =SUM(B4:B7). This must match Detail!F16 — verify they do.
  5. Percentage column. C4: =B4/$B$8. Note the dollar signs on $B$8 — the denominator stays the same when you copy down. Format the column as Percentage (Home → Number → Percentage). Copy C4 down to C7.
  6. Freeze Top Row.
📌 Why Dashboard pulls from Detail, not directly from sources. One layer of indirection makes the workbook resilient. If a source moves, you fix it on the Detail sheet (where the external references live); the Dashboard automatically updates from Detail. If the Dashboard pulled directly from sources, you'd have twice as many external references to repair when something breaks. This pattern — "links in one place, presentation everywhere else" — scales to dozens of source files.
Phase D — Build the Audit Sheet
  1. Click the Audit tab. A1: "External References — Source Map" (bold, font size 16).
  2. Row 3 headers: A3 "Destination Cell"; B3 "Source File"; C3 "Source Sheet"; D3 "Source Cell". Bold + fill color.
  3. List your sources. Rows 4–7, one per source workbook. Example for row 4: A4 "Detail!B4:B15"; B4 "budget-marketing.xlsx"; C4 "Budget"; D4 "B5:B16". Repeat for Engineering, Sales, Operations.
  4. Freeze Top Row.

The Audit sheet is documentation, not formulas. When someone inherits this workbook in two years, the Audit sheet tells them exactly which external files this workbook depends on. Real workbooks in real workplaces should always have one.

Phase E — The Break-and-Repair Drill
  1. Save and close all five files (master + four sources).
  2. In File Explorer, rename one source file (your choice). For example, rename budget-sales.xlsx to budget-sales-v2.xlsx.
  3. Reopen the master. Choose Update Links when prompted. The Sales column on the Detail sheet should now show #REF! errors; Dashboard's Sales row likely shows 0 or an error.
  4. Open Edit Links (Data → Queries & Connections → Edit Links). The Sales workbook should show "Source not found."
  5. Click Change Source... Navigate to and select the renamed file. Click OK. Status updates to OK; every Sales formula in the workbook is repaired in a single operation.
  6. Save. On the Notes sheet, write one sentence about how it felt to see #REF! appear and then disappear. (No screenshot required, just a record.)
Phase F — Polish & Export
  1. Click each tab and verify it looks right. Frozen rows working? Headers bold and colored? Tab colors visible? Formulas not showing #REF! anywhere?
  2. Press Ctrl+` to enable Show Formulas on the Detail sheet. Verify that every cell in B4:E15 shows an external reference formula (with the [filename.xlsx] visible), not a typed-in value. Press Ctrl+` again to toggle back.
  3. Verify Workbook Properties one last time. File → Info → Properties. Title and Author both filled in correctly?
  4. Save the .xlsx version.
  5. Export as PDF with "Entire workbook" option selected (File → Export → Create PDF/XPS → Options → Entire workbook). All four sheets should appear as pages in the PDF.
  6. Open the PDF and verify all four sheets are present. Dashboard, Detail, Audit, Notes — one page each (Detail may span two pages depending on column widths).
  7. Submit the .xlsx AND the .pdf to the Week 1 — Roll-up LMS assignment.

Bonus Challenges (Optional)

⭐ For Students Who Finish Early

  • Add a stacked bar chart to the Dashboard. Visualize the four department annual totals as a single stacked bar. (Insert → Chart → Stacked Bar.) The chart should update automatically when source values change.
  • Add conditional formatting to the Detail sheet. Highlight any cell in B4:E15 whose value is more than $5,000 above the column average. Home → Conditional Formatting → Highlight Cells Rules.
  • Set Startup Prompt on the master to "Don't display the alert and update." Edit Links → Startup Prompt → choose the no-alert option. Open and close the master to verify it now refreshes silently. Document the behavior change on the Notes sheet.
  • Move the master to OneDrive and turn on AutoSave. Verify Version History accumulates revisions as you edit. Restore an old version to a new file as a backup, then delete the backup.
  • Document the dollar-sign workflow. On the Notes sheet, write a short explainer: when do you want $B$5 (full absolute), $B5 (column-absolute, row-relative), or B5 (fully relative) in an external reference? Use your Detail sheet formulas as examples.

Pre-Submission Checklist

Structural

  • Four worksheets present — Dashboard, Detail, Audit, Notes (in that order)
  • Each sheet tab renamed and colored
  • Top row frozen on every sheet

Linking (the heart of the rubric)

  • Detail!B4:E15 are real external references — verified by Show Formulas (Ctrl+`) showing [filename.xlsx] in each cell
  • Dashboard pulls from Detail, not directly from sources (verify B4 = =Detail!B16 or similar)
  • The break-and-repair drill is complete — Notes sheet has the one-sentence record
  • Audit sheet documents all four sources with filename, sheet, and cell range

Properties & Submission

  • Title and Author set in File → Info → Properties
  • PDF exported with "Entire workbook" — all four sheets present as pages
  • Both files named lastname-firstname_week01_budget-rollup-master.xlsx and .pdf
  • No #REF! errors anywhere in the final version