Transaction Modeling

Transaction modeling is financial modeling at the transaction level. Because transactions occur on specific dates, the forecasted transactions can be tabulated into time periods of any length – days, weeks, months, quarters, or years.


Background on Financial Modeling

Financial Modeling is a well established business planning and analysis tool. You might use a financial model to analyze key decisions, or to set goals and establish budgets. A financial model is also useful to analyze performance against a plan.

With Financial Models you reduce your business to key variables. You play around with your estimates for these key variables to get a sense of the future and ask “what-if” about what concerns you the most. Typically you project out several months or years of sales and expenses, make assumptions about financing, and calculate profits and cash flow. Financial Modeling was originally adopted only by larger businesses because of its huge cost (25 years ago the only option was to spend thousands of dollars a month on computer timesharing). Now that Excel is ubiquitous on the desktop PC, anybody can do financial modeling.

Some characteristics of traditional big company models:

Ø Time periods are usually month or years

Ø A complete Income Statement, Balance Sheet, and Cash Flow statement are the primary outputs

Ø There may be several hundred (up to a few thousand) variables per entity

Ø There is a mixture of financial and non-financial data

Ø There may be multiple entities that are consolidated

Ø Dollars may be kept in thousands or millions in order to avoid large numbers

Ø Balances are typically projected based on some relationship to activity such as the “Days Sales Outstanding” method to forecast Accounts Receivable based on Sales, or “Inventory Turnover” to forecast inventory levels based on Cost of Sales

Ø Individual transactions (such as a product sale or payment of a specific invoice) are not forecasted


Transaction Modeling

Transaction modeling is a form of financial modeling. However, the focus is on the transaction, not the financial statement line item. This means that the precise date of each event is important, not just which month or year it falls into. As such, transaction modeling is ideally suited for doing very detailed cash flow projections. I’ve also seen it used as an introduction to mainstream financial modeling. Once you understand the fundamentals of modeling at the transaction level, it is easier to grasp the account level modeling.

In transaction modeling, you project the detailed transactions that affect a company’s cash position over a short period of time. Transactions are the detailed sales, collections, and expenses processed by a general ledger. Transactions also include borrowings and repayments, and even non-cash events. Some transactions may be bundled together for ease of input and calculation. For example, if you have a payroll of $4,569.19 on August 23, 2004, you might list just his amount rather than break it into the individual paychecks. The import thing is to retain enough detail to compute your cash balance day by day if necessary.

You could do transaction modeling by using a general ledger and creating several months worth of future transactions. This would be a painful, time consuming process to say the least. Can you imagine having to estimate the 350 individual sales transactions that will occur next month, and the exact amount of each, and the exact time when each customer will pay the invoice? A good transaction modeling tool must handle a mix of user specified transactions and ones automatically generated by the tool itself. Automatically generated transactions could be individual sales transactions based on a total projection for the month. Also, they could be based on outcomes of prior periods – for example the minimum payment on a credit card based on what the balance is, which might have been increased to make up for a shortage of cash flow in a prior week.

SurvivalWare Transition Planner makes it possible to do transaction modeling.

SurvivalWare does this by shouldering some of the “detail” burden, and processing the results into an easy to understand format. You can use Survivalware to do deterministic modeling, which can be thought of as “best guess” modeling. (e.g., this is what I think is going to happen, and here is my precise forecast of cash flow based on that thinking). But things can be tough to forecast precisely, especially at the transaction level. Survivalware allows you to use ranges of values instead of point estimates for those variables that tend to bounce around, like sales and collections. Then SurvivalWare generates the detailed transactions, and tabulates them for analysis. The effort required to do a cash flow projection can be as little as that required to do a one month expense budget.


A final word on financial modeling

Transaction modeling does not replace the need for mainstream financial modeling. You gain a lot of understanding from stepping back from the detail and looking at the big picture. (What if I increase ad spending by 25% starting in January, and I see a jump in sales starting in July? What is the impact of raising staff utilization to 75% from 72% over the full year? How much in sales is required to generate a 10% operating margin?)

Rather, you should use transaction modeling as a way to look at the near term cash flow impact of key decisions. (If I hire another person, and she starts on the 15th, what does that do to next month’s cash flow? Things are really tight – do I need to lay off people to get through this slump? If things don’t change, how much time do I have before I run out of money? Am I kidding myself by juggling payments to creditors?)

There is a balance: you have to survive the short term without running out of cash in order for there to be a long term. Transaction modeling can help you do this. But if you spend all your time focused on the short term, you may dig yourself into a rut, and never escape.

Download SurvivalWare V. 2.01 for a 30 day free trial:

Click below to sign up for our free e-mail newsletter: