Create an Excel model presenting the scenario analysis described in the text and shown in Fig 14.11. Provide overall conclusions.

Pro Forma Sensitivity Analysis*
	Assumption		Net Income Attributable to Walmart			Short-Term Borrowings
Item	Base	Revised	Base	Revised	Difference	Base	Revised	Difference
Revenue Growth	5%	10%	$16,497	$17,357	$860	$755	$397	-$358
Revenue Growth	5%	0%	$16,497	$15,638	-$859	$755	$1,112	$357
Operating Expenses	0.192	0.19	$16,497	$17,132	$635	$755	$121	-$634
Inventory Days	44.3	42.2	$16,497	$16,497	$0	$755	-$1,312	-$2,067
*dollar amounts are in millions

Text:

Suppose that Walmart's net sales increase by 10 percent over the previous year, instead of the 5% we've used in our analysis. Because of the higher forecasted sales, the consolidated net income attributable to Walmart increase by 5.2% over our base-case pro forma, going from $16,497 million to $17, 357 million. Then, after we deduct anticipated dividends, the resulting increase in retained earnings leads to a decrease in required short-term borrowings, which drop from $755 million to just $397 million. Conversely, if Walmart's net sales show no increase (rather than the 5% increase we used previously), then the consolidated net income attributable to Walmart decreases by 5.2% over our base-case pro forma, moving from $16,497 million to $15, 638 million. In turn, the lower increase in retained earnings leads to an increase in required short-term borrowings, which rise from $755 millon to $1,112 million. However, it appears that Walmart would have ample capacity to increase its borrowing.

Next, suppose Walmart is able to reduce its operating expenses by 0.2 percent, to 19.0 percent of net sales from the base-case 19.0 percent of net sales from the base-case 19.2 percent. Note that such a decrease in one year is consistent with Walmart's five year plant to reduce operating expenses as a percent of net sales by a full percentage point. The result of such a seemingly small change is substantial. Specifically, the consolidated net income attributable to Walmart increases by 3.8 percent over our base-case pro-forma, from $16,497 million to $17,132 million. The increase in retained earnings also leads to a decrease in required short-term borrowings, which drop from $755 million to just $121 million.

Finally, suppose what Walmart is able to improve its inventory management, thereby reducing its age of inventory slightly to 42.2 days- the same level as that shown in 2011-instead of 44.3 days. This change has no direct impact on the income statement. Rather, its only effect is on the short-term borrowings required, which are not only eliminated but result in an excess of available funds of $1, 312 million, which could be used for purposes such as reducing long-term debt.

The results of each of these individual changes are summarized in Figure 14.11. Of course, there are many other possible scenarios we could consider in our sensitivity analysis. The likelihood of each of these outcomes and the particular variables on which to focus should relate back to our size up analysis. For example, if we are not confident in out revenue projection- perhaps because of general economic uncertainty- then revenue, is one key variable we might adjust. Similarly, since Walmart has a stated goal of reducing operating expenses, then operating expenses is another key variable on which we might focus.