The Adventure Toys Company manufactures a popular line of action figures and distributes them to toy stores at the wholesale price of $10 per unit. Demand for the action figures is seasonal, with the highest sales occurring before Christmas and during the spring. The lowest sales occur during the summer and winter (post-Christmas) months.
Each month the monthly €œbase€ sales follow a normal distribution with mean equal to the previous month€™s actual €œbase€ sales and with a standard deviation of 500 units. The actual sales in any month are the monthly base sales multiplied by the seasonality factor for the month, as shown in the table below. Base sales in December 2014 were 6,000, with actual sales equal to (1.18) (6,000) = 7,080. It is now January 1, 2015.

(a) Formulate a simulation model on a spreadsheet to track the company€™s cash flows from month to month. Indicate the probability distributions (both the type and the parameters) for the assumption cells directly on the spreadsheet. Simulate 1,000 trials for the year 2015, and paste your results in the spreadsheet.
(b) Adventure Toys management wants information about what the company€™s net worth might be at the end of 2015, including the likelihood that the net worth will exceed zero. (The net worth is defined here as the ending cash balance plus savings interest and account receivables minus any loans and interest due.) Display the results of your simulation run from part (a) in the various forms that you think would be helpful to management in analyzing this issue.
(c) Arrangements need to be made to obtain a specific credit limit from the bank for the short-term loans that might be needed during 2015. Therefore, Adventure Toys management also would like information regarding the size of the largest short-term loan that might be needed during 2015. Display the results of your simulation run from part (a) in the various forms that you think would be helpful to management in analyzing this issue.

Transcribed Image Text:

Month Seasonality Factor Month Seasonality Factor anuary February March April May June 0.79 0.88 0.95 1.05 1.09 0.84 July August September October November December 0.74 0.98 1.06 1.10 1.16