Produce a cash flow forecast for the 12 months from January to December 2020, and for the total year.

See pictures in attachments for information.

moodle.nottingham.ac.uk Cash flow forecast for 2020 Reset your worksheet to its original values. You now must produce the cash flow forecast for the 12 months, January - December 2020, and for the total year. To produce the cash flow forecast, you will need the 'Statement of Financial Position for 2019 for McCroft's Supermarket, which is shown below in Table 4: Table 4 McCroft's Statement of Financial Position, Year ended December 2019 Non-current assets: 840,984 Land, Property, plant, equipment and vehicles Total non-current assets Current assets: 840,984 Inventory Trade receivables 69,231 25,500 4-Nov-20 8 Prepayment Bank Cash Total current assets 11,500 125,000 1,925 233,156 Current liabilities 0 Bank overdraft Trade payables Accruals Interest payable Tax payable Total current liabilities (165,490) (23,622) 0 (42,478) (231,590) 1,566 Net current assets Non-current liabilities Bank loan Total non-current liabilities Net assets 0 0 842,550 Equity Share capital (E1 ordinary shares) Share premium Retained earnings (SoCE) Total equity 400,000 50,000 392,550 842,550 matamaathaaaahamnihalomain rahlari The format for the cash flow forecast that you must follow is shown below in Table 5: Table 5 McCroft's Cash Flow for the Months January to December 2020 see Each month (Jan. - Dec.) & Total Year note E Opening Balance Receipts Cash Sales 182 Credit Sales 183 Loan 4 Total Receipts 4-Nov-20 Payments Suppliers 5 Sales and marketing 6 General and administrative 7 Expansion Costs 8 Wages & Salaries 9 10 Managing Director's Salary + bonus Interest 11 Tax paid 12 Dividends 13 Total Payments Net Flow Closing balance The following assumptions apply: 1. The split of the 2020 sales by month is as follows: January February March April May June July August September October November December Total 8.00% 8.05% 8.10% 8.15% 8.20% 8.25% 8.30% 8.35% 8.40% 8.50% 8.60% 9.10% 100.00% 2. Cash Sales - assume that 90% of sales shown in the P&L each month are made in cash. 3. Credit Sales - assume that the remaining 10% of sales shown in the P&L each month are credit sales and are paid in the following month. The December 2019 credit sales were 23,500. 10 4-Nov-20 4. Assume that the loan is obtained and is paid into the bank account at the beginning of March 2020. 5. In regards to payments to suppliers (for the cost of goods sold, COGS), assume products are sold and purchased in the same month, so no stock movement and suppliers are paid in the following month. Note: December 2019 COGS = 155,000. To calculate COGS, use the percentage COGS you have calculated for your 2020 forecast P&L, i.e. COGS = 100% - Gross Profit percentage. 6. For Sales and Marketing, assume the following: Sales and Marketing costs are split equally by month; 75% of the costs are paid in the month; and the remaining 25% of costs are paid the following month. Note: December 2019 Sales and Marketing costs were 15,600. 7. For General and Admin., assume the following: The General and Admin. Costs, less the 50,000 establishment costs that were written off, are split equally by month; 50,000 establishment costs are also split equally by month from March to December and are paid in the month; 80% of the General and Admin. costs (excluding establishment costs) are paid in the month; and the remaining 20% of the General and Admin. costs (excluding establishment costs) are paid in the following month. Note: December 2019 General and Admin. costs were 26,000. 8. The Expansion costs of 400,000 are incurred and paid equally per month, starting in March 9. Wages and Salaries are paid in the month incurred. ere written off, are split equally by month; 50,000 establishment costs are also split equally by month from March to December and are paid in the month; 80% of the General and Admin. costs (excluding establishment costs) are paid in the month; and the remaining 20% of the General and Admin. costs (excluding establishment costs) are paid in the following month. Note: December 2019 General and Admin. costs were 26,000. 8. The Expansion costs of 400,000 are incurred and paid equally per month, starting in March. 9. Wages and Salaries are paid in the month incurred. 10. The Managing Director's salary is paid in the month incurred; any bonus is payable in March 11. The interest payment of 8% per year on the 300,000 loan starts in March and is paid each month. The loan capital will be repaid from 2025; therefore, it will not affect 2020 cash flows. 12. Assume that the last year's (2019) tax bill is paid in full in April 2020. 13. The following dividend payments are planned for 2020: An interim dividend of 40,000 is payable in July, and a final dividend of 50,000 is payable in December. Note: the dividends can only be paid if the closing cash balance exceeds 110,000 in the preceding month. Mr McCraft Supermarket Mr McCraft's Supermarket Projected statements of Profit Anuptions COGS: 2019 2020 Sales TOTAL 2015 DER Our Canned Good Fraren Food Meats Frate Clothing 2,955,400 5.08 2009 15.50 ON 210X 12.50 80% 100.0% Operating expenses Sanna 4.50% of total sales Sandma 55.000 General and 5.75% of total sales General and Depreciation 37,500 so son 25% 65% SON 655 EN 147.770 591000 45. 53172 620,614 16,625 236.432 152.57 10 171.97 361 640.00 11.01 244115 Sales De Dairy Canned Good Frogenfood Meat Fruit & Ve coing Total sales Coutloods sold Dell Dairy Canned Good frozen Food Mat Feu Vege Clothing Total cost of goods 2.955,400 2,051451 Growth 2020 2021 2002 Wapes and stories teine employees MMC 20.00 Previous year's PBT bonus payab 150.000 Me McCroft 90.000 Manager 46,500 Assistant 32,500 No of emplo 15 Ne of hours 40 Noof weeks 52 Hourly rate ERGO We increat 2. 102.00% 1255 5.75 73.885 295.540 33.565 145,7 110,117 240 126 156045 105.145 15471 357.030 207910 T91.117 L255.25 10211 Tarot Renovation 20% Gross pro 1,100,140 40 450,000 1.21 102 Written oli mindre 50,000 400.000 20 300.000 ven Loan requr Intrate EN Profitert Profit before 46515 ES 144 157.93) 314 31 500 141.320 0.000 370.458 2,500 354266 12:47 Operatings Sales and me Geneland Depreciation We all Managing die Total operating expenses PRIT Interest Profit before tax Taxation Profit Prowth 977719 212,191 212,391 LIZO 121,01 22.000 1080 20 81,441 52 12,428 193 Sheet1 + Abu Baba Yes = ABC Mr McCroft's Supermarket Projected statements of Profit or loss 2019 2020 2021 2022 147.770 59100 458.07 531.972 $20,634 369,425 236,432 157573 510 290 472.975 549,261 | 381.431 244116 161145 645382 500,171 580 544 677.651 403 364 2815 175.000 6912 316.413 $22.955 725.751 432.607 is Sales Del Dairy Canned Go Freren Food Meats Fruit & Vege Clothing Total sales Cost of goods sold Del Dairy Canned Goox Frozen Foods Mests Fruit & Vegel Clothing Total cost of goods sold 2.955 400 30051,451 1.226,909 1,460,00 73,885 295 540 343,565 345.782 310,317 240 126 156045 76,286 305,145 354,731 357.020 320,402 247,930 161,117 19,673 322.691 375 128 377,548 338 825 262,156 170,181 14.06 402.125 40491 361,390 281,195 112, 733 1.765 260 1.822631 1927,433 2007, 122 Gross profit 1,190,140 40.27% 1,228,819 4022 1.299,476 40.27 1.291,600 year Operating expenses Sales and me General and Depreciation Wages & Managing de Tots operating expenses PAIT interest Profit before tax Tanti Profitert 187,990 114,936 37.500 347 320 30.000 192.315 370.458 $7,500 354,266 132,478 200,211 3:30.547 57.500 361,352 90,000 210.739 143,97 $2,500 368.579 977,740 212,391 212,391 12.470 169,913 265 1.107.000 121,601 20,000 101.80 20,360 81,441 5219 1019.610 259,856 24.000 215, L2990 275,693 2400 251.690 So, 340 101,150 188,693 NET Abc Baba Yes BIU moodle.nottingham.ac.uk Cash flow forecast for 2020 Reset your worksheet to its original values. You now must produce the cash flow forecast for the 12 months, January - December 2020, and for the total year. To produce the cash flow forecast, you will need the 'Statement of Financial Position for 2019 for McCroft's Supermarket, which is shown below in Table 4: Table 4 McCroft's Statement of Financial Position, Year ended December 2019 Non-current assets: 840,984 Land, Property, plant, equipment and vehicles Total non-current assets Current assets: 840,984 Inventory Trade receivables 69,231 25,500 4-Nov-20 8 Prepayment Bank Cash Total current assets 11,500 125,000 1,925 233,156 Current liabilities 0 Bank overdraft Trade payables Accruals Interest payable Tax payable Total current liabilities (165,490) (23,622) 0 (42,478) (231,590) 1,566 Net current assets Non-current liabilities Bank loan Total non-current liabilities Net assets 0 0 842,550 Equity Share capital (E1 ordinary shares) Share premium Retained earnings (SoCE) Total equity 400,000 50,000 392,550 842,550 matamaathaaaahamnihalomain rahlari The format for the cash flow forecast that you must follow is shown below in Table 5: Table 5 McCroft's Cash Flow for the Months January to December 2020 see Each month (Jan. - Dec.) & Total Year note E Opening Balance Receipts Cash Sales 182 Credit Sales 183 Loan 4 Total Receipts 4-Nov-20 Payments Suppliers 5 Sales and marketing 6 General and administrative 7 Expansion Costs 8 Wages & Salaries 9 10 Managing Director's Salary + bonus Interest 11 Tax paid 12 Dividends 13 Total Payments Net Flow Closing balance The following assumptions apply: 1. The split of the 2020 sales by month is as follows: January February March April May June July August September October November December Total 8.00% 8.05% 8.10% 8.15% 8.20% 8.25% 8.30% 8.35% 8.40% 8.50% 8.60% 9.10% 100.00% 2. Cash Sales - assume that 90% of sales shown in the P&L each month are made in cash. 3. Credit Sales - assume that the remaining 10% of sales shown in the P&L each month are credit sales and are paid in the following month. The December 2019 credit sales were 23,500. 10 4-Nov-20 4. Assume that the loan is obtained and is paid into the bank account at the beginning of March 2020. 5. In regards to payments to suppliers (for the cost of goods sold, COGS), assume products are sold and purchased in the same month, so no stock movement and suppliers are paid in the following month. Note: December 2019 COGS = 155,000. To calculate COGS, use the percentage COGS you have calculated for your 2020 forecast P&L, i.e. COGS = 100% - Gross Profit percentage. 6. For Sales and Marketing, assume the following: Sales and Marketing costs are split equally by month; 75% of the costs are paid in the month; and the remaining 25% of costs are paid the following month. Note: December 2019 Sales and Marketing costs were 15,600. 7. For General and Admin., assume the following: The General and Admin. Costs, less the 50,000 establishment costs that were written off, are split equally by month; 50,000 establishment costs are also split equally by month from March to December and are paid in the month; 80% of the General and Admin. costs (excluding establishment costs) are paid in the month; and the remaining 20% of the General and Admin. costs (excluding establishment costs) are paid in the following month. Note: December 2019 General and Admin. costs were 26,000. 8. The Expansion costs of 400,000 are incurred and paid equally per month, starting in March 9. Wages and Salaries are paid in the month incurred. ere written off, are split equally by month; 50,000 establishment costs are also split equally by month from March to December and are paid in the month; 80% of the General and Admin. costs (excluding establishment costs) are paid in the month; and the remaining 20% of the General and Admin. costs (excluding establishment costs) are paid in the following month. Note: December 2019 General and Admin. costs were 26,000. 8. The Expansion costs of 400,000 are incurred and paid equally per month, starting in March. 9. Wages and Salaries are paid in the month incurred. 10. The Managing Director's salary is paid in the month incurred; any bonus is payable in March 11. The interest payment of 8% per year on the 300,000 loan starts in March and is paid each month. The loan capital will be repaid from 2025; therefore, it will not affect 2020 cash flows. 12. Assume that the last year's (2019) tax bill is paid in full in April 2020. 13. The following dividend payments are planned for 2020: An interim dividend of 40,000 is payable in July, and a final dividend of 50,000 is payable in December. Note: the dividends can only be paid if the closing cash balance exceeds 110,000 in the preceding month. Mr McCraft Supermarket Mr McCraft's Supermarket Projected statements of Profit Anuptions COGS: 2019 2020 Sales TOTAL 2015 DER Our Canned Good Fraren Food Meats Frate Clothing 2,955,400 5.08 2009 15.50 ON 210X 12.50 80% 100.0% Operating expenses Sanna 4.50% of total sales Sandma 55.000 General and 5.75% of total sales General and Depreciation 37,500 so son 25% 65% SON 655 EN 147.770 591000 45. 53172 620,614 16,625 236.432 152.57 10 171.97 361 640.00 11.01 244115 Sales De Dairy Canned Good Frogenfood Meat Fruit & Ve coing Total sales Coutloods sold Dell Dairy Canned Good frozen Food Mat Feu Vege Clothing Total cost of goods 2.955,400 2,051451 Growth 2020 2021 2002 Wapes and stories teine employees MMC 20.00 Previous year's PBT bonus payab 150.000 Me McCroft 90.000 Manager 46,500 Assistant 32,500 No of emplo 15 Ne of hours 40 Noof weeks 52 Hourly rate ERGO We increat 2. 102.00% 1255 5.75 73.885 295.540 33.565 145,7 110,117 240 126 156045 105.145 15471 357.030 207910 T91.117 L255.25 10211 Tarot Renovation 20% Gross pro 1,100,140 40 450,000 1.21 102 Written oli mindre 50,000 400.000 20 300.000 ven Loan requr Intrate EN Profitert Profit before 46515 ES 144 157.93) 314 31 500 141.320 0.000 370.458 2,500 354266 12:47 Operatings Sales and me Geneland Depreciation We all Managing die Total operating expenses PRIT Interest Profit before tax Taxation Profit Prowth 977719 212,191 212,391 LIZO 121,01 22.000 1080 20 81,441 52 12,428 193 Sheet1 + Abu Baba Yes = ABC Mr McCroft's Supermarket Projected statements of Profit or loss 2019 2020 2021 2022 147.770 59100 458.07 531.972 $20,634 369,425 236,432 157573 510 290 472.975 549,261 | 381.431 244116 161145 645382 500,171 580 544 677.651 403 364 2815 175.000 6912 316.413 $22.955 725.751 432.607 is Sales Del Dairy Canned Go Freren Food Meats Fruit & Vege Clothing Total sales Cost of goods sold Del Dairy Canned Goox Frozen Foods Mests Fruit & Vegel Clothing Total cost of goods sold 2.955 400 30051,451 1.226,909 1,460,00 73,885 295 540 343,565 345.782 310,317 240 126 156045 76,286 305,145 354,731 357.020 320,402 247,930 161,117 19,673 322.691 375 128 377,548 338 825 262,156 170,181 14.06 402.125 40491 361,390 281,195 112, 733 1.765 260 1.822631 1927,433 2007, 122 Gross profit 1,190,140 40.27% 1,228,819 4022 1.299,476 40.27 1.291,600 year Operating expenses Sales and me General and Depreciation Wages & Managing de Tots operating expenses PAIT interest Profit before tax Tanti Profitert 187,990 114,936 37.500 347 320 30.000 192.315 370.458 $7,500 354,266 132,478 200,211 3:30.547 57.500 361,352 90,000 210.739 143,97 $2,500 368.579 977,740 212,391 212,391 12.470 169,913 265 1.107.000 121,601 20,000 101.80 20,360 81,441 5219 1019.610 259,856 24.000 215, L2990 275,693 2400 251.690 So, 340 101,150 188,693 NET Abc Baba Yes BIU