Assignment Overview

Duration: 6 hours (mostly outside class; lab time is for checkpoints)
Total Marks: 30 points
Type: Individual practical work
Submission: One Excel workbook plus a short reflection note

Sample data: Download sales_data_large.csv and employee_payroll.csv.

Learning Outcomes

By completing this assignment, you will demonstrate your ability to:

  • Manage and clean tabular data (sort, filter, validation, freeze panes)
  • Build formulas with relative, absolute, and mixed references
  • Use logical, lookup, statistical, financial, and date functions
  • Create pivot tables, charts, and a summary dashboard
  • Link worksheets (and optionally workbooks) for reporting

Part A: Data Management and Formatting (8 points)

Task 1: Import and Format Sales Data (4 points)

  1. Create a new workbook. Save as PRC110_Unit3_[YourRollNo].xlsx.
  2. Import sales_data_large.csv into a sheet named Sales (Data → From Text/CSV, or paste).
  3. Ensure headers are in row 1 and the data is a continuous table (no blank rows).
  4. Apply formatting:
    • Header row: bold, dark blue fill, white font, AutoFilter on
    • Currency columns (Sales Amount): Number format with thousands separator and 2 decimals, or Accounting (NPR)
    • Freeze the top row
    • AutoFit column widths
  5. Add a new column Unit Price with formula = [@Sales Amount] / [@Units Sold] (or =D2/E2 filled down). Guard against divide-by-zero with =IF(E2=0,"",D2/E2).

Evidence: Sales sheet correctly formatted with Unit Price formulas.

Task 2: Sort, Filter, Validation, Text to Columns (4 points)

  1. Sort Sales by Region (A→Z), then Sales Amount (largest to smallest).
  2. Create a sheet named Cleaned. Copy the Sales table there.
  3. On Cleaned, insert a helper column that splits a combined field:
    • Insert column A titled RegionCode. Temporarily create values like North-N01 in a scratch column if needed, or use Text to Columns on a sample “City-Code” column you add with 10 example values (e.g. Pokhara-PKR).
  4. Apply Data Validation on the Region column: list North,South,East,West.
  5. Apply conditional formatting colour scale on Sales Amount.
  6. Set Print Titles so row 1 repeats on each printed page (Page Layout → Print Titles).

Evidence: Cleaned sheet with validation, conditional formatting, and print titles set.

Part B: Cell References and Core Formulas (7 points)

Task 3: Pricing Sheet with Absolute References (4 points)

  1. Create sheet Pricing.
  2. In H1 enter tax rate 0.13 and label G1 as VAT Rate.
  3. Build a small product table (at least 6 products) with columns: Product, Units, Unit Price (NPR), Line Total, VAT, Grand Total.
  4. Formulas:
    • Line Total: relative =B2*C2
    • VAT: =D2*$H$1 (absolute tax rate)
    • Grand Total: =D2+E2
  5. Add summary cells using SUM, AVERAGE, MAX, MIN for Line Total and Grand Total.

Evidence: Pricing sheet showing absolute $H$1 usage in the formula bar.

Task 4: Mixed References Mini-Matrix (3 points)

  1. On Pricing, create a 4×4 commission matrix (rows = products A–D, columns = rates 5%, 8%, 10%, 12%).
  2. Store rates in row 20 (B20:E20) and product bases in column A21:A24.
  3. In B21 use a mixed reference formula such as =$A21*B$20 and fill across/down.

Evidence: Matrix filled correctly; formula uses mixed references.

Part C: Essential Functions (10 points)

Use sheet Employees imported from employee_payroll.csv (or recreate the same columns).

Task 5: Logical and Conditional Aggregates (4 points)

  1. Add column Gross = Basic + Allowance.
  2. Add column Tax = Gross × absolute tax rate cell (reuse 0.13 or a local tax cell).
  3. Add column Net = Gross − Tax.
  4. Add column Status with nested IF:
    • Net > 45000 → "High"
    • Net > 25000 → "Medium"
    • else "Low"
  5. Use COUNTIF / SUMIF / AVERAGEIF to report counts and totals by Status and by Department.

Task 6: Lookup and Financial/Date Functions (6 points)

  1. Build a lookup panel:
    • Cell B30: employee name typed by you
    • C30: VLOOKUP returning Net
    • D30: INDEX/MATCH returning Department (verify both approaches)
  2. EMI calculator block:
    • Loan amount, annual rate, tenure (months)
    • =PMT(rate/12, nper, -pv) labelled clearly
  3. Date block:
    • Join date column (or add one)
    • Years of service with =DATEDIF(join_date, TODAY(), "Y")
  4. Optional stretch (bonus, not required for full marks): SIN/COS demo cell documenting a simple angle conversion for syllabus familiarity.

Evidence: Working VLOOKUP, INDEX/MATCH, PMT, and DATEDIF outputs.

Part D: Pivot Tables, Charts, and Linking (5 points)

Task 7: Pivot + Chart (3 points)

  1. From Sales, create a Pivot Table on sheet Pivot:
    • Rows: Region (and optionally Product)
    • Values: Sum of Sales Amount, Sum of Units Sold
  2. Insert a Pivot Chart (clustered column or pie).
  3. Add a Slicer for Region (or Product) and demonstrate a filtered view.

Task 8: Cross-Sheet Summary (2 points)

  1. Create sheet Dashboard.
  2. Pull key totals from Employees and Pivot using worksheet links (e.g. =Employees!G12, =Pivot!B4).
  3. Add one chart on Dashboard summarising regional sales.
  4. Write 4–6 bullet insights in a text box or adjacent cells.

Deliverable workbook: PRC110_Unit3_[YourRollNo].xlsx containing Sales, Cleaned, Pricing, Employees, Pivot, Dashboard.

Also submit PRC110_Unit3_Reflection_[YourRollNo].docx (½–1 page): which functions were hardest, one error you fixed, and how Excel helps BBA coursework.

Submission Guidelines

File Organization

PRC110_Unit3_[YourName]/
├── PRC110_Unit3_[YourRollNo].xlsx
└── PRC110_Unit3_Reflection_[YourRollNo].docx

Submission Method

Submit on Google Classroom by the deadline set by your faculty. Late work may attract grade deductions.

Grading Rubric

Component Points Criteria
Part A: Data management & formatting 8 Import, format, freeze, validation, conditional format, print titles
Part B: References & core formulas 7 Relative, absolute, mixed references correct
Part C: Essential functions 10 Nested IF, aggregates, VLOOKUP, INDEX/MATCH, PMT, DATEDIF
Part D: Pivot, chart, linking 5 Pivot + chart + slicer + Dashboard links + insights
Total 30  

Tips for Success

  1. Keep one clean header row; never merge header cells before pivoting.
  2. Press F4 to cycle $ locks while editing formulas.
  3. After changing source data, right-click the pivot → Refresh.
  4. Name sheets clearly; avoid spaces in names you will reference often.
  5. Save early and keep a backup copy.

Common Mistakes to Avoid

  1. Using relative references for tax/VAT rates
  2. Filtering the wrong range (missing rows)
  3. Building pivots from incomplete selections
  4. Leaving #N/A lookup errors without checking exact name spelling
  5. Submitting CSV files instead of the .xlsx workbook

📚 Related Notes: