City Guilds Model Questions Vol1 2022 Information Technology Question 4

A small retail shop records its monthly sales (in thousands of Naira) for the year as shown in the table below. The manager wants you to use a spreadsheet to analyse the data and help plan for the next year. | Month | Sales (₦ 000) | |-------|--------------| | Jan | 120 | | Feb | 150 | | Mar | 130 | | Apr | 160 | | May | 140 | | Jun | 155 | | Jul | 165 | | Aug | 170 | | Sep | 158 | | Oct | 162 | | Nov | 168 | | Dec | | Tasks: (a) Enter the data and write a formula to compute the total sales for the year (including the blank December cell). (b) In column C, calculate the commission for each month using the following tiered rates: up to 150 000 ₦ → 5 %; 150 001 ₦ to 160 000 ₦ → 7 %; above 160 000 ₦ → 10 %. (c) Apply conditional formatting to highlight any month where sales are below the average sales of the months with data (i.e., Jan–Nov). (d) Using Goal Seek, determine the sales figure required for December to achieve an annual total sales target of 2 000 000 ₦. Provide the formulas used, the resulting commission values for the months with data, the months highlighted by conditional formatting, and the required December sales figure.