Business management problem must use excel and analytic solve
6. Action Adventures 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 months. Each month the monthly "base" sales follow a normal distri-bution with a 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 sea-sonality factor for the month, as shown in the subsequent table. Base sales in December 2018 were 6,000, with actual sales equal to (l.18)*(6,000) = 7,080, It is now January I, 2019. Cash sales typically account for about 40 percent of monthly sales, but this figure has been as low as 28 percent and as high as 48 percent in some months. The remainder of the sales are made on a 30-day Month Seasonality Factor January 0.79 February 0.88 March 0.95 April 1.05 May 1.09 June 0.84 July 0.74 August 0.98 September 1.06 October 1.1 November 1.16 December 1.18 interest-free credit basis, with full payment received one month after delivery. In December 2018, 42 percent of sales were cash sales and 58 percent were on credit. The production costs depend upon the labor and material costs. The plastics required to manufacture the action figures fluctuate in price from month to month, depending on market conditions. Because of these fluctuations, production costs can be anywhere from $6 to-$8 per unit. In addition to these variable production costs, the company incurs a fixed cost of $15,000 per month for manufacturing the action figures. The company assembles the products to order. When a batch of a particular action figure is ordered, it is immediately manufactured and shipped within a couple of days. The company utilizes eight molding machines to mold the action figures. These machines occasionally break down and require a $5,000 replacement part. Each machine requires a replacement part with a 10 percent probability each month. The company has a policy of maintaining a minimum cash balance of at least $20,000 at the end of each month. The balance at the end of December 2018 (or equivalently, at the beginning of January 2019) is $25,000. If required, the company will take out a short-term (one-month) loan to cover expenses and maintain the minimum balance. The loans must be paid back the following month with interest (using the current month's loan interest rate). For example, if March's annual interest rate is 6 percent (so 0.5 percent per month) and a $1,000 loan is taken out in March, then $1,005 is due in April. However, a new loan can be taken out. each month. Any balance remaining at the end of a month (including the minimum balance) is carried forward to the following month and also earns savings interest. For example, if the ending balance in March is $20,000 and March's savings interest is 3 percent per annum (so 0.25 percent per month), then $50 of savings interest is earned in April. Both the loan interest rate and the savings interest rate are set monthly based upon the prime rate. The loan interest rate is set at prime + 2%, while the savings interest rate is set at prime - 2%. However, the loan interest rate is capped at (can't exceed) 9 percent and the savings interest rate will never drop below 2 percent. The prime rate in December 2018 was 5 percent per annum. This rate depends upon the whims of the Federal Reserve Board. In particular, for each month there is a 70 percent chance it will stay unchanged, a 10 percent chance it will increase by 25 basis points (0.25 percent), a 10 percent chance it will decrease by 25 basis points, a 5 percent chance it will increase by 50 basis points, and a 5 percent chance it will decrease by 50 basis points. a. Formulate a simulation model on a spreadsheet to track the company's cash flows from month to month. Use Analytic Solver to simulate 1,000 trials for the year 2019. b. Adventure Toys management wants information about what the company's net worth might be at the end of 2019, includ-ing the likelihood that the net worth will exceed $0. (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 2019. Therefore, Adventure Toys management also would like information regarding the size of the maximum short-term loan that might be needed during 2019. Display the results of your simulation run from part a in the various forms that you think would be helpful to manage-ment in analyzing this issue.