TUTORIAL 2 – EXCEL PRACTICALS & SPREADSHEET LITERACY
Focus:
Spreadsheet construction • formulas • logic • charts • interpretation
Tools: Microsoft Excel (no calculators)
Instructions to students:
All work must be done in Excel.
Use formulas (no manual calculations).
Clearly label all columns, charts, and outputs.
Screenshots alone will not earn marks, interpretation is required.
SECTION A: DATA CONSTRUCTION & BASIC FORMULAS
Question 1: Sales Data Setup
Create the following dataset in Excel starting in cell A1.
Product Units Sold Selling Price (P)
Alpha 180 45
Beta 140 60
Gamma 220 38
Delta 160 52
Epsilon 200 40
Required:
a) Construct the table neatly with appropriate headings.
b) Insert a new column called Revenue (P).
c) Use a formula to calculate revenue for each product.
d) Calculate total revenue.
e) Calculate average revenue.
SECTION B: ABSOLUTE & RELATIVE REFERENCING
Question 2: VAT Application
VAT is charged at 14%.
a) Store the VAT rate in one separate cell.
b) Create a new column called VAT Amount (P).
c) Use an absolute reference to calculate VAT for each product.
d) Create another column called Revenue Incl. VAT (P).
Explain (in one sentence):
Why is absolute referencing necessary in this calculation?
SECTION C: IF STATEMENTS & BUSINESS LOGIC
Question 3: Performance Classification
Management has set a minimum revenue target of P8,000 per product (excluding VAT).
a) Create a column called Performance Status.
b) Use an IF statement to classify each product as:
“Target Met” or
“Below Target”
c) Count how many products met the target.
Question 4: Decision Support
Based on your results:
a) Identify which product(s) management should review.
b) State one possible business action management could take.
SECTION D: BAR / COLUMN CHART (COMPARISON)
Question 5: Revenue Comparison Chart
Using revenue excluding VAT:
a) Create a column (bar) chart comparing revenue by product.
b) Ensure the chart has:
A clear title
Proper axis labels
Data labels
c) State two insights management can obtain from this chart.
SECTION E: PIE CHART (COMPOSITION)
Question 6: Revenue Contribution
a) Calculate the percentage contribution of each product to total revenue.
b) Create a pie chart showing revenue contribution by product.
c) Clearly label percentages on the chart.
Explain:
Why is a pie chart appropriate here, but not always appropriate in business analysis?
SECTION F: AVERAGE & CONDITIONAL THINKING
Question 7: Above / Below Average Analysis
a) Calculate the average units sold.
b) Create a column called Sales Volume Status.
c) Use an IF statement to classify products as:
“Above Average Volume”
“Below Average Volume”
d) Identify any product that is:
Above average in units sold but
Below target in revenue.
SECTION G: SIMPLE DASHBOARD THINKING
Question 8: Summary Section
At the top of the worksheet, clearly display:
Total Revenue
Average Revenue
Number of products meeting the target
Use cell references, not retyped values.
SECTION H: REFLECTION & INTERPRETATION
Question 9: Interpretation (Written Response)
In 5–6 lines, answer the following:
a) Which product is the best overall performer?
b) Which product poses the greatest concern?
c) How does Excel help management make faster decisions in this case?
Constructing charts and diagrams using Excel
4. For each of the following data sets, choose a suitable type of chart/diagram and
construct it using Excel.
a) A multinational company sells its products in six regions world-wide. The total
sales in each region for 2005 and 2006 are given below.
Region Sales, £m
2005 2006
W. Europe 91 83
E. Europe 152 112
North America 33 28
S.E. Asia 182 164
Africa 40 55
Australasia 36 47
b) The turnover, by department, of a large store for 2006 is given below:
Department Turnover, £m
Furniture 7.5
Fashions 11.9
Electrical 12.4
Soft furnishings 3.2
Hardware 5.8
SECTION I: PROFESSIONAL PRACTICE
Question 10: Spreadsheet Quality
List three spreadsheet best practices demonstrated in your work that reduce errors and
improve decision-making.