Management Accounting

ACC2125 Excel Coursework 2017-18

ACC2125 Excel Coursework 2017-18

You have recently been recruited to help a fashion company. Up until now they have only produced annual Financial Statements but given the need for better information to plan for the future your boss had asked you to produce two different forward cash forecasts for the next five years based on the information available from their latest Financial Statements which are summarised in Appendix 1. At present the company do not have an overdraft facility.

Option One: (No major changes going forward)

Constant sales volume growth of 5% p.a. but increased volumes at the expense of Gross Margin with current average price per product falling by 1% p.a.

Utility costs to inflate by 10% p.a.

Wages and salaries to increase by 2% p.a.

All other overheads to increase with inflation forecast to be 3% p.a.

They sell luxury clothing products that are more popular in the winter months with 40% of sales taking place between October to December inclusive. Sales for last year showed sales of 160,000 products with an average selling price of £130 per item.

Unusually the company still produces luxury clothing and other products in the UK from specialist woollen and fabric brands unique to the UK. This is their USP (unique selling point) to offer such products made in the UK to the rest of the world. The company plans to purchase £250,000 of machinery every year to keep at the forefront of the latest manufacturing techniques. These machines are installed over the summer holiday period - the last week of April and the first week of May and has already spent such an amount in the last financial year.

On the last day of the financial year the company purchased the shares of one of the founding directors for £2m in cash on her retirement to leave a closing cash balance of £55,000.

Option Two – Impact of Brexit

All information the same as above plus :

Brexit is causing a lot of uncertainty in the business world and they now wish to also assess possible effects of Brexit on sales in their forward budgets using sensitivity analysis. Currently 60% of sales are to the EU and they wish to assess the possible impact of Brexit going forward.

Non-EU sales will remain unchanged but they assess the risk to EU sales falling to 75% of future predicted levels as 0.6; EU sales remaining as forecast 0.3 and additional growth of EU sales of 5% over current forecasts as 0.1

You only have access to last years Financial Statements and not to any month by month breakdown as previously they did not produce any management accounts (see Appendix One over the page)

Ignore taxation.

All workings need to be done in ‘work sheet 2’ and driven to a front page summary ‘work sheet 1’ showing yearly cash flows and monthly summaries produced under both of the assumptions described above. Work in whole pounds

The idea of any model is that if say the key assumptions are altered all relevant figures will automatically change for all future years; similarly if the percentage of sales is changed then again any linked data also needs to reflect this. As far as possible ALL figures should be driven from initially last years results or the previous years forecast.

It is important to work out which are fixed and which are the variable costs in putting together your forecasts.

Note :- VERY IMPORTANT

Students are required to attend a minimum of 50% of the first 4 Excel lab sessions which begin in WEEK TWO of the module (for half of the students registered on the module) and WEEK THREE for the remainder. Your attendance will be monitored and if you have not met this requirement then you will have one last chance to by attending your final FIFTH lab session.

If any student fails to satisfy this condition then you will be deemed to have FAILED the coursework and will need to resubmit a different version which will automatically be capped at 40% of the available marks as a second submission.

Required :

a) Produce a detailed cash flow from last years Financial Statements using the information in Appendix 1. You will also need to expand the overhead section by making reasonable assumptions as to what other costs you would you would expect to be reflected in a manufacturing business and ensure you have accurately included all of the information given to you in Appendix 1 Please make sure your individual category totals for Sales, Cost of Sales, Distribution, Overheads etc. do actually agree to those given in the Financial Statements.

b) Produce detailed yearly cash flows for the next five years based on the cash flow produced in (a) above using the company’s original budget assumptions.

c) Produce revised yearly forecasts for the next five years using sensitivity analysis to reflect the possible impacts of Brexit. Show the differences between b) above and c) in cash generated on a year by year basis.

d) Produce a detailed budget for the next 12 months on a monthly basis using the original budget assumptions including a total for the year.

e) Produce a revised monthly forecast for the next 12 months reflecting the possible impacts of Brexit and give a total for the year. Show any differences between the two monthly forecasts on a month by month basis.

f) Clearly identify all assumptions used in your model as to how expenses or income have been inflated and which you have treated as being fixed as opposed to variable costs.

g) Write a report to your manager summarising your findings and what actions the business needs to consider going forward together with the benefits or limitations of sensitivity analysis. (800 words max.) This Report needs to be included on ‘worksheet 3’ of your Excel file.

h) Complete the one page summary of your individual contribution to this coursework using Appendix 2 to be included on ‘worksheet 4’

Appendix 1

Last Year’s Results – from the Financial Statements and Notes to the Accounts

£

Turnover 20,800,000

Cost of sales 14,240,000 (Materials and wages)

Gross Profit 6,560,000

Distribution 3,000,000

Overheads 1,300,000

Operating Profit 2,160,000

Turnover £20,800,000

Geographical Breakdown of Turnover:

£

Non –EU 40% 8,320,000

EU 60% 12, 480,000

Staff Costs (excluding Directors)

Salaries £260,000

Wages £1,440,000

Average number of Employees

Production 80

Admin 10

Directors

Remuneration £360,000

Operating Profit

This has been arrived at after charging:

Depreciation £45,000

Lease of the Premises £300,000

Audit fees £24,000

Buy-back of Shares

25,000 Ordinary shares were redeemed for £2,000,000 on the last day of the financial year.

Appendix 2 (to be included on worksheet 4)

Assessment Individual contribution to Excel Coursework

Discuss which parts of the coursework you were solely responsible for and those that involved the two of you and how you arrived at this allocation of responsibilities. (150 words max.)

Student One No. & Name (150 words max.) % Contribution

Student No. & Name (150 words max.) % Contribution

Write Both Student Numbers + Names clearly at the top of worksheet 1

Formats should be along the lines of :

‘Worksheet 1’

(a) Yearly Cash Flows (itemised by each expense or income type for the year – NOT in the condensed format of the Financial Statements)

(i) Cash Flow based on Financial Statements + Five Year Forecasts

Last Year Year 1 Year 2 Year 3 Year 4 Year 5

Income

Total Inflows

Cost of Sales

Overhead Expenses (detailed by individual expense type)

x

x

Total Outflows

Net Movements

Opening Balance

Closing Balance

(ii) 12 Month Forecasts plus Yearly Total

Month 1 Month 2 Month 3 etc. …….. Month 12 Year Total

Income

Total Inflows

Expenses (detailed by expense type)

x

x

Total Outflows

Net Movements

Opening Balance

Closing Balance

‘Work Sheet 2’

(a) Cash Flow Workings + Assumptions

Yearly Income - broken down into:

EU and non EU sales

Direct Expenses

Materials

Wages

Overheads: (by individual expense type)

Salaries Rent Audit Etc., etc.

All assumptions to be clearly stated on ‘Excel Worksheet 2’ next to the expense or income flow

Work in whole £’s and ignore pence.

‘Work Sheet 3’ - 800 word Report

‘Work Sheet 4’ - Appendix 2 allocation of responsibilities

ACC2125 Management Accounting 2017-18

Excel Exercise

Students are required to attend the Excel workshop computer labs to enable them to complete this assignment. In addition to gaining Excel skills to produce the correct numbers, marks will also be awarded for an appropriate use of Excel skills together with the presentation and layout of the final document.

You are expected to work in PAIRS given the complexity of the task and are reminded NOT to share any of your work or collude with any other group. If for any reason you are unable to find another student to work with then you will be required to submit this work by yourself. No group is to consist of more than two students.

A Discussion Board has been set up in the Excel Coursework section of Unihub if you are having difficulty in finding a partner to work with.

ALL DETAILED WORKINGS MUST BE SHOWN ON ‘WORKSHEET 2’ BEFORE DRIVING ANY TOTALS ONTO THE FRONT PAGE SUMMARY (‘WORKSHEET 1’).

You should adopt the suggested layouts on cash budgeting from your first year’s ACC1115 handbook as a guide as to how to show your detailed workings and front page final summary of results.

The opening and closing cash movements should be summarised at the end of your cash flow statement – i.e. do not include the opening balances as part of your receipts.

If identical pieces of coursework are submitted by two or more pairs all will be awarded zero marks.

If you have any queries in understanding what is required of you to complete this exercise please raise them immediately with myself.

John Fletcher 12th October 2017

ACC2125 Coursework (Excel) - Marking Guide

	Marks given	Very Good	Good	Satisfactory	Poor	Unacceptable
Presentation and Style: Consistency in numerical presentation; Readability; Headings, Titles Format, Column widths etc. Working in pairs effectively		4 4 2	3 3 2	2 2 1	1.5 1.5 0	0.5 0.5 0
Section total	10	10	8	5	3	1
Excel Skills : Most efficient formulae to achieve objectives; headings print ranges , drive data between sheets		30	22	15	12	8
Section total	30	30	22	15	12	8
Numerical accuracy of cash flow statements. Reasonable Assumptions Identification of expenses Correct inflation factors		15 10 10 10	10 8 8 8	8 5 5 5	6 4 4 4	2 3 3 3
Section total	45	45	34	23	18	11
Report on projected outcome of Brexit on future business and use of sensitivity analysis.		15	11	8	6	4
Section total	15	15	11	8	6	4
Total Marks given
Out of		100

9