• 028 11/11/2014

    In a financial model it is common to see measures of profitability averaged historically and projected forward. This is, after all, the manner in which building a five-year projection is presented in "Integrating Financial Statements" on this website. As an introduction to financial modeling this is a suitable approach, but as you graduate from projections built off of fictional historical data to real-world modeling exercises, attention should be paid to the detail that comprises expenses.

    When the detail is incorporated, many line items are likely to be projected in line with historical averages as well. But there are expense categories for which a historical average may not be appropriate, and the clever ways these items are projected can be quite interesting.

    Analysts at Wells Fargo recently employed a creative method to determine the potential impact of a drop in the price of cotton on a select group of retailers. The WSJ blog MoneyBeat reports:

    Wells Fargo weighed a short-sleeved graphic T-shirt, a hoodie and a pair of denim jeans from Abercrombie & Fitch, Aeropostale, American Eagle Outfitters and Gap to figure out the value of the cotton in each. A pair of American Eagle jeans, for example, weighs 1.4 pounds and sells for $34.30. At 63-cents-a-pound, the value of the cotton is 86 cents, versus $1.04 a year ago when cotton cost 76 cents a pound — a savings of 18 cents.

    Cheaper cotton should generate an average benefit to gross margin, across all three clothing items, of about 0.58 percentage points, the bank found. But the bottom-line gain wouldn’t be as large. The retailers sell non-cotton items. They face rising costs for labor, transportation and safety standards. And many companies buy fabric, not raw cotton. Any of these factors could easily wipe out the benefit.

    As the excerpt points out, another important variable to consider is the potential impact to the bottom line. If you were to take a look at all of the line items that comprise Cost of Goods Sold (COGS), you would want to look for line items that might fluctuate and stand to make an impact.

    Naturally the objective would be to focus on all large expense categories, but it is also important to keep overall profitability in mind. The narrower the margins the more important this detail becomes. Half a percent may not mean much if a company operates at 30% EBITDA margins, but at 5% EBITDA margins your profitability swings 10%.

    Click the image on the right for the full article.


  • 027 10/16/2014 How do You Determine the Minimum Cash Balance?

    Note: I thought it would be a good idea to start posting answers to questions I receive via email. I try to answer all of them but occassionally my job gets in the way...

    This question refers to the video titled "Integrating Financial Statements."

    In the model it is difficult to understand the need for a minimum cash balance because we are projecting the financial statements of a fictional business. But the minimum cash balance would be maintained by the company's management team to avoid shortfalls resulting from differences in cash inflows and cash outflows. This is a bit abstract so I thought I would attempt to provide some context…

    Consider the following: Let’s say you own a business subject to seasonality. Your company manufactures inflatable pool toys. More precisely, alligator pool floats. Every year your sales start to trend higher in April and May as customers get ready for summer. Anticipating this need you increase purchases of raw material (inventory) in the first quarter, which consumes cash. 

    Now that you have purchased the raw material and started manufacturing in anticipation of greater sales, consider a scenario where a summer blockbuster is released depicting alligators as evil – like “JAWS” but now you’re not safe anywhere (because they have legs). As a result everyone is terrified of alligators, even inflatable ones, and sales drop below expectations. With a large percentage of your cash tied up in inventory, and sales trickling in, you will still require cash to cover expenses (salaries, rent, etc) until sales resume.

    If you want to show this in the model, assume that the periods are months instead of years and increase Inventory Days from period to period, but keep sales constant. To reflect declining sales you could also reduce revenue in each period. What you will see in the model is that the revolver (line of credit) grows to compensate for the cash shortfall. In reality, however, a limit would be imposed on credit available through the revolver (imposed by the lender), and once this is exhausted you will have to rely on cash. Modeling several scenarios and deciding which are likely helps establish the minimum cash balance to maintain at all times.

    If that is too specific... it's wise to keep cash on hand (and/or access to line of credit) to manage fluctuations in working capital, unpredictable sales and to survive disasters (other needs exist, but my attempt to keep this short is failing...). As a responsible manager you would attempt to predict what these anticipated or unanticipated cash needs might be, and maintain appropriate levels of cash for those scenarios.

    Finally, if you are looking for a metric to answer this question, it is not uncommon to hear the cash need (or line of credit need) referred to as a number of days of sales.


  • 026 10/09/2014 What Exactly is Capital?

    I can no longer help but post simple descriptions when I stumble upon them in periodicals I read regularly. This explanation of capital and the helpful image "How to Make a Capital Cushion" (above) come from Bloomberg Businessweek: 

    Regulators don’t want banks to go bust in the next financial crisis, or even come close. So they’re requiring them to carry thicker safety cushions of “capital,” that miraculous loss-absorbing material.

    So what exactly is capital? Sometimes it’s described as a rainy-day fund, which is wrong. More often it’s characterized as something banks “hold,” which can make it sound like a pile of money that has to be set aside so it can’t be lent out for a profit. That’s not right either.

    Put simply, capital is nothing more than the difference between what banks own (assets) and what they owe (liabilities). Assets include loans the bank makes, which produce interest income, along with bonds, deposits at the Fed, and cash in the vault. Liabilities are other people’s money that banks are allowed to play with, including deposits by their customers. A bank with a large capital cushion could sell its assets, return all its depositors’ money and pay off other borrowings, and have money left over.

    Full article: LINK



Models are:
A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above



*Answers a, b, c, d, e, f and g are all correct.