City Guilds Model Questions Vol1 2025 Information Technology Question 4

Practice practical question 4 from the 2025 City Guilds Model Questions Vol1 Information Technology examination. This question covers Spreadsheets .

City Guilds Model Questions Vol1 2025 Information Technology Practical
Topic: Spreadsheets
Question 4 CITY_GUILDS_MODEL_QUESTIONS_VOL1 • 2025 • INFORMATION_TECHNOLOGY • practical

A small business records its monthly sales (in units) for three products – Alpha, Beta and Gamma – for the twelve months of 2025. The data are entered in a spreadsheet as shown below (A1:G13). Column A lists the month names (Jan to Dec). Columns B, C and D contain the sales figures for Alpha, Beta and Gamma respectively. The manager asks you to: 1. Determine the total annual sales for each product and the overall total. 2. Find the average monthly sales for each product. 3. Produce a line chart that displays the three products’ monthly sales on the same axes. 4. Highlight any month in which a product’s sales exceed 5 000 units. 5. Project the sales for January 2026 for each product assuming a uniform 5 % increase over the December 2025 figures. Answer the following:

Question Parts

( a )
Write the formula (using appropriate cell references) that calculates the total annual sales for Product Alpha. Explain why you chose absolute or relative references in the formula.
( b )
Using the same sheet, give a single formula that can be copied across columns B, C and D to obtain the average monthly sales for each product. State the cell where the first result should appear.
( c )
Outline the steps required to create a line chart that plots the monthly sales of Alpha, Beta and Gamma on the same graph, ensuring the chart includes a legend, axis titles and data markers.
( d )
Explain how to apply conditional formatting so that any cell in the sales data range (B2:D13) that exceeds 5 000 units is highlighted in red.

Detailed Solution & Marking Scheme

Get the step-by-step solution, key points required by examiners and detailed marking guidance through the Edcrib AI Tutor.

Reveal Answer

About This Question

This is City Guilds Model Questions Vol1 2025 Information Technology Question 4. It is one of the practical questions from the 2025 City Guilds Model Questions Vol1 Information Technology examination.

The question covers Spreadsheets .

View More City Guilds Model Questions Vol1 2025 Information Technology Questions More City Guilds Model Questions Vol1 Practice Questions Back to Questions

Master the Exam!

You've seen a preview, but there are thousands more questions plus AI tutor to break down complex solutions.

Unlock Full Access Available for Android & Windows
Chat with Support