← Back to ISMG-2050 Course Hub
Comprehensive Project: Cost Analysis Report
Assignment Metadata
- Module: Module 2 (Weeks 3 & 4)
- Due Date:
- Starter Input:
starter_data_wk3.xlsx - Expected Submission:
[your initials]CostAnalysis.xlsx
Project Overview
Analyze multi-category operational costs using a real-world dataset. This project emphasizes absolute references for rate lookup tables, conditional formatting for variance flags, and professional page setup for executive summary reporting.
Sheet 1: Raw Data Import (Tab named "RawData")
- Open starter_data_wk3.xlsx in Excel. You will find the following columns on the first sheet:
- A1: Department, B1: Category, C1: Month, D1: Budgeted ($), E1: Actual ($)
- A2:A30: Department names (Marketing, Engineering, HR, Finance, Operations)
- B2:B30: Cost categories (Salaries, Supplies, Travel, Equipment, Software, Utilities)
- C2:C30: Months (Jan through Dec, repeating per department)
- D2:D30: Budgeted amounts (entered by you \u2014 see Table below)
- E2:E30: Actual amounts (entered by you \u2014 use realistic variances of +/- 5\u201320%)
Budget Reference Table (use as your guide for D column):
| Department | Monthly Budget |
| Marketing | $8,000 |
| Engineering | $25,000 |
| HR | $5,500 |
| Finance | $6,200 |
| Operations | $12,000 |
Sheet 2: Summary Analysis (Tab named "Summary")
- A1: type Department, B1: type Total Budgeted, C1: type Total Actual, D1: type Variance ($), E1: type Variance (%)
- A3:A7: Marketing, Engineering, HR, Finance, Operations (one row per department)
- B3: Enter:
=SUMIF(RawData!$A:$A,A3,RawData!$D:$D)\u2014 copies down to B7 - C3: Enter:
=SUMIF(RawData!$A:$A,A3,RawData!$E:$E)\u2014 copies down to C7 - D3: Enter:
=C3-B3\u2014 copies down to D7 (positive = over budget) - E3: Enter:
=D3/B3\u2014 copies down to E7. Format as Percentage (1 decimal place). - A9: type Total Department Budget, B9:
=SUM(B3:B7) - A10: type Total Department Actual, B10:
=SUM(C3:C7) - A11: type Overall Variance, C11:
=B10-B9
Conditional Formatting \u2014 Variance Flags
- Select range D3:D7 on the Summary sheet.
- Home > Conditional Formatting > Highlight Cells Rules > Greater Than: Enter
0and choose Light Red Fill with Dark Red Text (over-budget flag). - Select range D3:D7 again.
- Home > Conditional Formatting > Highlight Cells Rules > Less Than: Enter
0and choose Light Green Fill with Dark Green Text (under-budget flag). - Select range E3:E7.
- Home > Conditional Formatting > Data Bars > Gradient Fill - Blue to visualize variance magnitude.
Page Setup & Printing
- Select the "Summary" sheet.
- Page Layout > Orientation > Landscape.
- Print Area > Set Print Area for range A1:E11.
- Scale to Fit: Width = 1 page, Height = Automatic.
- In the Center Header, type:
[Company Name] \u2014 Cost Analysis Report - In the Footer, insert Page X of Y via the Page Layout footer dialog.
- Select the "RawData" sheet, set to Landscape, and set Print Area to A1:E30.
- Set scaling to Fit All Columns on One Page.
- Preview both sheets. Verify data fits without clipping.
Submission Criteria
- File named exactly
[your initials]CostAnalysis.xlsx - Two worksheets: "RawData" and "Summary"
- SUMIF formulas correctly aggregating by department
- Variance calculations (both $ and %) accurate
- Conditional formatting applied: red over-budget, green under-budget, blue data bars on % variance
- Landscape orientation with print area and header/footer configured on both sheets
- AI Disclosure statement included in a note below the Summary sheet: "AI Disclosure: [Used/None]"