Thursday, November 26, 2015

Economic models in Excel

Ernesto Hontoria López

(Versión en español)

For many years I have been reading the drawbacks of using spreadsheets like Excel, for economic modeling, for consolidating budgets and financial statements, and for preparing reports. However, my experience tells me that Excel is a must have tool for those dedicated to these activities, and that sooner or later the financial analyst will use Excel to solve their problems. There is a simple reason for that: it is often quicker and easier to do it in Excel.

Without neglecting the achievements of business intelligence systems (BI), as COGNOS, PROFIT or LONGVIEW, for the development and maintenance of economic models, this article aims to give some practical suggestions for making them in Excel in the best possible way. Although the author of these lines acknowledges the superiority of some business intelligence systems for modeling complex situations or to handle large volumes of data; this superiority usually comes with bigger system investments, beyond the reach of many small businesses and entrepreneurs.

An economic model is a planning tool that allows to determine how certain variables affect profitability of the business, and to predict, through changes in the values ​​of these variables, future potential results. An economic model is a mathematical representation, a set of equations or formulas to project the profit and loss of the company and its cash flow, operating under given conditions of prices and costs. These conditions are reflected through the values ​​of the input variables which constitute the data of the model.

Economic models support the decision-making process and allow measuring their risks by simulating the possible outcomes of each decision: How much, a 5% reduction in the selling price of a product, would impact the cash flow? How much working capital would be needed to increase the level of production by 10%? The models are also used to predict the impact of changes in the business environment, the ability of the company to meet its commitments and pay its obligations: How much cash would be needed to cover the payroll if the government declares a minimum wage increase of X? How much cash would be needed to cover an increase in the royalties or in the income tax rate? Economics models are also good to maximize shareholders' profit through tax and financial planning: What is more convenient for the company: buying or leasing an asset, increasing the headcount or outsourcing part of the business, borrowing or internally funding the new project?

A good economic model in Excel has the following characteristics:
  1. Maitain  a consistent and orderly logical sequence.
  2. Their formulas are simple and easy to follow by other financial analysts.
  3. It is readable. Texts and titles of the cells describe well the content of the formulas and numbers in the cells. The Excel document serves for decision making.
  4. The formulas and data are in separate cells, logically separate and clearly identified.
  5. It is designed for the user.



If you have the task of developing an economic model, I would suggest you to invest time keeping an orderly sequence of calculations, not only for the sake of another analyst trying to understand your work, but for your own self, if you need, a couple of months later, to review the math you have done. It has happened to me many times in the past, that for delivering results faster, I have neglected the order in the spreadsheet, and several months later, when I was asked to review the numbers, I have had to redo everything because I couldn't understand my own work. In the hard way, if you want, I have learned what my mom used to tell me: "the lazy man works double ". So, try to invest more time in models at the beginning in order to create something you can easily understand and reuse later without becoming a headache.

 Following this idea, try to complete the calculations that have logical relationship between each other in the same block of the spreadsheet, avoiding jumps from one tab to another or intermediate stages scattered throughout the file. Related calculations are easier to understand if they are close in the worksheet (if you can see them together) and if they follow a logical sequence. If the model has multiple tabs, try to maintain a similar structure in each of them. It will be easier to follow the logic if the tabs are designed consistently.

Try to keep, formulas short and as simple as possible, so that they can be reviewed and understood by others. If a calculation requires a very long formula, it may be useful to separate it into several steps. By separating the calculations in smaller steps remember to keep the order in the logical sequence: If A + B = C, place all the calculations you need first to find A, followed by those required to find B, before calculating the result C.

A model will generate more confident in its users if the texts and titles explain well the numbers in the cells and the logical sequence of the math is clear. Complicated calculations, tangled structures, numbers scattered without order, generate suspicions and doubts that invalidate the tool for decision making. At the end, your purpose is to have a discussion about the potential outcomes for the business, of taking this or that decision, not whether economic model calculations are correct or not.

A key element that facilitates the validation of the model's results is the separation of the data from the formulas. The formulas (equations) should not contain manually keyed data. The data feed variables, and the variables form the formulas. The formulas of the models are stable and usually do not require modification. If you want to calculate the revenues, for example, which formula equals to: Revenue = Price x Units sold;  "Price" and "Units sold" are going to be input variables, which means they are going to be well identified cells in the Excel model. The numbers placed in these cells will constitute the input data. Revenues, however, is a formula written in a third cell in Excel that multiplies the two mentioned variables (input variables).

The revenues are going to change every time that the data change (Price or units sold), but the formula remains intact. By keeping input data separate and visible, we can easily change the data, and getting results quickly, without the risk of damaging the formulas. Additionally, after probing that the formula is producing the right results, the discussion turns to validate data, that is, whether the prices, and the units sold, are reasonable assumptions. The discussion on this matter has an strategic value; it is an intrinsic part of the planning exercise, while a discussion of whether this or that formula is right or wrong, does not add any value to the business, and must be resolved by the analysts in advance.

When designing an economic model you must consider: who will be the final users; what is the technical jargon they handle; what variables are familiar to them; how they are used to see the results and what kind of indicators serve them as a reference to verify that the numbers are reasonable. Before starting to develop the model, you should have a clear idea of the purpose of the model; what are the questions you are trying to answer; for whom it is done; who are going to manipulate it and what are their skills levels in Excel.

Thursday, September 26, 2013

Rolling forecasts versus Annual budget

Ernesto Hontoria


Have you ever been trapped in the preparation of an annual budget that is being changed every few weeks and you can never wrap it up? Have you ever dreamt of completely doing away with the annual budget of the company you work with? Would you embrace the idea of replacing it by easier to do rolling forecasts? That was the main idea discussed in the IBM Finance Forum 2013. Tempting? By sure it is! The idea of erasing annual budgets from the list of activities is at least “sexy” for those of us who have suffered the preparation of never ending budgets. Replacing it by rolling forecasts, which would be continually updated throughout the year, is the equivalent of replacing a mega activity that consumes a considerable amount of effort and generates a peak of work, by a series of short routines, evenly distributed through the calendar, and focused on the critical -and more volatile- variables of the business.

Sounds good so far? Well, it gets better. In an increasingly volatile and competitive environment, annual budgets begin to look like a straightjacket when you want to give a turn to your business. For example, If you are in the middle of a fiscal year, and decide to change the pricing strategy that was envisaged in the budget, or not to launch the new product that was planned to be launched, or to postpone the opening of a new store for 6 months, waiting for more favourable conditions, the possibility of achieving the goals set in the annual budget would be seriously compromised. In a rapidly changing economic environment, with a lot of pressure from new competitors (many of them global), and with technological changes every second, it is not surprising that the budget of the company could be completely obsolete in a few months, and sometimes even, after few weeks of starting the new fiscal year.

No wonder then that the idea of ​​replacing the annual budget by a process of continuing re-forecasting the business, in order to amend estimations every time that the market conditions change, looks so appealing. Moreover when business intelligence systems and technology enable and facilitate these changes. However, it is not so simple. Its implementation involves a cultural change which affects the entire company, not just those who traditionally are involved in budgeting.

The company's annual budget is a planning tool commonly used to align business goals. Rather than to forecast the future, the budget is set like a target to be achieved. It is used as a reference to determine individual goals, and employees’ annual bonuses. Very often the annual budget is the core of the employees’ incentive plans. It is also used to control the appropriate use of funds and resources, especially in governmental and public funded institutions. The annual budget could be eliminated from the task’s list, but something needs to fulfill the role that it is playing.

At the end, we are talking about changing the Planning Cycle. One option is to move away from the old annual cycle to a new one which is continually updated. Every time that something relevant in the business changes, a new forecast is produced. According to the IBM Finance Forum’s speakers some large and famous corporations –labelled by them as leading companies- have already replaced budgets by rolling forecasts and are happy with their decision. With some scepticism, I am guessing that they have changed an old headache for a new one that seems to make more sense in the current business environment.

My own experience tells me that the annual budgets are based on assumptions that are continuously changing. Those premises change several times during the budget process forcing to produce different budget versions before “the final” one is published. Once published, the assumptions start to become obsolete as the reality very often refuses to follow our budgeting path. In this sense, having a tool that allows us to update our assumptions every time they change, producing thus a fresh projection, is definitively a step forward.

The critical issue then becomes: how to set goals to measure employees’ performance, and to award them accordingly if you have replaced the budget by rolling forecasts. Today those goals are imbedded in the annual budget. But, how to set goals if everything is constantly changing? One possible solution is to set shorter cycles and to measure employees’ performance more often, but this would require a greater administrative effort. Perhaps there is also a technological solution for this problem. Another possibility is to look for new Key Performance Indicators (KPI) to measure employees’ performance. Instead of financial indicators try to find a new ones that measure the real drivers of the business, like number of clients served, or items sold per square meter instead of annual sales, for example; or try to find indicators that will remain relevant despite changes in pricing strategy or the opening or closing of stores.

I should recognize that, at the moment of writing these lines, I am full of doubts and concerns about how the replacement of the annual budget by rolling forecasts will affect the entire annual planning cycle in its broadest sense. Especially, how it will affect the thorny issue of employee’s incentives. However, I do not hesitate to welcome new tools to break the long process of annual budgeting in favour of smaller processes continuously updated. That imposes rethinking the way we do annual budget, looking for how we can continuously update the premises on which our financial projections are based, in order to facilitate a better decision-taking process. It would mean to break the budget mega activity into a number of ongoing routines of scanning the business environment. Perhaps the annual budget could be, after all, a snap shot, a photograph taken sometime in the year of the annual projection we have at that moment, plus some kind of challenging adjustment to be achieved.

Probably is time to reconsider the entire Planning Cycle Process and to see how the opportunity to replace the budget by rolling forecast can help us to break some of the perversions created by linking employees’ incentives to the execution of an annual budget. One of these perversions is the tendency to try to achieve budget despite changes in the business environment. Following blindly the budget could be sometimes detrimental to the organization in the long run, if the assumptions on which the budget was based have significantly changed. Another perversion is the tendency to spend what is left in the budget before the fiscal year end. Not because the organization needs it, but because it will be the base for next year budget allocation. For the sake of achieving budget goals or to ensure a similar allocation of resources in coming fiscal years, managers sometimes act in detriment of their own organizations. When budget time comes it is common to see how certain numbers are inflated or deflated to facilitate achieving the goals at the year end. The annual budget becomes a push and pull game between the managers and directors of the company, each one struggling to place the bar at a height that allows them to achieve their own annual bonuses. How do the rolling forecasts break these perversions? I confess that I am still studying the issue...

Monday, February 18, 2013

The Role of the Financial Analyst

Ernesto Hontoria

Here we are, trying to describe the occupation that allows us to earn our livings for some years now. What is the role of The Financial Analyst in a company? Without any question I would say it is to clarify accounts: search for numbers, add and subtract them, put a bunch of them together in a table or chart that will make our boss happy to have something to show to his or her superior, when this makes questions about the financials. It is not a boring and tedious work, as it was once described to me by the merchandising manager in charge of the book department in the company I worked some years ago. She wouldn’t be able to spend the day reviewing numbers – she said. Rather, I would say, it is a noble occupation, that seeks to give happiness and peace of mind to those leading the company. After all, they could not sleep well at nights without understanding why the company accounts are showing those numbers, and what those numbers really mean. How could they sleep well without knowing whether the company is heading or not in the right direction?

The Financial Analyst is a smart person -usually charming and nice looking- sitting for more than eight hours at a computer, making reports, checking numbers, explaining changes, analyzing spending accounts, projecting revenues, preparing presentations, taking coffee and more.  In my experience, those activities could fall into three main groups: reporting, analysis and planning. The reports are the basis to build the business intelligence (BI). Their function is to give those who run the company information on the status of operations and industry. The Financial Analyst is usually responsible for gathering the information that feeds many of the reports designed by the company. He or she gets the data, filters it, sorts it, and puts it together in previously agreed reporting templates. Once the report is ready our hero –or heroine- reviews and conciliates the numbers and, after assuring that everything is right, distributes or publishes it in order to guide future decisions.

Analyzing is more interesting than reporting. Its goal is to seek and find answers to different situations that arise in the company: Why are the sales dropping? Why are the operational costs rising? Why the financial statement of this month shows the rental cost with an opposite sign? The Financial Analyst must seek answers to different questions, browsing in the accounting systems, looking at the transactions behind the numbers, understanding the reasons why the accountants have booked transaction in one way or another, checking whether the numbers accurately reflect the operational status of the company. Analyzing requires contacting the people involved in the activity that is being analyzed, sometimes in both, accounting and operations, in order to understand what is really going on, and to ensure that the financial statements and reports accurately reflect the operational status of the company.

The Financial Analyst not only looks to the past. He or she needs to understand the operational processes in order to be able of projecting the future, of building economic models to estimate incomes and operating costs under certain assumptions and in different scenarios. How much would the company receive as income if the product is sold at certain discount? How much would the raw materials cost in a couple of years? What would be the impact in the cost structure of paying a salary increase? What would be the return for expanding the plant capacity? Projecting the future is part of the planning. To do this, our fellow Financial Analysts should understand how the several operational processes interact with each other, turning raw materials into inventory, inventory into sales, and sales into cash to cover operational costs and provide profit.

To carry out the duties of this job, The Financial Analyst must have the ability to communicate well with others, so as to maintain good working relationships with colleagues, accountants, cost analysts, plant operators, managers, and even with people outside the company. Good communication skills are essential to clarify accounts; to understand what is behind the numbers in a report, what is happening in the business, and to project what will happen in the future.  The numbers that The Financial Analyst manages in the solitude of her or his job are not boring and tedious. Those numbers, which have been obtained from different sources, including communications with operators and accountants, are instead full of meaning and technical details about the company and the industry.

Sometimes it will be better to communicate face to face, or by phone. Others, the analyst will find more convenient to write an e-mail or letter with tables and charts attached, referring questions or comments which would be difficult to explain otherwise. In many cases the analyst will need to use both: submit written information with tables and charts, formats and templates, and also establish personal contact to answer questions and to avoid misunderstandings. A good analyst should be proficient in oral and written communication, should know how to ask the right questions and find the right answers, to understand the technical jargon and be able to synthesize the findings, in order to expose and explain them to decision makers. In her or his position, The Financial Analyst is exposed to many people from different levels in the company, which I dare say makes the ability to communicate well almost as important as the ability to deal with numbers.

The activities of The Financial Analyst ultimately aim to make the best decisions possible. Reports, financial statements, analyses, economic models are instruments to understand the situation of the company and its industry, and to take actions accordingly. Accuracy and correctness of the numbers, analyses, interpretations and reports are important to allow good decisions. The better the analyses and reports reflect the real situation of the company and its industry, the better will be the decisions of those in charge of taking them.

One of the challenges facing businesses today is to be able to integrate information from multiple sources into concise reports and analyses in order to to facilitate decision-making, that is, turning information into appropriate action to survive in a highly competitive world. The integration and management of information, known today as business intelligence, has led to the emergence of computer systems that integrate data from multiple databases, in various ways and in relatively simple manner. The problem for companies today is not the access to information; most of them have abundant information about their customers, their buying habits, the amounts they spend, their ages and their tastes. Nowadays companies also have a sea of ​​statistics of their own production processes, productivity, failures of their machines, the cost of each part, each screw, the time it takes to repair them, etc. The issue is, rather, how to digest the vast amount of data, how to convert it into useful information, how to interpret it correctly, and how to take advantage of it.

The Financial Analyst has a starring role when trying to untangle the web of data, so it is increasingly important to be skilled in managing multiple systems and databases. It is not enough to communicate well, having financial and accounting knowledge; it is also important to know how to extract the right information from the databases, how to handle and cross data from different sources to produce information. The good news is that many systems leading the technology market today, are implementing interfaces similar to Excel, which undoubtedly is the favorite tool of Financial Analysts, and will make their role a little bit easier.