Projecting the Financial future - Financial Modeling and Forecasting Financial Statements

Many people mistakenly believe that financial accounting reports are simply dry, historical summaries of the past. - Now there's nothing wrong with history. A historical summary of a company's past performance allows you to evaluate management performance, especially if you compare that past performance to what was planned. - However, most financial statement users are not as interested in the past as they are in the future. - For example, when deciding whether to loan money to a company, a banker wants to know what the company's cash flows will be in the future. After all, it's out of future cash flows generated by the company that the loan will be repaid. - In a similar fashion, potential investors are interested in future profits, and future cash flows. When you buy a company, you're buying its future, not its past. - Now, in this course, we will show you how to use the historical financial statements as a platform for forecasting a company's future. - We will consider each of the important financial statement numbers, such as cost of goods sold, depreciation expense, and the levels of inventory and fixed assets. And we'll ask the question what will cause this number to change in the future. - We will see the forecasting power of the simple account equation assets equal liabilities plus equity. - We will work through some practice exercises in constructing forecasted financial statements. These are also called pro forma financial statements. - And we will illustrate the insights that financial statement forecasting provides by using our favorite teaching case, the near-death experience of Home Depot back in 1985. Use the past to understand the future(Viewed) - Now a common criticism I hear about accounting is that it's too focused on the past. Managers, bankers, and investors don't want to know about the past. They want to know about the future. - People say that looking at financial statements is like tryin' to drive your car by looking in the rear-view mirror to see where you have been. - Well, I'm going to go with Winston Churchill on this topic. He said, "The further back you can look, "the further forward you are likely to see." Churchill, a student of history, understood that studying the past allows one an educated glimpse into the uncertain future. - Studying and analyzing the historical performance of a company allows financial statement users to project the future performance of a company. - We can make some reasonable assumptions about a company's future performance, and it is then fairly straight-forward to see what future financial statements might look like. - To illustrate, let's discuss forecasting a company's income statement. Now, the same general concepts apply if we're looking at a balance sheet. So, to begin, we recognize that the amounts of some expenses are tied directly to the amount of sales for the year. - For example, if we do nothing differently in the future, it seems reasonable to predict that cost-to-sales will increase at the same rate as sales increases. - For example, year after year, Walmart's cost-of-sales is approximately 75% of Walmart's sales amount. In other words, if Walmart sells you something for $100, that thing costs Walmart about $75 to purchase from its suppliers. - So, if Walmart sales are forecasted to increase next year to $1 trillion, then cost-to-sales would probably be about $750 billion. - By the way, Walmart's actually sales are about half that level, 'about $500 billion, but hey, we're doin' a 'what-if' calculation. Why not try out an optimistic scenario? - Exactly, similarly, other operating expense, such as wages, are also likely to maintain a constant relationship with the level of sales. - Some expenses don't, necessarily, vary with sales. For example, depreciation expense will vary with the amount of a company's buildings and equipment. If the amount of buildings and equipment increases, it's reasonable to expect depreciation expense will also increase. - Now, consider interest expense. The amount of interest expense is not tied to the level of sales. Instead, you pay more interest if you have more loans. If the amount of a company's loans goes up, it is reasonable to forecast that interest expense will go up as well. - Now, let's not forget about income tax expense. Income tax expense is not a function of sales. It's a function of income, hence the name. - If you tell me forecasted income for next year, I can then apply the expected income tax rate to generate an estimate of next year's income tax expense. - Now, by the way, self-employed individuals in the United States do this simple forecast all the time as they send estimated income tax payments into the U.S. Internal Revenue Service. - Now, a major point is this. In creating forecasted financial statements, we just systematically think about what causes financial statement numbers to change? What events or activities drive those changes, and then we forecast the future based on our expectations about those changing activities. - Using historical information and making some reasonable assumptions, we can get a look at what the future might look like. - And as we put more investigative effort into our assumptions, we can get an even more accurate picture of what the future might look like. - It's not hard, and it's kind of fund, and the nice thing about getting a glimpse of what the future looks like, is that if you don't like it, you can change the assumptions associated with your forecast. - Yeah, you can change the assumptions to get a different forecast. You just need to make sure that those changed assumptions are associated with reasonable and achievable actions in the real world. If they are, you can create a different financial future. Keys to running a business - Running an organization without the benefit of any financial forecasts is a very exciting exercise. From one day to the next, you have no idea what's coming. Every morning, you wake up to a whole new collection of surprises. Now, that's one way to run an organization. A different way is to spend some time carefully constructing a numerical plan, a financial model, a forecast. Yes, there'll still be surprises, but you'll have the model, the plan, the forecast within which to adapt to those surprises. A careful financial model is simply a numerical plan of a business's activities made in advance that helps a business identify and solve problems before those problems ever actually arise in the real world. Now, think about a company that's been around for a long time. Let's use General Electric as an example. They've been around since 1878, when Thomas Edison started the Edison Electric Light Company. Now let's compare this old company, General Electric, to a brand-new company, a startup company, a company that involves just two partners, 10 employees, and lots of exciting ideas. Which one of these two companies, the old one or the new one, is likely to have a more comprehensive, sophisticated internal financial modeling and forecasting process to aid in both short-term and long-term planning? Well, the old company, of course. General Electric, for example, has a very elaborate internal financial modeling and forecasting process. In contrast, a startup company, they probably haven't taken the time to make any kind of financial plan at all. Now think about which of these two companies really needs a financial plan, the old company, General Electric, or the new startup company. Of course they both can benefit from financial planning and forecasting, but which one can benefit more? Let me put it this way. If General Electric decided not to do any detailed financial forecasting this year, would the General Electric employees still know what to do? Well, sure. The company's been around for so long that everyone knows what to do. You pretty much do what you did last year, plus a little more. At least for a year or two, General Electric could probably continue forward in good shape without a financial plan just by operating on organizational inertia. Now, how about that startup company? In a startup company, no one knows what they're doing because it's never happened before. It's their first year. They're creating the business as they go, so it would be extremely useful for the partners in this startup company to spend a little time planning out on paper how things are going to go before launching into the unknown. For example, our startup company has 10 employees now, but is that enough to handle the necessary workload next month, or is it too many? Well, if you haven't even tried to forecast your level of activity next month, along with the output capacity of your employees, then you have no idea whether 10 employees is too many, too few, or just right. Consider another example. Let's say that your startup company is developing green technology and needs the rare earth element terbium in its production process. The company's terbium supplier's located in China, and the average time between when an order is placed and when the terbium arrives is about two months. That's the average. But sometimes it's one month, and sometimes it can be as long as four months. Now, without terbium, the entire production process shuts down. With this uncertainty about terbium delivery delays, operating a startup company without a detailed financial and production forecast of terbium needs, that's terrifying. Yeah, that startup company needs a financial plan, needs it badly. Trying to operate your new business without some advanced financial modeling is like driving a car without looking ahead out of the front windshield. Exciting, sure, but not a good idea. Financial forecasts and loans Financial forecasts and investment decisions Use financial forecasts to understand new information • 2. It All Starts with an Accurate Sales Forecast IBM and the famously bad sales forecast(Viewed) IBM and the famously bad sales forecast - In 1950, resistance to the idea of electronic computers was high inside IBM. - IBM's engineries were specialists in electromechanical devices and were uncomfortable working with vacuum tubes, diodes, and magnetic recording tapes. - In addition there were many questions about the customer demand for electronics computers - In fact, one IBM executive forecast that the size of the world-wide market for computers was no more than five. - Now think about that for a moment. Five computers for the entire world. Look around where you are sitting right now, with your phone, your computer, and various other personal devices. There may be five electronic computers in the room with you, right now. - While following significant internal debate and in spite of this pessimistic sales forecast of five computers. IBM pressed forward with the production of it's first electronic computer, the Model 701. - Programming these early early electronic computers involved flipping switches and plugging cables into different holes in big circuit boards. Now, on a personal note this is exactly what our dad did when he started as a computer programmer for the US Department of Defense. He was programing computers by switching wires around on circuit boards. - Through the 1960's and 70's with it's aggressive leasing program emphasis on sales and service and continued investment in research and development IBM established a dominate. Now, some claimed a monopolistic position in the main frame computer market. - Since then IBM has made a couple of strategic decisions that, in retrospect created a lot of money for other companies. First, when creating it's personal computer back in 1981 IBM decided to use microprocessors made by Intel. - IBM could have designed and produced it's own microprocessors. The company had historically made all of it's computer components in house. But, for speed of development of the IBM PC, IBM went with the existing Intel microprocessors. - And, it's interesting to note as of May 2019, Intel is worth about $230 billion dollars. - Additionally, when the IBM PC was released in 1981, IBM chose not to develop the operating system for its first PC. Instead, electing to use a system called DOS, licensed from a small 32 person company named, Microsoft. - And, as of May 2019, Microsoft is worth about $1 trillion. - [Man In Red Shirt] Now you keep saying what everybody's worth. What's IBM worth? - Well, as of May 2019, IBM is worth about $ 125 billion. In retrospect it makes you wonder where IBM would be today if the company had made it's own microprocessors and it's own PC operating system. - It's also interesting to go all the way back to that original sales forecast in 1950. Only a market for 5 computers, world-wide. If IBM had relied completely on that sales forecast the company would have never entered the computer market at all. - Most of us would probably have never heard the letters IBM. - The starting point, and in many ways the most important point of any financial model exercise is the sales forecast. Help/Feedback Combine historical trends with current plans(Viewed) Incorporate seasonal patterns and recent developments The costs of being wrong - Sales forecasting is not an exact science. Lots of things can go wrong. For example, sometimes data are misinterpreted. Also, it's often the case that the future just doesn't turn out according to the forecast. The future is an uncertain place. An example of data misinterpretation arose in the acquisition of Autonomy by Hewlett-Packard or HP. HP is a hardware and software company based in Palo Alto, California. In October 2011, HP purchased Autonomy, an enterprise software company founded in Cambridge, England. The purchase price was $11 billion. Just over one year later, in November 2012, HP announced that it was recording a $9 billion impairment loss related to the Autonomy acquisition. This means that HP had determined that the value of Autonomy wasn't $11 billion. It was just $2 billion. Now, this case is still in the courts. HP has accused Autonomy of manipulating the reporting of its sales, and Autonomy counters by saying that this is a simple matter of the people at HP not understanding International Financial Reporting Standards. What is clear is that bad data, or data misinterpreted, caused the company to look like it was worth $11 billion, when it was actually worth only $2 billion. Now, here is my favorite sales forecasting fiasco: the AOL acquisition of Time Warner. In January 2001, AOL, which was originally known as America Online, acquired Time Warner. The combined company was valued at over $150 billion. Now this was at the height of the internet bubble, the market was crazy in terms of assumptions about how much money would be made through internet commerce. The AOL-Time Warner merger seemed like a match made in heaven. Pairing the online presence of AOL with the vast media library of Time Warner. The expectation was that the obvious synergy's would generate astronomical future sales and profits. Well, not long after this deal, a wave of reality swept over the world. People came to their senses, and it was realized that the future sales growth expectations for all internet-related companies were wildly overoptimistic. Worldwide, the values of tech companies dropped by an average of 50%. Because the $150 billion AOL-Time Warner valuation had been based on future sales projections that were too high, when more realistic assumptions were applied, it was realized that the combined company was worth much less, over $100 billion less. But errors also happen in the other direction. Sometimes things turn out much better than had been forecasted. And if the original forecast was pessimistic enough, the forecast itself might have turned the company away from something that ultimately turned out to be very profitable. Recall, that in the early 1980s, IBM made two fateful decisions to outsource microprocessor production to Intel and operating system software to Microsoft. Presumably, IBM executives had forecast that future sales and profits from personal computer microprocessors and operating systems weren't large enough to pursue. Well, as of May 2019, the combined market values of Intel and Microsoft were 10 times as much as the market value of IBM. Oops, bad forecast. Sometimes data are faulty or misinterpreted, as with autonomy. But the most common reason for significant errors in forecasting is that the future just doesn't turn out as expected, or hoped. As with AOL and Time Warner, and with IBM, Intel and Microsoft. As we learn the basics of financial modeling and forecasting, keep in mind that this is not an exact science. Prudence, caution, and healthy skepticism are in order, in selecting the data to input into a forecasting model. 3. What Causes Financial Statement Numbers to Change? Home Depot 1985: Three weeks to live(Viewed) The impacts of natural changes(Viewed) The impacts of long-term planning decisions(Viewed) The impacts of financing choices(Viewed) 4. Constructing a Forecasted Income Statement The Gap and predictable change(Viewed) Forecasting sales-based expenses(Viewed) Fixed costs and variable costs(Viewed) Forecasting interest and income taxes(Viewed) 5. Constructing a Forecasted Balance Sheet The power of the accounting equation(Viewed) Identifying the missing number(Viewed) Easy "plugs": Cash, investments, paid-in capital(Viewed) Realistic but challenging "plug": Loans(Viewed) • 6. Constructing a Forecasted Statement of Cash Flows The Home Depot story revisited(Viewed) How to deduce cash flows(Viewed) Forecasting operating cash flow(Viewed) Forecasting investing cash flow(Viewed) Financing cash flow(Viewed) - In the 2018 Fortune 500 listing of the largest companies in the United States, Home Depot is number 23. Home Depot is by far the dominant home improvement retailer in the United States and in the world. - Most people don't know that Home Depot was close to death back in 1985. We know because for many years, we've been teaching a Harvard Business School case about Home Depot. The case was written by Harvard professor Krisha Palepu. - An analysis of Home Depot's financial statement numbers from 1985 reveals that the company's income was down. But the income drop doesn't appear to be an immediate crisis. - Not a crisis until you look at the cash flow numbers. During 1985, Home Depot's operations were burning through four million dollars per month. This is the amount of case paid to suppliers, to employees, and for other operating expenses that were in excess of the cash collected by customers. Four million dollars a month. - And there's additional bad cash flow news. During 1985, Home Depot also burned through an average of eight million dollars per month building new stores and buying some stores from other companies. - In total, Home Depot's 1985 cash burn rate was 12 million dollars a month. Eight million burnt through capital spending, and four million dollars burned through daily operations. - Startup companies often keep track of their cash burn rate. A startup company starts off with a pile of cash. Cash from the personal savings of the founders, usually not much, cash from early investors, and maybe some cash from a local bank loan. - The goal of the startup company is to start generating cash from profitable business operations before burning through all that initial pile of cash. The size of that initial pile of cash, combined with the cash burn rate, gives the company founders an idea of how long they can last before they have to go back out into the streets looking for additional financing. - Now, in Home Depot's case back in 1985, remember that the company was burning through cash at the rate of 12 million dollars a month. In 1985, the company had borrowed 92 million dollars. That was in addition to the 120 million dollars the company had borrowed the year before. Could Home Depot just keep borrowing money forever? - Well, when 1986 started, Home Depot had a cash balance of nine million dollars. With a cash burn rate of 12 million dollars per month, that left them just three weeks of remaining cash. - Three weeks to come up with a cash flow plan. Three weeks with the cash burning away at three million dollars a week. Three weeks to arrange new loans or get additional cash from investors or three weeks to come up with a plan to slow down or even reverse this cash burn rate. - Okay, we're all on the edge of our seats. What did they do? - Remember, at the end of 1985, Home Depot was in dire straits. - To try to fix Home Depot, we have looked at the impact on Home Depot's forecasted cash flows of various changes in operating, investing, and financing practices. - Although we did find changes that might help, at least in the short term, we didn't find the magic bullet that would completely fix Home Depot's operating cash flow problems in order to put the company on solid footing for future growth. - When we do this exercise in class, we pause and say, "Well, I guess it's hopeless. "Home Depot can't be fixed, hopeless." I actually have a PowerPoint slide where the word hopeless twirls around on the screen. - But students know it can't be hopeless. They've seen Home Depot. Surely the company must have figured out something. - So after a pause, a few students will call out, "Do everything at once. "Don't just change one thing at a time, "change everything." - And of course, that's exactly what Home Depot did. They changed everything in 1986. - Based on the modeling exercises we did before, here is a forecast of Home Depot's operating cash flows for 1986 through 1990, assuming that the company continues operating in the same way as it had in 1985. - And if you're a visual person, here's a time series graph of the forecasted operating cash flows. - Ugly, ugly. A company with forecasted operating cash flows like this has no future except death. - Okay, well let's change everything. We will manage our inventory more efficiently, reducing the number of day sales in inventory from the existing 108 days to an achievable industry benchmark of 66 days. - We will squeeze our accounts receivable, reducing the collection time from the existing 14 days down to nine days. - We will stretch out the time we wait to pay our suppliers from 48 days to 50 days. Not a big change, but everything helps. - Let's manage our purchasing costs from our suppliers and our pricing to our customers to increase our gross profit percentage a little bit, from 26% to 28%. - And finally, let's reduce our overhead expenses, wages, rent, and so forth, from 22% of sales to 20% of sales. - All of these changes are reasonable, benchmarked against best practices in the industry. - Now, take a look at these forecasted operating cash flows which reflect all of these changes. - And here's the time series graph. - Astonishing. A complete transformation. With these operating cash flows, Home Depot can easily keep growing, paying for its expansion with internally generated cash flows, as well as the proceeds of loans which cash flow hungry bankers will now be eager to supply. - So I guess it's as easy as that. Just a few changes in a spreadsheet and Home Depot's future is secure. - Uh, no, no, no, my friend. It's not as easy as that. Behind each one of these spreadsheet parameters is a person, an inventory management person, an accounts payable person, a wage compensation person. The marvelous transformation in the spreadsheet is only possible if those people can make it happen in the real world. - That's a good point. Financial modeling and financial statement forecasting is a great starting point, a tool to explore various options. - But in the end, the real work is done by real people in the real world, who then make the financial model come to life.