Create a 12 month (January - December 2016) cash forecast for Smith Company in Excel using the following assumptions:
- Initial sales will be $50,000 in January. Sales will increase each month by 1%
- 40% of each month's sales will be collected that month in cash; 30% of that month's sales will be collectd in the month following the sale; 20% of that month's sales will be collected 2 months later; 8% of that month's sales will be collected 3 months later; and 2% will never be collected.
For Example: 40% of january's sales will be collected in january, 30% of january's sales will be collected in February, 20% of January's sales will be collected in March, 8% of January's sales will be collected in April, and 2% of January's sales will never be collected.
- The company will Start the begining of the year with a cash balance of $150,000.
Present the forecast of cash collections (Note: there are no spending projections to consider) for the year in a professional, clear, meaningful way for managment. The format is up to you.