Business Office

Excel™ Projection Engine       BPES_PRO

The “Excel™ Projection Engine” is a VBA macro designed to execute workbook calculations based on growth rate parameters (linear or non-linear). It runs through selected data rows on to apply the growth parameters and lets Excel™ cell formulas calculate the dependent data values. It has the ability to save the results in successive output columns that it can sort by “priority” based on user-selected target values that can be maximized (or minimized). If can also discard results based on out-of-bounds conditions. We use this in a number of analysis tools, but there are many unique applications where the same abilities might provide capabilities for optimizing production or staffing levels or many other applications that can be represented in an Excel™ worksheet model, but that require an iterative approach until the operational “sweet spot” has been identified. This engine is typically used to find the risk of major shifts in underlying data, as applied to our Financial Planning & Budgeting model.

To use the pre-coded engines the student must understand that the code is locked and can only be controlled through data external to the VBA-code itself. This is to stop the code from being corrupted if the wrong code logic is applied. If you want to write custom code we suggest using the VBA training courses that explain the underlying nature of the VBA-code itself, which is a completely different focus from building an Excel™ worksheet application that is based on what the pre-coded engines deliver “out of the box” to make it easier to get started. The basic logic is designed to create a time-series from the input parameters, so that you can test different rates of increase or decrease, linear or curved up and/or down. You can also simulate seasonality for added effect.

The projection engine has pre-defined logic to save and restore data cells that will be affected by the changes made to various parameters. For each of these data cells it will change the contents in a range “ ” so that it generates not only all the combinations that must be considered, but also how the values should populate the row. These curves can be long-term or annualized, since the parameters will reflect the first month of the first year in detail. This way you can express seasonality, for example, or you can reflect where the product life cycle expectation is across the 5-year term. You can also identify the output cells that it must save to results columns, so that it can later determine which are the best overall results and then sorting the columns in descending order of priority. The engine and its options are fully documented, which means you can incorporate that into your model documentation for the application you want to create.

This session is focused on the columns to the left of the projection engine that should be used for a particular business application model that you are considering executing under the control of the projection engine. This is essentially a stand-alone Excel™ worksheet, which means that you could convert most business worksheets into simulations by incorporating them into the prescribed VBA-model structure.

The projection engine expands on the simulation engine by generating complete time-series based on the selection of a “curve” pattern. This simplifies the Excel™ cell formulas that do not have to calculate the time series from a single parameter value, but instead the user model can directly apply the generated monthly values, even if the time series are non-linear. If curve patterns are annualized we reuse them for each year, otherwise the results are calculated for a 5-yeat continuous curve.

The projection engine turns the worksheet calculation off while it creates time series, and enables calculations when it is ready, until we copy values into the output column. This minimizes the overhead and the process runs quicker that way. The simulation engine refers to the first cell of targeted time series with a simple cell formula like “=C15” in the input selection column. The actual values placed in 12 successive cells per year depend on the curve it generates. The main feature is that the current value for that time series is saved within the projection region because of that generator.

Output columns are similarly selected, so you can use standard Excel™ logic to make references that persist even if you make changes to the worksheet. A temporary output column is in the projection region – from there the output is moved to the current case column, complete with the parameter values. We use the same approach to select data for the SORT commands that prioritize the output columns from best case to worst case.

However, for boundary limit conditions we use “=IF(,”Y”,”N”)” where the condition test can be as complex as necessary to show if the outcome should be “Y” (to include the case) or “N” (to exclude the case). To run a projection, keep the cursor in the user region while you click the [RUN] button on the simulation region to start the engine.

Normally we use the [RUN] button selection to start the projection, but if we place the cursor in one of the output columns the [RUN] button will restore that particular case and show the worksheet as it looks with those values initialized. So, if you want to quickly show the 5 alternative cases it does not take much to restore the views. When it has recalculated these data, the projection prompts for a worksheet name.

If you enter the name of a new worksheet it will create that worksheet under that name and then copy the contents of the columns to the left of the projection region to that output worksheet. If you do not enter a name of 8 characters or more there is no output worksheet produced. Either way the process will stop. If you want another case reported, then you need to select that column and click on [RUN] again.

Our sample application focused on the financial planning and budgeting as an example of a type of worksheet in which you may want to try different combinations of assumptions to see how things play out. You can use the same logic to create production models, product mixes, seasonality, and more. Note that the projection incorporates the same logic as a simulation but with added functionality. The “curve” definition for simulation simply is “Cell” which only initializes a single cell as we have seen before. The one to select depends on your need for the additional functionality, where the simple option if possible is always preferred.

In summary, there are many repetitive calculations that you may want to analyze, and it may be that you want to show the results using basic Excel™ graphics that show the relationship between input and output values. Learn more about the tools and put your imagination to work to find useful ways to put these tools to work for you.

Learning Formats       BPES_PRO

This course is currently available in a classroom setting (public or company private) with approximately 15 contact hours.

PDF – Certificate Of Completion

Each course offers a certificate of completion that identifies the course, the student, and a brief description of the course. To receive a certificate the student must have attended at least 80% of the course sessions. This personalized certificate is forwarded to the student by Email.

PDF – Course Notebook

Each course includes a notebook in PDF format that provides the minimum knowledge the student must master in order to obtain the certificate. In the notebook you will find references to other study materials. Students receive the notebook by Email when their registration is confirmed.

PDF – Program Overview

An overview of this study program can be downloaded from the website by right-clicking on the program link on the enquiry page.

PDF – Current Training Schedule

A list of upcoming training sessions can be downloaded from the website by right-clicking on the schedule link on the enquiry page.

Registration – Service Providers

To register for any training course please look on the enquiry link page of your service provider (from where you accessed this website). On the page you will find a registration request form where you can order the course that you are interested in. The availability dates will be provided to you, along with payment instructions if you decide to go ahead.