Unit learning outcomes assessed: Learning outcome 2 – Evaluate the impact of technology and how it can aid the communication of management accounting concepts to both finance and non-finance personnel. Requirement: There are seven tasks in this assignment. All tasks must be completed and submitted in Microsoft Excel. You can only submit ONE file and this must be in Microsoft Excel. Any written parts to your work must be included within this file. The tasks are designed to assess both management accounting, technology and research competences. You will need to use the Excel skills covered during your weekly Computer lab sessions. All the material from the lab sessions can be found in weeks 1-7 on Moodle. The management accounting skills were covered during lectures, supertutorials and tutorials. You can access the notes from these sessions on Moodle. All work must be individual. Hand In arrangements: You are required to hand this assignment electronically via email to c.levison@mmu.ac.uk by midnight on Friday 4th August 2017. Financial Decision Making: Year One (Level 4) Resit Assignment Brief Page 2 Assignment Title: You are a junior analyst working for a firm of business advisers. One of your clients Mr P has come into the office with some figures from an ice cream franchise he owns. http://www.franchisedirect.co.uk/foodfranchises/gallones-ice-cream-parlours-franchise- 11166/ Your boss, Mr S would like you to carry out some analysis of the figures, and he has provided a breakdown below of what is required. As the figures provided by Mr P estimates, they are likely to change in the future, Mr S wants you to produce a series of spreadsheets (a workbook) he can amend at a later date. Your workbook must be fully linked with formulae throughout, clearly labelled and be easy for Mr S to use. You must also show where you have gathered any research information from, so Mr S can revisit your data. Mr S has suggested you read the whole brief and the marking criteria shown on the final two pages before beginning as you may wish to plan your workbook accordingly. Financial Decision Making: Year One (Level 4) Resit Assignment Brief Page 3 Task 1 Last year’s cost card and sales forecast are shown in Appendix 1 (page 6). As you can see, Mr P still produces inefficient hand written schedules, which will need to be checked for errors. From the information you have been given prepare: A standard cost card for last year, clearly highlighting any figures you have found to be incorrect. Task 2 Mr P sold fewer servings than he expected and he thinks he should have made a profit of £129,329 this year (see Appendix 1), rather than the £140,000 he was hoping for. Using the Actual results show in Appendix 2 (page 7) – prepare a budgetary control schedule showing the following:- The original budget for the year. A flexed budget for the actual level of sales. The actual profit for the year. You will need to explain to Mr P why his expected profit of £129,329 is incorrect. Task 3 Calculate the following variances:- Materials price and usage. Labour rate and efficiency. Fixed overhead expenditure and volume. You should suggest a reason for each variance you have calculated (approx. 50 words per reason) Prepare a Reconciliation Statement showing Budgeted to Actual profit. Task 4 Mr P wants to do some planning for next year – He is assuming sales of 105,000 servings (although this forecast may change). Financial Decision Making: Year One (Level 4) Resit Assignment Brief Page 4 He wants to reduce the serving size next year, but he does not know by how much. He thinks it will be in the range of 1% to 5%. Other changes to last year’s figures Selling price – you will need to use your research from task 6 to determine the selling price for next year. Staff costs to change to either decrease to £7.50, stay the same or increase to £8.50 Fixed overheads will be £45,000. No change to the royalty. Prepare next year’s cost card for one serving. Prepare next year’s budget. Task 5 Using your budget from task 4, prepare a breakeven chart for next year. Task 6 Research task – Mr P has noticed a number of other ice-cream parlours have opened recently, and he has a feeling that his ice cream maybe incorrectly priced. Perform some research on the likely competitors pricing. Advise Mr P what you think the price should be and why. Task 7 – Spreadsheet Functionality Before submitting your spreadsheet to Mr S, make sure he can easily alter the information that may be subject to change (see Task 4) It is vital that the figures requested can be altered easily and the changes are reflected in the rest of your workbook and you should test your workbook before handing it in. Mr S must be able to alter these figures himself easily and see the effect of these changes on the overall position. Assessment of your work book Your annual appraisal at work is due soon, and your boss (Mr S) will be evaluating you on how well you have done this piece of work – fundamentally – does your workbook function as a tool to advise Mr P. He will be looking at the following criteria: Formulas – have you used formulas to make sure your workbook functions properly? If you ‘type’ into cells, task 7 will not work and it will be difficult to provide any meaningful advice to Mr P. Financial Decision Making: Year One (Level 4) Resit Assignment Brief Page 5 Workbook structure – is it clear what each page of the workbook is showing. Is it easy to navigate round the workbook, are sheet tabs labelled etc. Remember, your boss will be using this spreadsheet in a meeting – can he easily locate the info he needs? Linkages between sheets – as for formulas, if your sheets are not linked it will not function as it needs to, and the information will not flow through the workbook. Tasks – are all of the seven tasks complete? Clients don’t appreciate (or pay for) half finished work. Data collection – is your research referenced – are there notes to show where numbers were gathered from? Can Mr S easily find your data? Workings – you should show any workings you have done to get to your figures. Are workings labelled and tidy? Management accounting knowledge – are you demonstrating your management accounting knowledge in a way that in clear and simple– remember Mr P is not a finance professional, and you should show any assumptions you have made. Evaluation of business – how is the business doing? You must provide some advice to Mr P based on the data you have produced. Presentation and intended audience – does the workbook function properly? Is it clearly labelled? Is it easy to find the cells that need altering for Task 7? Is it easy to read without too much information per sheet. See the Marking Criteria Rubric on Page 8 and 9 for more detail of the assessment criteria