Assignment 1: Excel task PLEASE USE MICROSOFT EXCEL
Unilever’s revenue from each of its sectors from 2015-2017 in € billions are as follows:
• 2015: Personal care – 20.1; Home care – 10.2; Foods – 12.9; Refreshments – 10.1
• 2016: Personal care – 20.2; Home care – 10.0; Foods – 12.5; Refreshments – 10.0
• 2017: Personal care – 20.7; Home care – 10.6; Foods – 12.5; Refreshments – 9.9
• Total expenses in € billions were as follows: 2015 – 48.04; 2016 – 47.15; 2017 – 47.21
Projected revenue for 2018 is as follows:
• Personal care – 10% increase from 2017
• Home care – 2% decrease from 2017
• Foods – 1.5% increase from 2017
• Refreshments – 55.7% increase from 2017
• Profits for 2018 is expected to decrease by 77% due to additional marketing costs in
respect of a new product that will be launched under the refreshments category.
The revenue earned by its largest competitor, Proctor and Gamble in USD billions were as follows:
2015 – 70.7; 2016 – 65.3; 2017 – 65.1
Required:
Module Study Guide template – May 2018 !10
Task 1 – Unilever Profit/Loss (65 marks)
(i) Enter the revenue and expense data provided for Unilever from 2015-2017 in an Excel
Worksheet (15 marks)
(ii) Calculate the forecasted sector revenue and profit for 2018 using the information
provided (10 marks)
(iii) Using appropriate formula, calculate the total revenue and profit/loss for all the years (14
marks)
(iv) Calculate the forecasted expenses for 2018 (1 mark)
(v) Using the appropriate function, calculate average revenue across all sectors for each
year (8 marks)
(vi) Using the appropriate function, calculate the highest revenue-generating sector for each
year (8 marks)
(vii) Round off all numbers to two decimal points (1 mark)
(viii) Adjust the height of all rows in this table to 1.0 cm (1 mark)
(ix) Middle align the data in all the cells (1 mark)
(x) Briefly comment on Unilever’s profitability
over the period, including the forecasted
Assignemnt task is from page 10-12
figures (2 marks)
(xi) Briefly explain your findings in (v) above (3 marks)
(xii) Briefly explain your findings in (vi) above (1 mark)
Task 2 – Unilever Revenue Breakdown (10 marks)
(i) Create a separate table which shows both the sector revenue for 2018, and the sector
revenue as a percentage of total revenue for 2018. Use appropriate formula and ensure
that the figures in the revenue column are linked to the data in the table in Task 1
(ii) Using a suitable graph, show the revenue breakdown per sector for 2018
(iii) Insert an appropriate chart title, with data labels on the inside end indicating the
category name and percentage
Task 3 – Revenue Comparison between Unilever and Proctor & Gamble
(i) Create a new table and include Unilever’s total revenue from 2015-2017 by linking the
data to the table in Task 1
(ii) In the same table, include Proctor and Gamble’s revenue data, by converting it from
USD to Euros. The exchange rate to be taken is 1 USD: 0.89 Euros (6 marks)
(iii) Using a suitable graph, compare the revenue earned by Unilever and Proctor and
Gamble from 2015-2017
Module Study Guide template – May 2018 !11
(iv) Insert an appropriate chart title, with data labels on the outside end, a legend on the
right side, X axis label to read as ‘Years’, and Y axis label to read as ‘Revenue’
Task 4
(i) Re-name Sheet 1 as Unilever P&L
(ii) Font type – Cambrian
(iii) Font size – 12.5
(iv) Center align all the cells containing numbers and headings (1 mark)
(v) Clear headings for each task
(vi) Overall presentation, layout and neatness
Sample Solution
