In this example we show the original estimates and items that were added later that were flagged so that this audit trail can quickly explain why the estimates changed over time. A written estimate is often a more detailed assessment compared to an initial ballpark estimate, and it can change the viability of a project once all the costs are clearly itemized. So unless you keep track of what you change as new information becomes available it is easy to forget how the totals changed over time. Note that we identify a source document and page reference to support specific information that is quoted in the input to the model.

We have plenty of room to add further explanations and/or detailed calculations to support an estimate: since the data input log is not within the vicinity of the financial model itself we can use whatever cells we need to deliver the input much more succinctly than would be possible otherwise, as we like to keep the financial model small and presentable. Note also that we may not necessarily use all the inputs, but we can keep that information at hand for future consideration. You may think you will remember where you got your information from, but you will find out that is easier said than done: whoever inherits the information does not have memory of where assumptions and/or estimates came from.

In the course we show how we selectively compose a current total for different items based on which estimates are still current or not. We emphasize the simplicity of how to do that in Excel™ and how you can apply this same logic to virtually any analysis. If you add rows to the data in this audit trail the link to the actual analysis will automatically update to make sure the relevant data are retrieved for the actual model, again using basic Excel™ functionality. The importance of the audit trail is that we can answer the most fundamental questions of where the estimates came from so that the analysis can be evaluated.

The following notes summarize how we structure the worksheet that we can adapt to any project the student has to build a business case for:

Cost IT Internal

The first column contains a series of references to totals from Step-1 detailed cost / benefit audit trail tabulations, as in “=C73” for “IBM H/W (2/3 Dep1, 1/3 Dep2)” where we explain that IBM hardware is composed 2/3 of costs to be charged to Department 1, and 1/3 of costs to be charged to Department 2. A column total is produced with an Excel™ formula: “=SUM(C3:C20)” that can be adapted depending on how many input rows you actually have.

Cost Tax Included

The second column simply applies the tax to the before tax column as required for external costs (the internal costs do not incur a similar liability. This is simply defined as either “=C3*1.13” for taxable costs or “=C8” for non-taxable items. The tax rate can be hard-coded because it is simple enough to make it easy to adapt to any other situation.

Capitalized

The third column identifies the portion of the cost tax included that will be capitalized so that we use an annual depreciation to match the cost of the item to business revenues realized from using that item. We actually have a table to the right of the costs that identifies the capitalization rate that we can use as “=D8*J8”, where “J8” is the rate, and “D8” is the cost tax included. We made the capitalization rate an explicit variable so that it is easier to modify as well as to document which rate was used in a particular analysis session.

Expensed

The fourth column in principle is the balance between cost tax included and the capitalized portion of that cost: however, it gets a little more complicated than that because some costs don’t become an annual expense until year 2 of the analysis. We have established a reference (shown later) that gives us the option to defer the expense to year 2, using the formula: “=IF(K8="YR2",0,D8-E8)” that checks if the argument in column “K” is “YR2”. If that is so, then the year 1 expensed portion is set to 0, but if not we simply use the balance between cost tax included and the capitalized portion of that cost.

Total Less Contingency

The 2nd, 3rd, and 4th financial columns are also totaled as shown in the example. This is so that we can compute (and add) the contingency allowance into the model (if that is applicable to your organization).

Contingency

The contingency allowance is calculated only for those rows where a percentage is defined, as shown in the next section, which computes the effective amount set aside for contingency. To compute the ratio between capital and expense contingency allowances we first look at the capital portion of total cost tax included: “=E21/D21” turns out to yield 60% in this example. The expense portion is simply the balance of 1 – 60% = 40% (as in “=1-E24”). The contingency percentages can be selectively applied, and the relevant “gross” allowances can be calculated based on the cost tax included amounts as “=D7*G7” (where column “G” is a percentage and column “D” we know to be the cost tax included).

Licence / Maintenance

The 3rd support column shows where we apply license costs or maintenance costs against line items, which will be used later in the cash flow calculations. The point is that you make this information an explicit parameter that can easily be modified, but that also illustrates the assumptions used in these calculations.

Capitalized

The 4th support column shows how we decided on the split between capital expenditure and expense allocation for specific line items. Some of these parameters are simply conventions, but they are also easily changed if we receive new directives on how to split the costs.

One of the major challenges in a financial model is to determine how an asset is to be depreciated so that annualized cash flows can be computed in order to assess the Net Present Value of the business case. First we need to determine when the assets will be acquired:

Year 1 – Year 3 Acquisition Split

The “acquisition year” parameter columns identify the percentage of the acquisition that applies to a given year. In this analysis we apply 100% of the purchases right away. If we defer part of the value into subsequent years (staged delivery) that affects the depreciation calculations as explained on the next page. We can do that with some of the capitalized development if we think that the results may not become available until later, but deferring the depreciation can slightly lower the NPV for overall cash flow results.

Depreciation Rate

The “depreciation rate” parameter column shows the capital cost allowance rate for the asset types in the cost model. The way the calculations are setup the rates can differ between rows so that we calculate a specific depreciation pattern. Here 25% represents straight-line amortization over 4 years as a default. We can accelerate the depreciation where allowed over 3 years and achieve a slightly better NPV for the overall cash flow results.

Depreciation Calculations

With each row in this table corresponding to a line-item in the cost model, we can detail how we derive the depreciation allowance for each operating year so that this calculation can be audited. We can also set different rates (as illustrated on the previous page), and we can summarize the depreciation into the different asset classes (here we use only hardware and software). There are actually 3 sections in this table corresponding to assets acquired in year 1, year 2, or year 3 of the analysis. Assets acquired in year 1 can affect the full 5 years of this financial analysis. Typically the assets acquired in years 2 and 3 follow a similar pattern.

The “Expenses” output column in the cost model portion is where we filter out the costs that apply to an implementation initiative – commonly referred to as project expenses. These are one-time only costs, and key to the evaluation and approval of an initiative. There are also recurring costs that apply to your operating years. With these calculations we have now broken out all the information that we can put together in order to establish the cash flow model results for this particular example. If you adapt the model to support any other project you may need to change the above calculations to reflect the data you need for the data items relevant to the business case. By performing these calculations “off to the side” you do not cause complications for the main components of the model: as shown in examples, this can get messy when a complex IF formula needs to be modified when it can simply pull the results from a recurring costs table.

Cash Flow model

The purpose of the cash flow model is to show how we incur the expenses over 5 years, and when we see the benefits being realized.

• Project Expense = Year 1 is derived from the cost model expenses total including the contingency allowances on the actual expenses and the capital expenditures. If expenses are incurred in later years we must treat additional entries as recurring costs until all results are delivered, otherwise the NPV and IRR calculations will be compromised.
• H/W Depreciation = copied from the depreciation calculations, for a proper annualized distribution over the term of the depreciation, and/or to account for depreciation on different terms (eg: Hardware vs. Leasehold Improvements).
• S/W Amortization = copied from the depreciation calculations, for a proper annualized distribution over the term of the depreciation, and/or to account for depreciation on different terms, as above.
• On-going costs = copied from the recurring cost calculations that we can also summarize since they are already itemized, unless we need to segregate these costs for annual budget purposes.
• Cost Savings = the contentious part, because it is difficult to find a precise calculation with supporting information to support this analysis input.
• Total expense, net of cost savings = where we determine the annualized cash flow projections to see the benefits stacked up against the costs, so that we can treat an initiative as an investment opportunity.

The information produced up to this point should be consistent with what you need to provide input to a business case. However, for the project funding request we must also provide a quarterly breakdown of cash flow timing for the next fiscal year. Instead of producing separate versions of the cash flow, the model incorporates the ability to project the quarterly spending in a budget model that can be used for financial planning purposes. Other information may need to be transformed into categories that yield a standard business case – you can fairly easily extract the information from the above model, or you can adapt the model to highlight the items you need for the business case.

The business case also requires narrative descriptions that are out of scope for this model – you need to make sure that those descriptions are consistent with the model, and that the model delivers the results you need to make the business case attractive from an investor paradigm. You cannot produce numbers and be done with the analysis. Normally you will already have most of the business case written such that you can focus on collecting the supporting financial information and processing that into the above financial analysis.

There are many different ways to document the final business case, depending on the decision rules established for the organization. What you learn in this course should be sufficient to give you the confidence to take on the task of completing most simple business cases. For larger projects we recommend a more comprehensive approach that would be overkill for a smaller project, so for most initiatives (up to $1Million) the process covered in this course should give you the tools to do the job.

Learning Formats       PMAP_FME

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

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.