EXERCISE 1: MS Excel – Business Management
Scenario
You are the Business Operations Manager of a medium-scale manufacturing company with three
branches (Douala, Buea, Yaoundé). Management wants a dynamic decision-support workbook to
analyze performance, profitability, and future projections.
Tasks
1. Data Structure
o Sheet 1: Sales_Data
Date
Branch
Product Category
Units Sold
Unit Price
Cost per Unit
Sales Representative
2. Advanced Calculations
o Calculate:
Total Revenue
Total Cost
Gross Profit
Profit Margin (%)
o Use structured references (Excel Tables).
3. Decision Dashboard
o Design a Management Dashboard containing:
KPI Cards: Total Revenue, Total Profit, Average Profit Margin
Column chart comparing branch performance
Trend line for monthly revenue
o Apply Conditional Formatting to highlight:
Profit margins below 15% (red)
Profit margins above 30% (green)
EXERCISE 2: Advanced MS Excel – Human Resources Management
Scenario
You are the HR Analytics Officer of an organization with 120 employees. Management needs an HR
performance, payroll, and compliance analysis system.
Tasks
1. Employee Database
o Sheet: Employee_Records
Employee ID
Name
Department
Job Grade
Basic Salary
Allowances
Overtime Hours
Employment Date
Performance Score
2. Advanced Formulas
o Calculate:
Gross Salary
Deductions (Tax based on salary bands)
Net Salary
o Use:
IF, IFS, VLOOKUP/XLOOKUP
3. HR Performance Analysis
o Automatically classify employees as:
Outstanding
Satisfactory
Needs Improvement
o Based on Performance Score using nested formulas.
4. Payroll Summary
o Create a payroll summary per department using:
SUMIFS
COUNTIFS
o Highlight payroll cost overruns using Conditional Formatting.
EXERCISE 3: MS Word – Professional Document Production
Scenario
You are assigned to prepare a formal Annual Organizational Report for an international partner.
The document must meet professional publishing standards.
Tasks
1. Document Structure
o Cover Page (no header/footer)
o Table of Contents (automatic)
o List of Tables & Figures
o Executive Summary
o Main Report Sections
o Appendices
2. Advanced Formatting
o Use Styles consistently (Heading 1–3).
o Apply Section Breaks:
Roman numerals for front matter
Arabic numerals for main content
o Insert headers and footers with:
Organization name
Page numbers aligned differently per section.
3. Tables & Graphics
o Insert complex tables with merged cells.
o Apply professional table styles.
o Insert charts linked from Excel.
4. Finalization
o Generate the TOC automatically.
o Restrict editing except for form fields.
o Export a print-ready PDF.
Excel – Business Management (DATASET)
Sheet Name: Sales_Data
Date Branch Product Category Units Sold Unit Price Cost per Unit Sales Rep
05/01/2025 Douala Beverages 450 800 500 Nkongho
06/01/2025 Buea Snacks 320 600 350 Linda
07/01/2025 Yaoundé Beverages 510 800 500 Patrick
10/01/2025 Douala Confectionery 280 1000 650 Elvis
12/01/2025 Buea Snacks 390 600 350 Linda
15/02/2025 Yaoundé Confectionery 460 1000 650 Patrick
18/02/2025 Douala Beverages 520 800 500 Nkongho
22/02/2025 Buea Confectionery 310 1000 650 Elvis
Add calculated columns:
Total Revenue/sales
Total Cost
Gross Profit
Profit Margin (%)
Excel – Human Resources Management (DATASET)
Sheet Name: Employee_Records
Emp Name Departmen Job Basic Allowanc Overtim Employmen Performanc
ID t Grad Salary e e Hrs t Date e
e
HR00 Mbah Finance G5 250,00 40,000 10 15/03/2018 85
1 Alice 0
HR00 Tita IT G6 300,00 60,000 18 02/06/2016 92
2 John 0
HR00 Fonge Admin G4 180,00 25,000 5 10/09/2019 70
3 Mary 0
HR00 Ngala HR G5 240,00 35,000 8 01/02/2015 65
4 Peter 0
HR00 Ewan Finance G6 310,00 70,000 20 20/11/2014 95
5 e 0
Ruth
HR00 Tata IT G4 190,00 30,000 12 18/07/2020 78
6 Bruno 0
Tax Table (Separate Sheet)
Salary Range Tax Rate
≤ 200,000 5%
200,001–300,000 10%
> 300,000 15%
Calculate:
Gross Salary
Tax Deduction
Net Salary
Years of Service
Performance Category
MS Word – Annual Report Content (STRUCTURE)
Document Title
2025 Annual Organizational Performance Report
Executive Summary (Sample Text)
This report presents an overview of organizational performance for the year 2025, highlighting key
achievements, financial performance, human resource development, and strategic priorities for the
coming year.
Main Sections
1. Organizational Overview
Mission
Vision
Strategic Objectives
2. Program Implementation
Insert a table with merged cells:
Program Area Objective Key Activities Outcome
Youth Empowerment Skills development Trainings, Mentorship Improved employability
Gender Advocacy Awareness raising Campaigns, Dialogues Increased participation
3. Financial Performance
Insert a linked Excel chart
Caption: Figure 1: Revenue and Expenditure Analysis
4. Human Resources Summary
Insert a styled table showing staff distribution by department. (Organizational Chart)
Appendix A
Policies
Supporting documents
Appendix B
Attendance sheets
Financial statements