SDLC

Project Initiation

In this phase, we learn from the project sponsors what it is they want us to provide. At the end of this phase we present a cost/benefit analysis with a rough schedule. With this deliverable we ask our clients if they want to proceed.  If they do, we hand them a SOW (Statement of Work) for them to sign.

Vision

In this task, our clients describe what they want delivered and how that will benefit them. 

Current State

Ask our clients to describe how things are today and what the problems they want to overcome are. Ask for the costs of doing things currently. This provides the cost side of our cost/benefit analysis. 

Future State

Ask our clients what the future state (after the project) will look like and what the additional revenue, and/or reduced expenses will be. This provides the benefit side of our cost/benefit analysis. 

RACI

RACI stands for Responsible, Accountable, Consulted, and Informed. These are the classifications of the project team members. Responsible parties do the work (which usually is us). Accountable parties certify activities are complete. Consulted parties are often known as Subject Matter Experts (SME's) and are sources of information. Informed parties are persons we must keep up-to-date on our project's progress. 

Scope

In most projects there are related things that may or may not be part of this project. For example, if we are estimating a public, private, partnership project involving construction of a park, our sponsors may want us to include a tax impact section to estimate increased revenues due to park visitors staying at hotels (lodging taxes) and eating at restaurants (meals taxes). 

Out of Scope

Equally important to what is in-scope is what is out-of-scope. In our park example, our sponsors may not want us to include costs associated with increased vehicular traffic as visitors use the park.

Feasibility

With the above information and the rest of the SDLC map, we can take some time to create a rough schedule and cost/benefit analysis. While we may treat the initial meeting as a pre-sales engagement (no cost), these deliverables should be paid for even if it reveals to our sponsors that their project is not worth moving forward. 

Research

After project approval, we need to gain a better understanding of what we must provide. At the end of this phase we will have an initial project plan and a much more certain cost estimate.

Functional Requirements

These are the things that our sponsors require our project to do.

Technical Requirements

These define what is needed to support Functional Requirements. It includes what platform will be used (Windows or MAC), which version of Office (Office Online, O365, Excel 2019, 2016, ...), security requirements (VBA?), access requirements (LAN or WWW?),  etc.

Deliverables

This provides a list of things that we will place into our sponsor's hands. This includes not just the workbook, but also a Project Plan complete with tasks, assignments, and due dates; a statement of work (SOW); end user documentation; project documentation;  and more.

Inputs Catalog

This lists sources of information such as actuals, labor rates, material rates, rental rates, product costs, etc. It also includes user modifiable assumptions.

Outputs Catalog

This lists statements, reports, dashboards, printed forms, exports to other systems and anything else produced by our project.

UAT Criteria

This defines our sponsor's User Acceptance Test (UAT) criteria. This is essential. It clearly identifies what tests our project must pass before it is complete and our final payments are due. It prevents scope creep, whereby sponsors sometimes add items to the project and yet expect no increase in costs or timelines. Once UAT Criteria is defined, anything features that do not impact these tests are easily identifiable and require a project change order.

Initial Project Plan

This lists all tasks required to complete the project along with who will perform the tasks, when, and which tasks must be completed before other tasks can start. These provides a much clearer cost and timeline estimate and should be used by our sponsors as another check point as to whether the project should continue or not.

Analysis

Research tells us what we have to work with. Analysis is figuring out what we need to bridge the gap between our current state and our sponsor's vision (future state). 

Gap Analysis

This creates a list of things that need to be created to realize our sponsor's vision. Gap analysis is often best done by working backwards from the end state. We see what we want, and then we ask what that needs to support it. That often means we need to determine what the new thing needs to support it, and so on until we get back to what our research shows we have. 

Test Scripts

Every project has errors. We find errors by thinking about what can be wrong and testing for that condition, such as: do balance sheet balance; can users enter negative prices or non-existent dates; will a known scenario produce expected results, etc. We then document how we test for these things which usually include: inputs to make, processes to run, and the expected result. We should write scripts that produce successful results, but also unsuccessful results unless the project is properly prepared for the negative test.

The UAT scripts must also be part of this task.

Integrity Checks

We can build tests into our projects that continuously monitor for errors. Again, this could be a test for things that should balance, or tests for allocations that should total 100%. Adding these tests to our project will catch errors before our sponsors do, or inform our sponsors when their assumptions produce undesirable results.  For more on this subject see: BXL Integrity Checks 

Project Plan

Once again, we should update our project plan, share it with our sponsors, and ask if the project should continue.

Design

In this phase we determine how things should look and work - before we try to build them in Excel. 

Statements/Visuals

Here is where we design the formats and layouts of financial statements and, if desired, dashboard elements. We can present simple mockups of these things for review before coding something that isn't wanted.

User Interfaces

In this task we design any data entry forms, dashboard controls, navigation elements, etc. 

Project Structure

Here we design the overall flow of our project. Project structure should be dictated by what uses what. Things that require nothing are place up front, things that use those things and provide things to other things next, and things that use other things but provide nothing to any other thing, in back. An example is: cover sheet > navigation (table of contents) > labels > constants > data imports > user modifiable assumptions > calculations > aggregations > statements/reports > dashboard > integrity checks.

Development

Navigation

Complex models typically have several worksheets. Having a table of contents with hyperlinks to each worksheet is helpful to developers, auditors, and users. 

Inputs

Inputs are anything that does not have a precedent. This includes:

• Units of Measure (USD, Each, etc.) and Format labels (MM/DD/YYYY)

• Validation tables (for data entry)

• Named constants (named for transparency in formulas)

• Imported data (ex. Actuals)

• User entered assumptions

Calculations

Calculations are anything that had precedent cells (formula references) and dependent cells. This includes:

• Property calculations (single value calculations)

• Dimension calculations (data sets that are not bound by period)

• Period calculations (calculations for a unit of time)

Outputs

Outputs are anything that as precedent cells but no dependent cells. This includes:

• Aggregations of calculated values like PivotTables used for summaries

• Aggregations of calculated values in the form of a report or financial statement

• Charts and other visualizations of calculated results

• Financial Ratios and KPIs that show if a projects results are desirable

Error Checking

Error checks and alerts show us if the model is not in good working order and thus, outputs and not useful for decisions

Documentation

At the end of this phase we create end user documentation that can be used for training and for reference. We also create project documentation to help auditors, testers, and any developers that may come after us to fix or enhance our project. 

Testing

Tester Training

Before testers can execute test scripts they must be trained on how to use the system and how to conduct tests.

User Interface Testing

This makes sure entry cells are not locked, properly formatted, located appropriately, and include necessary validation.

Calculation Testing

This reviews formulas for:

• Consistency in calculation blocks

• Adherence to standards (such as free of literals and errors, as-well-as placed in reading order)

• Functioning properly

Output Testing

This checks outputs against base case scenarios to provide confidence the entire system works.

Integration Testing

If the project relies on data from external systems or provides input to other workbooks or system, this checks to make sure these integrations function properly and reliably. 

Performance Testing

Some projects require producing results within a set period of time. This ensures that our project with maximum expected load can complete all tasks within those requirements. 

UAT

User Acceptance Testing is performed last by our clients. UAT proves the project performs as required and documented at the beginning of our project.

Implementation

Training Data

Some projects require specific data, such as actuals and user assumptions, so training can be repeated in the same way for all training instances. 

Protection

In prior phases we may have need to access formulas. But in training and after go-live, formulas should be locked to prevent inadvertent and unauthorized modifications that would invalidate our project. 

End User Training

This is where we show users how the project works, how to interact with it, and how to understand outputs.

Data Conversion

Before going live with our project, we may need to replace training data with current data.

Project Distribution

Our workbook may need to be accessed on remote PCs, via the web, or from a local area network. This makes sure the project ends up where it is needed.

Post Go-Live Support

Training is not enough to ensure a smooth transition to live use. We should plan to be present with users when the project is first used.

Maintenance

Corrections

Users often uncover unexpected situations that need to be remedied. We should plan for this and level set sponsor expectations to anticipate problems and the associated costs to remedy them. 

Monitoring

While the project is in use, we should periodically check in to be sure all is well and no one has questions or found problems. 

Adjustments

Monitoring may reveal minor problems or opportunities for enhancements. If we provide a support agreement we many include adding these to the project as part of that agreement and justify continuing maintenance revenue streams.