Using And Managing Data And Information BA3005
The table below records the sales of milk (in pints) by a wholesaler over the period 8 months.
Quantity Sold (Thousands of pints)
Month
Skimmed
Semi-skimmed
Full milk
January
25
77
46
February
25
76
45
March
23
75
42
April
23
73
42
May
22
59
38
June
21
58
35
July
20
55
33
August
15
55
31
The prices (per pint for milk) are as follows:
Skimmed:£0.89
Semi-skimmed:£0.78
Full milk:£0.95
Required:
Explain, in a presentation, how you setup the information above on an Excel worksheet to calculate the total revenue for each month. Your explanation should include the use of relative and absolute references seen in class.
In the same presentation, explain how you would use an IF statement on Excel to distinguish between months with high, medium or low revenues.
The levels of revenue are categorised as follows:
Revenue
Level
Greater than £120,000
High
£100,000 but less than £120,000
Medium
Less than £100,000
Low
Task 2
You are an investment advisor and you are supposed to make a presentation to clients how they can use Excel to deal with the following scenarios.
Scenario 1:
A customer invests a regular investment in a portfolio that is expected to return an annual interest of 11%. The customer decides to invest £200 at the beginning of each month in this portfolio.
How much money this customer will receive after a period of 3 years?
Scenario 2:
A customer took a loan of £120,000 to fund his restaurant business. The loan costs 4% per year and is payable at the end of each year within 10 years.
How much is paid each year to pay back the loan?