I am really struggling to understand this assignment. I have tried my best to grasp the concepts, but I am still lost. I would be incredibly grateful if you could help me break down the assignment step by step. Please do not report my question; I am really in need of assistance. I added all the instructions I have, there is no missing information. I gave all the instructions I have. Please help me!!

Appendix 1 Estimated Financial Statement and Notes to the Accounts for the year to 31st December 2023 Estimated Income Statement - 31st December 2023 Velvet Chocolates Ltd 2023 Sales Revenue 4,532,500 Raw materials (1,359,250) Wages (299,200) Packaging (232,750) Cost ofsales 1,892,200! Gross Prot 2,640,300 Shipping 8: Distribution (122,500) Overheads 1 2,296, 5 31 I Operating Profit 221,269 Tax (55,31?) Profit after Tax 165,952 Dividend Paid (8,2981 Prot aer tax and 157,654 Notes to Accounts: Staff Costs (excluding 9 ACC2125 Pair Coursework Assignment 2023-24'docx Average number of 5 Average Salary in 2023 38,400 Directors Remuneration 350,000 Appendix 2 (to be included on excel worksheet 3) Assessment Individual contribution to Excel Coursework Discuss which parts of the coursework spreadsheet you were solely responsible for and those that involved the two of you and how you arrived at this allocation of responsibilities. (150 words each rnax.) Student One Student Number (150 words max.) % Contribution to spreadsheet Student Two Student Number (150 words max.) % Contribution to spreadsheet It is expected that both students will contribute to both the spreadsheet section and the written report. 10 ACC2 125 Pair Coursework Assignment 2023-24.docx WHERE THERE IS A SIGNIFICANT DIFFERENCE IN THE PARTICIPATION OF ONE STUDENT VERSUS THE OTHER THEIR MARKS WILL BE SCALED DOWN TO REFLECT THIS LACK OF INVOLVEMENT. For example if a student has contributed less than 50% to the excel spreadsheet then that student will not be eligible to the full marks available. Appendix 3 (to be included on rst page of word Report submitted through Iurnjtin) Assessment Individual contribution to Coursework Report on word Discuss which parts of the coursework report on word that you were solely responsible for and those that involved the two of you and how you arrived at this allocation of responsibilities. (100 words each max.) Student One Student Number (100 words max.) % Contribution to Report part (d) & (e) Student Two Student Number (100 words max.) % Contribution to Report part (d) & (c) It is expected that both students will contribute to both the spreadsheet section and the written report. WHERE THERE IS A SIGNIFICANT DIFFERENCE IN THE PARTICIPATION OF ONE STUDENT VERSUS THE OTHER THEIR MARKS WILL BE SCALED DOWN TO REFLECT TI-IIS LACK OF INVOLVEMENT. For example if a student has contributed less than 50% to the word report then that student will not be eligible to the full marks available. Marks Excellent Good Satisfactory Poor Unacceptable given Presentation, feedback and Lab participation: Consistency in 2 2 numerical presentation; Readability; Headings, Titles, 3 2 Format, Column widths etc N A Lab participation N W Feedback Participation Section total 14 14 11 7 5 4 Excel Skills for part (a), (b) and (c) forecast income statement and cash flow statement : Most efficient formulae to achieve objectives; 26 21 13 11 8 headings, drive data between sheets. Use of excel functions such as IF and conditional formatting. Numerical accuracy of 10 cash flow statements. Reasonable 10 8 5 Assumptions Identification of expenses 10 5 A Correct inflation factors 10 8 5 4 Section total 66 66 52 33 27 20Part ((1) Report Recommendations Identify differences Timing differences Advantages & Disadvantages of method Alternative method 6.5 LS 3.25 0.75 2.5 0.5 Section total 10 10 Part (e) Report Relevant Cost Estimate, clear assumptions and clear statement of ACCEPT or REJECT. Other commercial iactoms 2.5 2.5 1.5 1.5 Section total 10 10 Total 100 100 50 30 {ii} 12 Monthly Cash Forecasts plus Yearly Total for 2024 ONLY Month 1 Month 2 Month3 etc. Month 12 Year Total Inows Total Inows Expenses (detailed by expense type) x x Other outows ( detailed by type) x x Total Outows Net Movements Opening Balance Closing Balance 'Work Sheet 2' Cash Flow Workings + Assumptions Yearly Income 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 ow Work in whole 's and ignore pence on front Summary. Corporation tax is charged at the rate of 25% on operating prots. The company pays tax for the 12 month period on the 1st September in the following year. The business pays a 5% dividend based on the prot with a minimum guaranteed distribution of 5,000 p.a. unless a loss is reported for the year in which case no dividend is paid for that year. The dividend is paid for the year ending 31st December in the following April each year. In order to achieve expected increased levels of output the company plans to install additional machinery in its factory, there will be an initial payment of 275,000 in March followed by another payment of 275,000 in August. At present the company has an arranged overdraft facility charged at 7.5% up to a balance of 600,000 and 10% for balances above 600,000. Retained earnings were 25,000 at the beginning of 2023. Information relevant to the preparation of the 12 month detailed 2024 Cash Flow Forecast: The opening cash balance is estimated to be 150,000 at the beginning of 2024. Sales are collected 25% in the current month and 74.75% the month after, the remaining 0.25% relate to uncollectable bad debts. It is current practice to produce sufficient units each month to meet sales required and to cover minimum closing stock requirements. It is company policy to maintain finished goods stock at 12% of the following month's requirement. The opening stock of nished goods is estimated at 1,600 units at the beginning of November 2023. Each unit requires 450g of ingredients at a present cost of 12.33 per kg in 2023. Raw material stocks are maintained at 20% of the following month's requirements. Opening raw material stocks were 1,800 kg at the beginning of November 2023. Purchases are paid for 40% in the following month and 60% the month after. All other costs are paid in the month they are incurred in. Each unit takes 3.5 minutes to complete and the company estimates idle time is running at 5%. Direct labour is currently paid 20 per hour in 2023, all direct labour is hired on zero hour contracts and hours can be adjusted to meet production for each period. Ignore sales tax. Workings should to be done in 'work sheet 2' and driven to a front page summary 'work sheet 1' showing yearly prot forecasts and monthly forecast cash ow summaries produced under the assumptions described above. The idea of any model is that if say the key assumptions are altered all relevant gures will automatically change for all future years; similarly if the percentage of sales is changed then again any linked data also needs to reect this. As far as possible ALL gures should be driven initially from last years results or the previous years forecast. It is important to work out which are xed and which are the variable costs in putting together your forecasts and consider the cost driver. Section 2: Special Order Information Velvet Chocolates Ltd has received a request 'om a new retail customer based in the UK to produce 5,000 boxes of chocolates to be made and supplied in September 2024 at a price of 15 per unit. The box will not contain the standard selection and will use some ingredients of lower quality than those usually used by Velvet Chocolates Ltd. The following information has been provided concerning this special order: Ingredients used will cost 15% more than those usually purchased as the additional order will require a new supplier to be used. The customer has requested a different style of packaging which will cost 5 0% more than the usual used. In addition, a special machine will have to be hired for the month in order to package the chocolates at a cost of12,500. The chocolates will be sent directly to the retailer which will result in shipping costs being double the usual amount for distribution. As September is traditionally in the low season the production manager has indicated that there would be spare capacity to produce the additional units. It is expected that there will be additional administration time taken to process the order. Note :- IMPORTANT Students will be awarded 1 mark for Lab session attendance and lab quizzes completed with a maximum of 5 marks available for the 5 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. Remember to write your both Student Numbers of your pair at the top of your Excel worksheet 1. Required : a) Produce a DETAILED 5 year Forecast Income Statement for the business showing 2023 for reference and covering 2024 2028 inclusive. b} Produce a DETAILED monthly Cash Flow for JUST 2024 based on 2023's Financial Statement and all of the information given above. You will need to use the 12 monthly information given above. You will need to expand the overhead section by making reasonable assumptions as to what other costs you would expect to be reected 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 reconcile to those given in the forecast Financial Statement. Case Study Assignment You have recently been recruited by Velvet Chocolates Ltd, a chocolate manufacturer, as an assistant management accountant reporting to the Management Accountant, Zoe Taylor. Your role involves helping in the preparation of Jture budgets and forecasts. Up until now they have only produced annual Financial Statements and a simple budget, however, given the need for better forward plarming you have been asked to produce a ve year prot forecast (Income Statement) for the years 2024 to 2028 and a detailed monthly cash ow for next year 2024. Zoe, has determined the future cost estimates without discussing these with other managers. The increase in sales in future periods is based on estimates produced by the sales manager. More relevant information will be extracted from their estimated Income Statement 2023 sumrls tail in Appendix 1- Section 1: Management Accountant's cost estimates and Sales managers forecast information Velvet Chocolates Ltd manufactures deluxe chocolates sold in a premium box. All sales are to customers within the UK and the sales manager predicts sales for 2023 to be 245,000 boxes of chocolates. The sales are seasonal and the sales manager is optimistic that sales will grow at 5% per annum for the next ve years. The selling price is expected to remain at 18.50 per item. Cost of sales includes direct labour wages and raw materials and packaging. The boxes of chocolates are distributed to UK retailers using a distribution company that charges a fixed price per unit to deliver. Estimated shipping and distribution costs in 2023 are 0.50 per unit. These costs are expected to reduce in 2024 to 95% of the current level as fuel costs have fallen, in 2025 and onwards it is estimated the costs will inate by 2% per armum. Raw material prices are expected to increase by 8% in 2024, then by 6% per annum from 2025 . Packaging costs are 0.95 per unit. Packaging costs are expected to increase by 6% in 2024, then by 4% per annum from 2025. The fixed lease for the premises of 288,000 expires on 15-t September 2024. Anew 5 year lease has been agreed at a price of 312,000 p.a. The rent is paid quarterly in arrears. Rates are estimated as 40% of the rental value. Utility costs to inate by 3% pa. Direct labour Wages increase by 6% in 2024 and then by 3% per annum from 2025. Salaries to increase by 5% in 2024 and then by 3% per annum from 2025. Depreciation costs are expected to increase by 20% in 2024, then by 5% per armum from 2025. All other overheads are expected to increase in-line with UK ination of 2.5% per annum. A cost estimate using relevant cost principles for the special order. Give detailed notes explaining how each cost has been calculated and why some costs have been included and other costs excluded from your cost estimate. Discuss any other commercial factors that Velvet Chocolates Ltd should consider when making the decision whether to ACCEPT or REJECT the special order. The report should be 300 words maximum on Word document, submitted through 139mm! dropbox t) Important note on submission of word report and excel spreadsheet Clearly identify your STUDENT NUMBERS on all work submitted on both the excel spreadsheet and the word report. Excel Spreadsheet Submission - part (a) to (c) Complete the one page summary of your individual contribution to this coursework using Appendix 2 to be included on 'worksheet 3' of your excel spreadsheet. Word Reports part (d) and (e) Complete the summary of your individual contribution to this coursework using Appendix 3, this should be page one of your word report. c) Clearly identify, on your excel spreadsheet, all assumptions used in your model as to how expenses or income have been inated and translated and which costs you have treated as being xed as opposed to variable costs. d) Using the information provided in Section 1, produce a brief report for your manager. The report should include the following: Recommendations that the business needs to consider going forward given your ndings on the five year prot forecast and monthly cash flow forecast. Identify the differences between a cash ow statement and a profit forecast as to what items are included in one but not in the other. Explain the timing differences behveen receipts from sales and payments for purchases and the impact on the cash ow balances in 2024. Critical discussion of the budget method applied by Zoe Taylor, the management accountant, for determining the predictions of future costs. Suggest an alternative method to determine predictions of future costs. The report should be a brief summary of the points required, no more than 900 words maximum on Word document, submitted through Illliti dropbox. Continued on next page e) Using the information provided in Section 2, prepare a brief report advising Velvet Chocolates Ltd whether to ACCEPT or REJECT the special order. This second report should include the following: Write Both Student Numbers clearly at the top of worksheet l Formats should be along the lines of : 'Worksheet 1' (a) Yearly Forecast Income Statements (jterpjsed by each expense or income type for the year NOT in condensed format (as per in Appendix 1) (i) Income Statement based on predicted Financial Statements Last Year Year 1 Year 2 Year 3 Year 4 Year 5 2023 2024 2025 2026 202'? 2028 Sales Revenue Cost of Sales X X Gross Prot Gross Prot margin % Overhead Expenses (detailed by individual expense type) X X Total Expenses Operating Prot Operating Prot margin% Tax Prot After Tax Dividends Paid Prot aer Tax and Dividend Retained Earnings !J_Ifw_d Retained Earnings _t}y_d_