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:
- Maitain a consistent and orderly logical sequence.
- Their formulas are simple and easy to follow by other financial analysts.
- 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.
- The formulas and data are in separate cells, logically separate and clearly identified.
- 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.
No comments:
Post a Comment