Business modeling is the process of translating a
business model into financial results, or other required outputs, using input variables together with logical arguments for how outputs are derived from inputs. Spreadsheets are often developed with software such as Microsoft Excel, to forecast the future outcome given different potential scenarios.
Business modeling forces the business model designer or analyzer to identify key variables, make and verify important assumptions, test for different scenarios and by that understand the complexities of the business model and how different attributes and factors relate to each other. In some cases the business modeling process may not produce an answer to a specific business question, but may be constructed simply to enhance the understanding of the business model and its environment.
A process for Business Modeling:
1. Identify the purpose with the business modeling and the outputs required. What are the fundamental business questions? Is it to test a new
value proposition? A new customer segment? Business outcome given different scenarios in sales volume? Is it to replace own assets or activities with external ones? Is it to lower the
cost structure? Is it to test another revenue model? When the purpose is identified there are some practical questions such as: What currency to use? What time frame? How many time periods? Should it be a nominal or real forecast?
2. Identify the input variables and major drivers of input variables that determine the outputs. What are the variables that need to be identified or assumptions that need to be done? What are the decision variables that can be manipulated to optimize the model? What is the size of the market? How much do they spend today? What is the growth rate? What are the trends? How will revenues be generated? What will be the sales volumes? What will be the different price levels used? What will be given away for free? What will be the costs of targeting and acquiring customers? What will be the cost of goods sold (COGS)? What will be the fixed and variable costs? What will be the major drivers of revenues and costs? What are the trends regarding the identified major drivers? When in time are revenues and costs expected?
When all variables that influence the business are identified, it is important to identify relationships between input variables to reduce the data that needs collecting. Variables that can be derived from others should be removed.
3. Develop formulas (logical arguments) for how outputs are derived from inputs. The relationships between outputs and inputs are the core of translating a business model into financial results. Not all relationships may at first be clear and the business modeling process often clarifies how the business model really "works". This can be done on the back of a napkin or in Excel with a structured or free-form approach. To make the model easy to use, further develop, test and debug, it is recommendable to separate formulas into easy to understand modules. Before expanding into full scale model test each module with different trial solutions to check the logic.
4. Gather data and make assumptions. Shit In Shit Out, - the input data is essential for the output of the business modeling to be of any value and sources for gathered data should be clearly documented. In some cases it will not be possible to find all data that is necessary and assumptions have to be made or additional variables may need to be added that can derive the required variable with missing data.
5. Build the spreadsheet model. Start by sketching out blocks on a piece of paper to find a good way to use rows and columns, and to find a logical way to arrange input data, formulas and the presentation of output. When starting in the spreadsheet program I recommend separating between sheets with "input", "logic" and "output". If you chose to use only one sheet I recommend using borders, shading and colors to distinguish between cell types.
Starting with the "input" sheets, related data should be grouped together and entered into the spreadsheet with labels that identify the data, and the source of data or assumptions that have been made. To make the code easy to modify it is important to design the model so that each piece of data is entered only once, and if used in additional places the original data cell is referred to. To make the formulas easy to understand I recommend to name all input fields, so instead of cell references "B11 * C11" refer to names such as "Number_of_visitors * Revenue_per_visitor". This reduces the time to interpret and understand formulas, especially when they become long and complicated.
In the "logic" sheets, no new data should be entered and entering any numbers directly into the formulas should be avoided. Separating all data from the formulas makes the model easier to interpret, easier to modify and easier when performing sensitivity analysis to see what the effect would be if some of the estimates were to take other values. Keep all formulas as simple as possible and when complicated formulas are required, break it out into separate calculations with subtotals.
Finally the "output" sheets, should answer the fundamental business questions, stated in 1. It is the presentation of the results from the "logic" sheets and can be presented together with key variables and assumptions from the "input" sheets.
6. Test the spreadsheet model. Testing ensures that the model is accurate and free from any conceptual or technical errors. Testing and debugging can be done in numerous ways and some basic things to check for is to use simple test data and perform manual checks, examine logical operations and check for column and row consistency. Also, to range test the model using the extreme edges of expected possible input data, or to stress test the model using unexpected inputs such as negative values when positive values were expected, very large or very small values or set all inputs to zero.
7. Enter gathered data. Once the spreadsheet model has been tested, the gathered data together with references and assumptions made, should be entered into the "input" sheets generating the most probable outcome in the "output" sheets.
8. Test alternative scenarios. A number of scenarios can be developed and simulated, selecting different input variables. Focus should be on realistic alternative scenarios with changes in input variables that have a high level of uncertainty and high impact on the business outcome. Simulation such as the Monte Carlo method can be used to simulate the uncertainty in input variables generating an average output as well as its volatility and other sensitivities to present the probability distribution around the average output.
9. Analyze the results. Once the different alternative scenarios has been developed and tested the results should be analyzed. What changes can be made on the business model to improve the results? What strategy can be applied to execute the business model differently? How can the ranges of uncertain variables be lowered? Can the same revenues be generated with a lower
cost structure and/or with lower risk?
10. Adjust the business model. Business modeling is an iterative process and when forced to identify key variables and assumptions, the business model designer may find new ways to innovate and adjust the business model not only to maximize the output of the most realistic scenario, but to create robust business models if some of the assumptions turn out to be false...
"Financial models are always wrong; but they are still very powerful for helping you understand your business"
Taylor Davidson, principal at Unstructured Ventures
