← Back to ISMG-2050 Course Hub

Project Overview

Build a four-year college cost projection model from scratch. This project integrates absolute cell referencing, multi-sheet formulas, conditional formatting with threshold alerts, and professional page setup for printing.

Sheet 1: Cost Breakdown (Tab named "Costs")

1. A1: type Tuition per Year
2. B3:B6: Enter the following annual tuition figures:
   • B3: $12,500 (Freshman)
   • B4: $13,100 (Sophomore)
   • B5: $13,750 (Junior)
   • B6: $14,400 (Senior)
3. A8: type Room & Board per Year
4. C3:C6: Enter the following annual room and board figures:
   • C3: $9,200
   • C4: $9,500
   • C5: $9,800
   • C6: $10,100
5. A10: type Books & Supplies per Year
6. D3:D6: Enter the following annual book costs:
   • D3: $1,200
   • D4: $1,350
   • D5: $1,200
   • D6: $1,100
7. A12: type Total Annual Cost
8. B12: Enter formula: =B3+C3+D3 (Freshman total)
9. Copy the formula down to C12:D12. Then copy across to E12, F12, G12 for Sophomore through Senior totals.
10. A14: type Total Four-Year Cost
11. B14: Enter: =SUM(B12:E12)

Sheet 2: Tuition Growth Analysis (Tab named "Growth")

13. A1: type Year, B1: type Tuition, C1: type Change, D1: type % Change
14. A3:A6: Freshman, Sophomore, Junior, Senior
15. B3:B6: Reference Costs sheet: =Costs!$B$3, copy down to B4:B6 (absolute $ references)
16. C4:C6: Tuition change from prior year: =B4-B3, then copy down through C6
17. D4:D6: Percent change: =C4/B3. Format as Percentage (2 decimal places).
18. B8: type Average Tuition, C8: =AVERAGE(B3:B6)
19. B9: type Highest Tuition, C9: =MAX(B3:B6)
20. B10: type Lowest Tuition, C10: =MIN(B3:B6)

Conditional Formatting \u2014 Threshold Alerts

19. On the "Costs" sheet, select range B12:E12.
20. Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
21. Enter 24000 and choose Light Red Fill with Dark Red Text.
22. This highlights any year where total annual cost exceeds $24,000.
23. On the "Growth" sheet, select range D4:D6.
24. Apply a Data Bar conditional format (Gradual Green Fill) to visualize percent change visually.

Page Setup & Printing

21. Select the "Costs" sheet.
22. Page Layout > Orientation > Landscape.
23. Print Area > Set Print Area for range A1:E14.
24. Scale to Fit: Width = 1 page, Height = Automatic.
25. Insert the formula =$A$1 & " - College Cost Projection" into the Center Header field on the Page Layout tab.
26. Switch to the "Growth" sheet, set to Portrait, and set Print Area to A1:D10.
27. Verify both sheets preview correctly before saving.

Submission Criteria

• File named exactly [your initials]CollegeCost.xlsx
• Two worksheets: "Costs" and "Growth"
• All formulas correct and producing expected numeric output
• Conditional formatting applied on both sheets (threshold alert + data bars)
• Both sheets configured with correct orientation, print area, and headers
• AI Disclosure statement included in a note below the Costs sheet: "AI Disclosure: [Used/None]"

→ Back to Week 3\u20134 Homework Portal