AD2.
1 Re-assessment Task Instructions
This assessment involves creating a Profit/Loss Statement for Esse Ltd. producer of instant coffee and related products.
From the data provided below from the first quarter of 2017, design a spreadebeer for Esse Ltd, to be used at both the Glasgow and Dundee branches. You should ensure that the
Expenses and Revenue data for each branch are on a separate worksheet and hath are tiaked to the consolidated First quarter summary Statement.
1.Print out your draft layout one showing values and one showing formulae.
Code Glasgow Dundee
Expenses (E) Jan Feb Mar Jan Feb Mar
2017 2017 2017 2017 2017 2017
S011712 8000 6000 20000 5000 7000 8000
Supplies
Coffee C235789 30000 15000 51000 21000 20000 30000
Advertising A1392 15000 4000 32000 18000 15000 17000
Rent R07678 10000 4000 30000 10000 11000 12000
Other 053764 4000 5000 17000 5000 5500 4000
Operating
Expenses
Wages and 345173 72000 40000 47000 25000 30000 31000
Salaries
Income statement
(£) Glasgow Dundee
Revenue 400000 370000 406000 550000 410000 390000
Cost of Sales 25%of Revenue
Operating Profit Revenue- Cost of Sales-Expenses
Please note: You should print out worksheets only when asked to do so.
2. Create a macro to run and print your name in the header of each worksheet
To each footer add the text (IT in Business: Spreadsheets) followed by todays date.
3. During the consultation regarding this spreadsheet it was noted that the expenses reference codes were different lengths. Please use a function to only use the first 4characters and numbers in the reference given.
4. Add a column to the Expenses worksheets to show the Total for the Quarter.
5. In the Income Statement worksheet, calculate the Operating Profit for the Glasgow and Dundee branches.
6. In the Glasgow worksheet, in the Total for Quarter column, NAME the range of expenses, not including Wages and Salaries, as EXPENSES_1, and the Wages and Salaries expenses cell as EXPENSES_2. Add a formula to calculate the Total Expenses for the quarter, using these two named ranges.

7. Insert a column to the right of the March column in the Glasgow worksheet. Insert a formula which will
I allow the words CHECK OUT to appear against any March expense
figure that is greater than twice the sum of the January and February figures, and the word OK where that is not the case.
8. It would be interesting to have a summary showing total figures that are against CHECKOUT in March. Please produce this.
9.Please calculate the average expenses in March. With each of the expenses categories (Supplies, Coffee, Advertising, Rent, Other Operating Expenses, Wages and Salaries, etc) we would like to know how many of the expenses categories are greater than average expenses in March
10. On the Glasgow worksheet add a column to the right of the Total for Qtr column and call it Expenses Total for previous Qtr, and add the following figures, which you should
then sum:
Supplies 25, 000
Coffee 66, 000
Advertising 78, 000
Rent 56, 000
Other Operating Expenses 20, 500
Wages and Salaries 108.000
11. Apply conditional formatting to Total for Qtr figures to highlight those which have increased by more than 10% over the previous quarter.
12. It is possible (though not confirmed) that there will be a percentage increase in the revenue at both two branches. Please add a calculation to the operating profit sheet to show what the revenue from both two branches would be should a 3% increase per
month.
13. Use spreadsheet features to carry out the following tasks:
i ) Freeze the column and row labels and scroll down and to the right to show only the total of the column Expenses Total for Previous Quarter.
ii) Demonstrate to your lecturer unhiding % of Total Expenses column that you hid
earlier.
iii) In cell A1 add a Comment in the Glasgow worksheet that these are draft figures and are still to be finalised.
iv) Edit the above Comment, add `as at  (today's date).
v ) Delete the comment.
vi ) Urifreeze the column and row labels
vii ) Only certain members of staff are to have access to the workbook you have created. Demonstrate the protection for:
a) Workbook b) Cell

O3 Re-assessment Task Instructions
1.From the Glasgow worksheet you have created, create a chart to show the
composition of the total expenses for the first quarter. Apply suitable enhancement
to this chart to highlight the data. (Based on single data series)
Describe the chart in terms of its structure and the relevance of the data to the business.
Comment on the various components within the Expenses. Are there any areas that may cause concern?
Suggest an adjustment to the spreadsheet that would help monitor this more accurately.
2. From the Income statement worksheet you have created for Glasgow and Dundee, create a bar chart to show the comparison of Operating Profit and cost of sales for the individual branches. Apply suitable enhancement to this chart to highlight the data. (Based on multiple data series)
Change the colour of the Operating Profit series to green to highlight this data.Describe the chart in terms of its structure and the relevance of the data to the business.
Comment on the performance of each branch as illustrated by the chart.