The software is basically ready to use right “out of the box” by opening the ExcelFPB workbook. Typically you leave the default workbook “as is” by saving a copy under a different (project) name before you do anything to make changes. The major feature of the software is the VBA logic that is protected against user-changes (there are more ways to make mistakes than to get it right), but the data that drive the software can incorporate all kinds of Excel™ cell formulas that do the real business-focused lifting.

That is where you will spend much of your effort, to alter the default model and to accommodate the unique needs of a specific analysis project. Columns reserved for the VBA logic are given a specific background colour to ensure you do not accidentally corrupt the process by making changes that do not match the projection processing rules. The typical financial plan consists of 5 years of forecasts, each supplemented with 12 months of detail. This product walkthrough will include a sample project that students can use to become familiar with the nature of using this planning approach.

Operationally financial planning and budgeting are focused on products or services that already exist, and uses financial reports that exist but that are focused on past events. What we want is to project those results into the future, taking the product life cycle into account to project either a growth, maturity, or decline expectation. We will reflect the unique aspect of the business operation we must simulate in order to determine the viability of the operation at the core of this project.

Typically you work with internal subject matter experts to find out how the operations work and how revenues are generated, how costs are incurred, and what the production characteristics are to establish what the feasible output volume range is like. We need to work with marketing, product, and delivery management to understand their expectations for the underlying products or services. We have a sample worksheet in which we show a business model that can be adapted to specific projects and, if necessary, you can augment that model with additional Excel™ cell formulas-based logic on data rows reflecting the revenues and expenses (or other important items).

You first need to confirm that this is a valid representation of the operations model for the business that you are analyzing. Of course, repeating the calculations for different business cases and/or assumptions would be a lot of work, but that is automated. The projection engine embedded in the workbook provides the logic that generates different growth rates and saves the result for each model execution in summary columns. At the end of the exercise it can sort those columns at the end of the process in declining order of priority based on the target value(s) you focus on. The net result is that you can literally explore hundreds of “What If” scenarios in minutes.

The analysis following can also be an iterative process, but it is generally a matter of “HOW” to maintain profitability rather than “WHY” a product or service might be a good source of revenues. The point is not to obtain the finances to start a new venture, but to ensure the venture continues to be self-sustaining and revenue producing. The goal is to generate the budget for that product or service delivery so that the accounting system can then track actual performance to forecast in order to provide an early warning if actual performance is unsatisfactory.

The model may be adapted when we change the effort from rising star to peak performer, then to a cash cow, and eventually to a declining product or service that is slowly phased out. Based on using the projection engine to explore the impact of different parameters affected by the product life-cycle state we can generate data consistent with a selected action plan. We may have to change the model to focus on different parameters, or we can incorporate the slope of the life-cycle curve as a parameter table to increase, hold, or decrease levels of contribution in successive operating months.

Keep in mind that the Excel™ calculations only reflect the data you put into it, and that there may be other aspects not reflected in the calculations but in the input received from subject matter experts that give you concerns. People can easily get carried away with the results of these Excel™ calculations that may not accurately reflect the big picture.

Because the projection is performed entirely in Excel™ it is easy to use all kinds of Excel™ reporting capabilities to present the information in ways to highlight the findings for the client. It is also possible to convert the data in the monthly forecast columns into budget estimates using a comma- or tab delimited interface file (depending on what works for the accounting tool used in the company). Other than during the budget cycle we will not use the model for tracking purposes (we might if a competitor launched a new initiative that might affect the budgeted plans).

For the planning process it is customary to depend on a presentation using mainly Excel™ printouts – this is just an interim state until the data are approved by management. It is possible to transform the detailed analysis into simpler summary reports and to consolidate results across multiple products or services by using the built-in report writer engine.