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...

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?