The Financial Modeling Process

[RTfloydian]

I participated in the 2 day long University Bootcamp at my school. It was a valuable class, but the brevity of the program led some of us in the class to blindly follow the instructor at times. I am attempting to redo the WMT financial model on my own so that I can reinforce the skills I learned in the class, but I am having trouble completing it.

After completing the BS and IS(excluding the debt sweep items) I am stuck on the CFS and the Debt Sweep. Net Income is the first line item in the CFS, but the number is incorrect because of the incomplete debt sweep line items from the IS, which makes the CFO incorrect as well. Consequently, I can't remember how we arrived at the Cash Available/(Required) Before Debt necessary to complete the circular reference of the financial model.

I need some guidance on what steps to take to properly complete the WMT template.

[wsthost]

The Cash Available / (Required) Before Debt is calculated by taking the summation of Cash From Operating Activities (CFO); Cash From Investing Activities (CFI); and Cash From Financing Activities (CFF). In the case of our model, CFO, CFI, and CFF should equal $17274; (17,500); and (6,000), respectively, totaling (6226) for Cash Available / (Required) Before Debt.

Now, Cash Available / (Required) Before Debt plus the Beginning Cash on Balance Sheet less Minimum Cash Balance less Mandatory Debt Repayment for 2006 will equal to our Cash Before Discretionary Debt Repayment/ (Borrowing). If this number is negative, it will increase the revolver in our debt sweep. However, if this number was positive, it would be used to pay down any revolver balance in the debt sweep.

In the case of our model, Cash Before Discretionary Debt Repayment/ (Borrowing) should equal (10407) for 2006. The Company used their revolver to pay this balance, hence incurring future interest, which flows through to our pro forma income statement numbers.

Now remember, the revolver is just one form of debt, and is usually the cheapest because of its priority of repayment. We must also include the other tranches of debt, as well as commercial paper. These balances all incur interest expense which is calculated on our interest schedule below the debt schedule, and eventually carried over to our income statement to project our future interest expense and interest income (depending on our cash balance, and the interest rate earned for cash).

[RTfloydian]

What you are saying makes sense to me. However, the difficulty I am having with the CFS results from the value of Net Income that is necessary to arrive at the CFO. In order to correctly compute CFO, Net Income has to equal 11,837. However, due to the fact that Interest Income and Interest Expense are needed to compute Net Income, and Net Income is needed to compute CFO, how is it possible to accurately arrive at the Cash Available/(Required) Before Debt value of (6,226)?

[wsthost]

You must complete the debt sweep first, then calculate you're interest expense/income. It will be the last step in the model. At that point all the numbers should tie.

Recall that when first constructing the IS, interest expense/income are left blank. THEN that is the very LAST thing you do AFTER finishing the Debt Sweep.
Refer back to the Ciricular Reference exhibit (available on your online self-study account) for a refresher on how that works.