You have been asked to simulate the cash inflows to a toy company for the next year. Monthly sales are independent random variables. Mean sales for the months January to March and October to December are

$80,000, and mean sales for the months April to September are $120,000. The standard deviation for each month’s sales is 20% of the month’s mean sales.

We model the method used to collect monthly sales as follows:

■ During each month, a certain fraction of new sales are collected. All new sales not collected become one month overdue.

■ During each month, a certain fraction of one-month overdue sales is collected. The remainder becomes two months overdue.
■ During each month, a certain fraction of twomonth overdue sales is collected. The remainder is written off as bad debt.
You are given the information in the file P12_40.xlsx from some past months. Using this information, build a simulation model that generates the total cash inflow for each month. Develop a simple forecasting model and build the error of your forecasting model into the simulation. Assuming that there are $120,000 of onemonth-
old sales outstanding and $140,000 of twomonth-
old sales outstanding during January, you are 95% sure that total cash inflow for the year will be between what two values?