Explore BrainMass

ChangePoint AR cash collections, cash forecasting

The CEO wants a cash flow forecast based on Changepoint AR invoicing information. The new system does not have a programmed report for this, so until a custom report is written, you will need to use an Excel spreadsheet model to forecast incoming cash.

Of the company's actual sales, all are credit sales. None are cash on the spot. Of the credit sales, 36% are collected one month later, 42% two months later,and 20% three months later. The company has bad debt of 2%.

1) Based on the pattern of collections above, forecast cash collections for October 2009 through March 2010. You will need to complete the sales (invoicing) amounts for October through January. The amounts in italic in February and March are estimates provided for you.

2) Total the cash inflows for each month and present the amounts collected for each quarter.

3) Create a graphical presentation of this data that would be helpful to the CEO and CFO. They should be able to follow it without asking you, so map (document) your model as needed.

Please see attached file for data


Solution Summary

Your tutorial computes cash collections in each month (presumes sales start in Oct and none prior). A graph is created form this data to convey the "stagger" in collections for each month's sales and the total collections in each month.