This chapter will reinforce and extend our use of the finance tools we introduced in the previous two chapters. We will do so by examining the effect of seasonality on a firm’s financials. As a consequence, our evaluation interval will not be year to year, but rather month to month.

It is common to use annual reports when analyzing a firm’s financials. The annual Income Statement represents the previous 12 months of activity, and the Balance Sheet reflects financial values at the fiscal year-end. Annual Income Statements and Balance Sheets provide a reasonable starting point to analyze a firm. In addition, they are sufficient if the firm’s sales, as well as assets and liabilities, are fairly constant throughout the year (as we assumed in the last chapter for PIPES). However, many firms have large variations in their sales, assets, and liabilities during the year due to seasonality. For example, the Christmas gift season is typically the largest for retailers, and thus sales are higher during this period while inventory levels are largest just prior and lowest just after. These, of course, have a cascading impact on receivables, payables, bank loans, and so on. If PIPES was located in the Midwest, and a large portion of sales was dependent on contractors, there would also probably be seasonality with more building taking place during the summer months.

To reinforce our financial tools and to illustrate the effect of seasonality, let us extend our analysis of PIPES but assume the firm is located in a northern climate with a seasonal variation in sales as shown in Table 4.1. Note we have set sales lowest in December, January, and February and highest in June, July, and August. The seasonal variation in sales results in 60% of sales occurring during the peak building season (April–September) and in 40% of sales occurring during the off period (October–March).

We assume that the year-end numbers, that is, the December 31 annual Income Statement and Balance Sheet, are identical to those in the last chapter. Thus, total sales and profits are the same; what we are introducing is variation in the monthly operations.

Monthly Pro Forma Income Statements

To compute PIPES’s projected funding needs for 2013, but on a monthly basis as opposed to a yearly basis, as in Chapter 3, we begin with the 2012 actual Income Statement and Balance Sheet. We then create Table 4.2, which gives monthly pro forma Income Statements for 2013.

Table 4.2 is created by using the monthly sales projections in Table 4.1 and by using financial ratios based on those determined in Chapter 2. For our Income Statements, the ratios will be constant over time, while for our Balance Sheets (discussed below) some ratios will be adjusted based on our assumptions about monthly activity. For example, we will keep Cost of Goods Sold (COGS) at 75.46% of sales (77% less the 2% reduction for prompt payment introduced in Chapter 3) for each month. Why? Because we are assuming that PIPES maintains the same mark-up over purchase prices when setting selling prices throughout the year. If true, then the monthly ratio of COGS/sales is the same as the annual ratio.

We also set monthly Selling, General and Administrative expenses (SG&A) as 15.5% of annual sales (the same as in the annual ratio) divided by 12. Thus, the monthly amount of SG&A is assumed to be constant month by month, rather than varying with monthly sales. Why? Because here your authors are assuming PIPES keeps staff levels and other SG&A expenses constant throughout the year. This makes the monthly expense constant each month (and the ratio of operating expenses/sales varies month to month).

These two assumptions seem reasonable to your authors. However, as we mentioned several times in Chapter 3, in finance there is more than one correct answer. Assuming COGS will remain as a constant percentage of sales each month seems reasonable, especially for a first pass. Likewise, the assumption that SG&A is a constant amount each month (rather than a constant percent of sales) also seems reasonable. We could assume that SG&A varies with sales as the firm hires and fires staff throughout the year, but your authors don’t feel this is as realistic. Someone with better knowledge of the internal operations of PIPES might feel differently. An important point: If you work for PIPES and are doing the forecast for the bank, you would have that knowledge and be able to forecast the monthly ratios more accurately. This is also true if you are an analyst for this industry and are doing a valuation using monthly pro formas.

Monthly taxes are assumed to be 35% of profit before tax. Since taxes are a constant function of profits before taxes, there is no reason to change the assumed rate.

As before, the interest expense will be an iterative item, which we initially set at the estimated annual amount of $47,000/year (taken from Table 3.3a in Chapter 3) or $3,917/month ($47,000/12). Later, after we create our initial Balance Sheet, we will adjust interest expense to reflect monthly changes in the amount borrowed.

A preliminary review of the monthly pro forma Income Statements in Table 4.2 shows projected sales vary from a low of $110,000 in January to a high of $302,500 in June, July, and August 2013. Monthly net income also varies from a loss of $8,090 to a profit of $22,610 over the same months. We should note here that annual sales (or, the sum of monthly sales) remains at $2,750,000. For the moment total net profit for the year remains at $131,010 (the same as our pro forma numbers in Chapter 3). The sum of the monthly COGS, SG&A, interest expense, and taxes are all equal to the annual amounts as well. Thus, if we compare the sums of the pro forma monthly Income Statements for 2013, all items are identical to the year-end December 31, 2013, pro forma annual Income Statement from Chapter 3 (Table 3.3a in that chapter).

Note, we know from Chapter 3 that our pro forma Income Statement will affect our pro forma Balance Sheet by determining the amount of net worth and, by consequence, the amount of the bank loan. We also know that the amount of the bank loan will affect the amount of interest paid and thus net income. So even though we have calculated preliminary pro forma Income Statements for each month, we must, in fact, iterate monthly between the January Income Statement and Balance Sheet and then the February Income Statement and Balance Sheet, and so on. We will do this later after we determine some of our monthly Balance Sheet items. (As we will see, these iterations will change our total interest expense for the year and net profit as well.)

Monthly Pro Forma Balance Sheets

Once the monthly pro forma Income Statements are generated, we now proceed to the 2013 pro forma monthly Balance Sheets. We calculate the monthly pro forma Balance Sheets in Table 4.8 similarly to how we computed the monthly pro forma Income Statements. That is, we think through each annual ratio and make an assumption about how it varies monthly.

Just as with an annual pro forma Balance Sheet, cash and bank debt are plug figures and are normally determined at the end of the process. In any particular month, a firm can have either a positive cash balance (indicating no additional required financing) or a negative cash balance (indicating the amount of financing required).

We will assume the minimum cash balance at the end of each month is $45,000. This is the same as our annual pro forma number from Chapter 3.¹ This means that at the end of each month PIPES will either have a balance above $45,000 and no short-term bank debt, or a balance of exactly $45,000 and short-term bank debt.

We set accounts receivable equal to the current month sales plus 17 days (or 17/30 = 57%) of the prior month’s sales. Huh? Where did this come from? Let’s back up and reexamine our annual Balance Sheet. In Chapter 2, we divided the 2012 year-end receivables of $211,000 by annual sales of $2.2 million and found that receivables are 9.59% of annual sales or 35 days’ sales (receivables/average daily sales or annual sales/365). This is for the year-end Balance Sheet however.

Note that with monthly pro formas and no seasonality, days receivables will not change and the monthly ratio of receivables to sales will be 12 times the annual average. Without seasonality, pro forma December sales will be $183,333 ($2,200,000/12) rather than the $110,000 as in our monthly pro forma above. Thus, receivables to sales for December 2012 would be 115% ($211,000/$183,333 or 9.59% * 12). The number of monthly days receivables will remain at 34.5 ($211,000/($183,333/30).²

Let’s slow down a second and say this again another way. If sales are constant over the year, then the month-end ratio of any given Balance Sheet item to monthly sales will equal the year-end ratio of the Balance Sheet item to yearly sales times a factor of 12 (i.e., 115% = 9.59% * 12). The days of sales for the Balance Sheet items remains the same with no seasonality, whether we use monthly or annual sales data (other than slight rounding errors from using a month of 30 days and a year of 365 days. Some analysts use a 360-day year).

So far, so good. With seasonality, however, we get fluctuations in the monthly sales, which cause fluctuations in the monthly ratios of Balance Sheet items to monthly sales. When forecasting pro forma receivables, we are trying to determine how long it takes PIPES to collect—how many days from a sale until payment is received. If receivables on December 31 total $211,000, and sales in December were $110,000, then it appears all of December sales remain uncollected as well as $101,000 of sales from November.

Dividing the $101,000 of uncollected sales from November by the average November daily sales figure of $5,867 ($176,000/30) indicates that it took all of December (remember, for convenience we set each month to 30 days) and 17 days in November ($101,000/$5,867) for a collection period of 47 days (compared to a computation of 35 days calculated using annual figures). Basically, to get our total year-end receivables of $211,000 we have to sum 47 days of prior sales (so add the $110,000 sales from December with $101,000 of the total $176,000 sales from November).

What we are going to assume now is that the number of days of receivables stays constant at 47 days throughout the year.³ That is, we are going to assume it takes just as long to collect the receivables on a day of sales in the winter as in the summer. Thus, the monthly pro formas are no longer a constant percentage of monthly sales and can’t be easily used to derive month-end receivables. Rather, we use a fixed number of days receivables, which at year-end 2012 is 47 days. In Table 4.3, we use monthly pro forma receivables of 47 days of prior sales (actual and pro forma).

For January 2013, this number is $172,330:

• 100% of January 2013 plus 17 days of December 2012 sales
• 100% * $110,000 + 17/30 * $110,000 = $110,000 + 62,330 = $172,330

For February 2013, estimated receivables are $227,330:

• 100% of February sales plus 17 days of January sales
• 100% * $165,000 + 17/30 * $110,000 = $165,000 + 62,330 = $227,300

Table 4.3 shows the amount of receivables calculated this way for each month.

This analysis shows how, by including seasonality, we increase our understanding of a firm’s operations. We now see that our initial estimate of how long it took PIPES to collect receivables (35 days) was inaccurate (as it is actually 47 days). Computations using year-end numbers assume sales are constant over the year. If this is not true, then the actual ratios might be dramatically different. The greater the seasonality (the change, or spike, from one month to the next), the greater the difference.

Next up is inventory. Would you expect inventory to remain constant over the year if sales vary over the year? No, why would you? We don’t expect the amount of physical inventory to remain constant. If PIPES has seasonal sales, it makes sense that the firm would hold higher inventory in the months preceding higher sales, and lower inventory in the months preceding slow sales periods. Do we expect the ratio of inventory to sales to remain constant? Probably not inventory to past sales, but perhaps inventory to expected future sales. A well-run business would increase physical inventory before the busy selling season and decrease it before the slow selling season. Here, your authors are going to assume that inventory stays constant as days of projected (future) sales. So, let’s do it.

Conceptually, it seems natural to relate inventory to future sales. However, as we showed in the last chapter, the equation for ending inventory is: Opening Inventory plus Purchases less COGS. For this reason, days inventory is normally computed as a function of COGS, not sales (since in our PIPES example, COGS is a constant percentage of sales, we can easily translate from days COGS to days sales). Below we will use COGS in our example.

In December 2012, inventory is $418,000. This is equal to 100% of pro forma COGS for the next three months and part of the fourth.

If we keep inventory constant at 98 days of future COGS, inventory at the end of January 2013 will be $511,870:

Likewise, for February, inventory will be $600,410 as follows:

Table 4.4 calculates similar pro forma amounts for each month.⁴

Turning next to net property, plant, and equipment (PP&E), we assumed in Chapter 3 an increase of 5% by 2013. For simplicity, we assume the increase is on the first day of the year.⁵ Thus, PP&E grows from $350,000 at the end of December 2012 to $368,000 on January 1, 2013, and remains constant through December 31, 2013.⁶

We turn now to the liabilities and net worth side of the Balance Sheet.

The bank debt, as noted in Chapter 3, is a short-term loan and is a plug number. Let us make a quick point about the interest rate, and then we will return to this item below. Its level also determines the amount of interest expense on the Income Statement, which in turn determines the amount of net income for each month. We assume the annual interest rate is set at 7%. This is the same rate as in Chapter 3. The interest rate for long-term debt is also identical with Chapter 3 at 9% as assumed.

In Table 4.2, our initial pro forma monthly Income Statement, we set our monthly interest expense at $3,917—the estimated annual amount of $47,000 (from Table 3.3a in Chapter 3) divided by 12. Here we will make the simplifying assumption that the interest expense for the month is based on the prior month-end’s outstanding debt. Our 7% annual interest rate translates to a simple monthly rate of 0.5833% (7%/12), and our 9% annual rate translates to a simple monthly rate of 0.75% (9%/12).⁷ For January 2013, the pro forma interest expense is $2,792 (the December 31, 2012, bank debt of $350,000 times 0.5833% = $2,042 plus the total long-term debt of $100,000 * 0.75% = $750). For February and other months, shown below, we first have to compute the ending balances of debt in the prior month.

Accounts payable are set at 10 days of purchases (using the assumption from Chapter 3, which captures supplier discounts). How do we calculate monthly purchases? As noted in the prior chapter:

To solve for purchases, we can rearrange the formula as:

The prior month’s ending inventory is the current month’s opening inventory, and we have already derived the monthly inventory and COGS above. This allows us to calculate purchases for each month. Payment in 10 days is simply the prior month’s purchases divided by 3 (using 30 days/month). This is shown in Table 4.5.

Previously, in our annual pro formas, we held accrued liabilities at $25,000. One of the most important components of accrued liabilities is taxes payable. By holding accrued liabilities constant, we implicitly assumed that the amount owed in taxes also stayed constant year to year. This assumption probably did not hold up in reality. With monthly pro formas, we will be more realistic. Taxes accrue on a monthly basis and are paid down quarterly (see the box on income taxes below). So, take a deep breath and clear your head; we are now going to teach you something new about not only pro formas, but taxes as well.

In our monthly pro forma Balance Sheets, our accruals will change every month by the amount of taxes due in that month. They will also change in March, June, September, and December by the payment of estimated taxes. Finally, in March there will be an additional payment (or refund) that nets the taxes owed for the previous year with the amount paid in estimated taxes (i.e., the quarterly installments) during that year.

PIPES ended 2012 with accrued liabilities of $25,000. We assume this includes taxes payable of $9,000 (which will be paid in March 2013) plus $16,000 in other accrued liabilities.

PIPES will also pay estimated quarterly taxes of $11,000 in March, June, September, and December of 2013 (using the 2012 total tax expense of $44,000 as the basis). The quarterly payments made during the year are based on the total actual tax expense from the prior year.⁸ This means the monthly accruals will increase each month by the current month’s tax expense and fall by $20,000 in March 2013 (the $9,000 owed from 2012 plus the $11,000 estimated quarterly payment) and by $11,000 in June, September, and December 2013.

Note, just as there is a circular relationship between interest paid on the monthly Income Statement and bank debt on the monthly Balance Sheet, tax accruals are also part of this relationship. This is because taxes due each month are dependent on income before taxes, which is dependent on interest paid, and so on. Thus to calculate our monthly accruals below, we must also calculate our monthly interest expense, and to calculate our monthly interest expense, we must calculate our monthly bank debt, and to calculate our monthly bank debt, we must calculate our monthly net income.

As we noted in Chapter 3, modern spreadsheet programs can solve this simultaneously. However, here we will do it iteratively to demonstrate how it works rather than just imposing a “black box” solution. Important: As noted above, we assume the interest for a given month is based on the prior month’s ending bank debt. (While this is not a simultaneous solution, it gives a result that is close to the simultaneous solution).

As can be seen in Table 4.6 below,⁹ accruals at the end of January 2013 will be $21,040 (the $25,000 at the end of December 2012 less a $3,960 tax recovery. The tax recovery is due to the fact that PIPES has a monthly loss before tax in January, so the tax expense is a negative amount). The accrued liabilities are $21,290 at the end of February (the $21,040 at the end of January plus the February tax expense of $250), and $6,000 at the end of March (the $21,290 at the end of February plus the March tax expense of $4,710 less the $9,000 payment for taxes owed from 2012 and less the $11,000 quarterly installment for 2013). Forwarding to the end of December 2013, accruals are $37,170, which can be similarly calculated (the balance from November of $50,140 less the December tax recovery of $1,970 less the quarterly installment of $11,000). The year-end balance of $37,170 can also be computed as the $21,170 balance of taxes owed for 2013 (the tax expense for 2013 of $65,170 minus the four quarterly installments of $11,000 each totaling $44,000) plus the $16,000 constant amount for other accruals.

Continuing down the Balance Sheet, long-term debt declines with annual repayments of $10,000. As with PP&E, the question is when the payments are made. Here your authors chose to assume, for simplicity, the payments are made twice a year with $5,000 being repaid on June 30 and another $5,000 on December 31.¹⁰

Shareholders’ equity is comprised of contributed capital, which (as in Chapter 3) is assumed not to change, and retained earnings. As PIPES pays no dividends, retained earnings change monthly by the monthly profit or loss. For example, retained earnings at the end of December 2012 are $279,000 and since PIPES has a loss in January 2013 of $7,360, the retained earnings at the end of January 2013 are $271,640. We will discuss dividends and dividend policy in Chapter 11.

Now it is time to make our Balance Sheet balance and fill in the two plugs, the cash balance and the bank loan. If PIPES’s liabilities and net worth are greater than our assets, we assume the difference is held in cash. If PIPES’s assets are greater than our liabilities and net worth in any month, the difference has to be financed. And here it is assumed it is financed with bank debt.

For each month during 2013, liabilities and net worth are less than assets, so cash remains at the minimum balance of $45,000, and the bank loan is a positive balance. In January 2013, the bank loan necessary to finance the assets (and to make our Balance Sheet balance) is $598,930. In February, it is $729,690, and it continues to rise to $1,014,920 in June before falling back to $554,840 at the end of the year.

A Different Picture of the Firm

So, how does the annual pro forma Balance Sheet produced in Chapter 3 compare to the monthly pro forma Balance Sheets in Table 4.8? Which is a more accurate prediction of PIPES’s required bank line of credit? When we introduce seasonality, it causes a large change in our perception of the firm and in our estimate of the line of credit we need to secure with the bank. As is seen in Table 4.8, the monthly pro formas show that PIPES requires a maximum line of credit of $1,015,000 at the end of June 2013 before it falls back to $555,000 at year’s end. The reason for this large seasonal difference is that the bank line of credit primarily finances receivables and inventory. Receivables peak in July and August (during the busiest sales months), while inventory peaks in May (just before). In December, receivables and inventory are much lower due to the lower monthly sales.

Extending the pro formas out to 2014 and 2015 (illustrated in Appendix 4A and 4B at the end of the chapter) shows that PIPES will require peaks of $1,189,150 and $1,453,960 respectively before falling to the year-end annual projections of $631,980 and $755,330.

Thus, the monthly pro formas present a very different picture of PIPES and its financing needs from the annuals due to the seasonality of this firm. Asking the bank for a line of credit of $557,000 is very different from a line of credit of $1 million plus. If neither PIPES nor the bank did their homework, a line of credit of $557,000 would be exhausted in January 2013 (when $599,000 is required).

Summary

This chapter demonstrated the mechanics of generating monthly pro formas in the face of seasonal sales. We showed that large variations in financing can arise during a given year due to seasonality. The reader can now understand that creating pro forma financials focuses management discussion on:

1. Whether the firm is forecast to be profitable
2. How much funding the firm requires

As in the prior chapter, if management goes to a bank asking for a loan, they better have these numbers prepared ahead of time in order to know what to request from the bank as well as to appear credible. Also, when management is considering a change in any aspect of their operations (e.g., a change in payables to 10 days), they must forecast the impact of that change.

We have by no means exhausted all the ways our three tools (Sources and Uses of Funds, ratio analysis, and pro formas) can be used. However, the reader should now have a good feel for why these tools are important and how powerful they really are.

Appendix 4A: PIPES Monthly Pro Forma Income Statements and Balance Sheets 2014

Appendix 4B: PIPES Monthly Pro Forma Income Statements and Balance Sheets 2015