Part III - B: Lee Incorporated: Based on total 2015 sales of $4,000,000, managem
ID: 2577770 • Letter: P
Question
Part III - B: Lee Incorporated: Based on total 2015 sales of $4,000,000, management has forecasted the following monthly growth in sales and monthly cash collection measures for 2016:
• Create a 12-month cash flow budget in Excel using the following assumptions:
• Annual 2015 sales of $4,000,000 with forecasted monthly growth of 2%
• 45% of each month’s sales for cash; 25% collected the following month; 20% collected 2 months later; 8% collected 3 months later; and 2% never collected
• Initial cash balance of $300,000
Assuming an initial cash balance of $300,000, management has concluded that they will achieve an ending cash balance of $31,000,000.
Part III - B: In a few sentences, evaluate management’s conclusion. (200 points
Explanation / Answer
Ending balance of cash that the company would achieve is $4396293. This implies the ending balance that the company would achieve is much more than the management's conclusion.
Annual Sales in 2015 4000000 Monthly Sales in 2015 333333.3333 Cash Budget Month 1 2 3 4 5 6 7 8 9 10 11 12 Sales 339999.66 346799.6532 353735.6463 360810.359 368026.6 375387.1 382894.8 390552.74 398363.79 406331.067 414457.6883 422746.842 Cash Sales (45%) 152999.847 156059.8439 159181.0408 162364.662 165612 168924.19 172302.7 175748.73 179263.71 182848.9802 186505.9598 190236.079 Realisations from credit Sales: 25% in the following month 84999.915 86699.9133 88433.9116 90202.59 92006.642 93846.77 95723.71 97638.184 99590.94779 101582.7668 103614.422 20% in two months 67999.932 69359.9306 70747.13 72162.072 73605.31 75077.42 76578.968 78110.54729 79672.75824 81266.2134 8% in three months 27199.9728 27743.97 28298.852 28864.83 29442.125 30030.968 30631.58717 31244.21892 31869.1033 Total Cash Received 152999.847 241059.7589 313880.8861 347358.477 354305.6 361391.76 368619.6 375991.99 383511.83 391182.0624 399005.7037 406985.818 Opening Balance 300000 452999.847 694059.6059 1007940.49 1355299 1709604.6 2070996 2439616 2815608 3199119.78 3590301.843 3989307.55 Ending Balance 452999.847 694059.6059 1007940.492 1355298.97 1709605 2070996.4 2439616 2815608 3199119.8 3590301.843 3989307.546 4396293.36