After studying the results of the analysis conducted to this point (the "base case" model presented in class), S.T. Semoran realized that a lack of cash was possibly causing some problems. Just last week S.T. had approached both BenderMet and Flexo about changing the terms of sale. Both vendors were quite interested, until they discovered that S.T. was hoping for later payment, rather than earlier payment. S.T. was unable to persuade either supplier to provide more generous conditions for paying for the SlimmerGymner units.
The next attempt for S.T. was with their banker at the Carrion Imperious Bank of Carnage (CIBC). While Novana had reasonably good relations with the bank, the bank viewed Novana more as a depositor than as a borrower. S.T. hoped that presenting the banker with projected cash flows for the next four months would be a good way to raise the question of a loan (S.T. is clearly an optimist). In the past, S.T. had not known how much credit to request - the cash flow projections might help solve that problem. S.T. decided that any negotiations with the banker would keep the number of ads at or below one hundred (bankers are not happy dealing with "What If" scenarios that are far outside the range experience - mind you, bankers are never really happy regardless of circumstances…).
After some discussions with CIBC, the banker proposed a $40,000 line-of-credit type loan. In order to secure access to the loan, Novana would have to pay CIBC $1,000 in January regardless of whether or not the credit was actually used. In addition, interest would be charged at the rate of 1.5% monthly. The banker explained the procedure which would be used for determining each month's interest: "Each month, the loan balance before interest will be computed as the beginning loan balance, plus the amount borrowed that month, less the amount repaid that month. Then, the loan interest will be determined using this balance and the interest rate. Finally, the ending loan balance will be determined by adding the loan interest to the loan balance before interest. This ending loan balance becomes next month's beginning loan balance." [Note: If the loan details are to be added to the Novana model, then this "loan section" would include all of the bold titles in the preceding description.]
S.T. thought that the additional cash from this credit loan would really enhance the profitability of the SlimmerGymner operations. S.T. knew that the bank would require that enough of the loan balance and/or interest must be paid each month so that the loan balance at the end of each month (after interest had been added) never exceeded the $40,000 limit and could never become a "negative balance". There would be no loan outstanding at the start of the time period, and CIBC would require that the entire loan - including all applicable interest charges - would have to be paid off by the end of the time period.
Incorporate the above changes into the base case Novana model shown in class. This addition will require two new sets of "decisions" for Novana to make each month: (i) how much to borrow, and (ii) how much to repay. After these decisions have been made for each month, the values found are used in conjunction with the beginning balance and interest charged to determine the ending loan balance. The amount borrowed in the month will be added to the cash received for that month, and the amount repaid in the month will be added to the cash paid out for that month.
What effect does this credit loan have for Novana in comparison to the base solution? Discuss fully.
Should Novana implement the plan suggested by the spreadsheet model? Discuss fully.
Question 2 - Using Simulation to Support & Evaluate Novana's Decisions
S.T. realized that if Novana were to implement the optimal plan found in Question 1, they would not really end up with the results exactly like those projected by the spreadsheet model. This plan was based upon a 50% split between credit card and COD sales, and upon the perfect ability to forecast sales for any number of ads scheduled. What would happen if Novana experienced variation in the sales split (credit card or COD) and in the effectiveness of ads? Assume that Novana will actually implement the plan developed in Question 1. We want to evaluate how effective this plan will be in the face of uncertainty by performing a Monte Carlo simulation of the possible outcomes.
The data for the split between credit card and COD sales had been collected from the actual monthly splits in each of the past twelve months. Although the average value of these monthly splits is 50%, the actual values have ranged between 45% and 56%. S.T. assumed that these values are representative of the types of splits likely to continue into the future and that each month's split is equally likely to be any value within the observed range. To incorporate this uncertainty, add a new row to your spreadsheet model which randomly generates a split for each month - ensuring that other formulas that have referenced the Credit Card Percentage data cell, now reference the new cells in each month (Note: you should delete the old data cell). Before continuing with the question, you may want to hit the F9 key a few times to see what effect this change has on the values in your model.
We have also learned in class that the formula used to generate the Units Sold in each month had been found using regression analysis on data from the previous 24 months. Although the regression equation provides a "value" estimate for the number of units sold each month, the regression report also provides substantial information on the uncertainty of this prediction (i.e. the forecast error). The "Standard Error" (STD) in the "Regression Statistics" section can be used to estimate each month's forecast error. Under the assumptions necessary for regression, the forecast errors about a predicted value estimate must be normally distributed. Hence, add another new row to your spreadsheet model which randomly generates the forecast error in each month. Each monthly error value is to be generated from a normal distribution with a mean of 0 and a standard deviation of STD [i.e. by using the Excel formula "=Norminv(rand(),0,STD)"; thus, half the time the error will be a positive value, half the time it will be a negative value. FYI, STD = 178.76]. Now change the Units Sold cells in each month by adding the respective monthly forecast error term to the monthly forecasted value formula.
Hit the F9 key a few times to see what effect this change has on the values in your model. You should observe that sometimes the units sold value becomes a negative number (which not be too realistic). Hence, further modify the Units Sold cells using the formula, "=Max(Forecast Value Formula + Forecast Error Term,0)", to prevent negative sales from ever occurring [Note: You have to supply the actual formula for "Forecast Value Formula"]. You might now also become aware of the fact that even after the above changes, there are months in which inventory levels can still be negative. We could make further modifications to our model to prevent this. However, we will permit negative inventory under the assumption that these units represent a backorder position.
After incorporating the uncertainty in units sold and in the credit card percentage split, S.T. was eager (as are your, of course) to investigate the impact of uncertainty upon Novana's "optimal plan" developed in Question 1. Three important factors can be investigated with this model:
How much uncertainty is there in the profit, as indicated by the May ending cash balance? What are the chances that Novana will be able to keep inventory levels from becoming negative? What are the chances that Novana will be able to keep a minimum ending monthly cash balance of $10,000?
These questions are among those that can be answered using the Monte Carlo simulation method to perform a number of replications of the model's solution. For each of these replications, we would need to keep track of: the May ending cash balance, the lowest inventory during the four month planning horizon, and lowest cash balance during the four-month planning horizon. In order to calculate the latter measures, we must add two more reporting cells to our spreadsheet model:
=Min(Ending Inventory[Jan]:Ending Inventory[April]) and
=Min(Ending Cash Balance[Jan]: Ending Cash Balance [April]) .
Now, the above questions can be analyzed using a 1-way data table with three "result" columns referencing the three cells of interest.
Run a Monte Carlo simulation of the optimal plan found in Question 1, recording the three output variables of interest (perhaps using 1000 repetitions). Construct a cumulative probability plot of each variable.
Suppose that Novana is basing its plans on the results from Question 1. Using the output from part (a) and relevant probability values, discuss how sound Novana's business plan actually is.
Suppose now that YOU are the "Friendly Banker" evaluating Novana's business plan. Discuss what conclusions you would reach with respect to extending credit to Novana.
