The First Project

Payton (our customer) tells us he has a personal project. He wants to buy a house but is confused by mortgage rates. Banks advertise interest rates and then a higher number with APR beside it. Payton knows APR stands for Annual Percentage Rate but the interest rate is an annual percentage rate too. "What I want," Payton explains, "is a simple spreadsheet where I enter the rate and any fees and know exactly how much money I will be borrowing and how much it will cost."

Sounds simple. 'Simple' is a warning sign. Any seasoned professional modeler winces at that word, simple. Simple means our customer expects things cheap. And any seasoned professional knows solutions look simple when we have no idea what the solution entails. Before we accept this project we must give some thought as to where we are, where we need to be, and what it will take to bridge that gap. But before we can determine what is needed to bridge the gap we need to be sure what we mean by "where we need to be." This is our project's deliverable. Getting agreement on exactly what we must deliver is crucial to a profitable project because it prevents 'scope creep'.

Scope creep is when customers add project goals and requirements after their project starts. This adds work. Added work adds costs and if we don't manage scope creep we will either have to absorb those costs or surprise our customer with a higher-than-quoted invoice. That never goes well.

Customers often don't realize when they add changes. Without specific deliverables it may be impossible to identify scope creep. But with documented deliverables we can spot scope creep before it starts and work with our customer on how to proceed: stop the project and pay for work done; continue the project as previously agreed; or change the project with increased costs.

Project deliverables with projected timelines and costs are included in a document called a Statement of Work, or SOW.

What is a Statement of Work (SOW)?

SOWs document what we will deliver. For an Excel model SOWs typically include:

Purpose Statement

This attempts to describe why our customer wants this. Getting our customer to articulate this can reveal issues we need to address that we might otherwise overlook.

Scope Specification

This attempts to draw boundaries to include what our model covers and what it doesn't.

Current Situation

This documents how things work now.

Future State

This describes how things will work when the project is complete.

Gaps

This lists what is missing from the current state that must be accomplished to realize the future state.

Tasks

This lists activities required to deliver the project. For larger projects this can be quite detailed.

This project will require the following tasks:

• This meeting (is this presales activity or part of the project and chargeable?)

• SOW creation and signing

• Research, Analysis, Design, Development, and testing

• Documentation

• Training and user acceptance testing (UAT)

Timelines/Schedules

This lists important dates.

For a small project like this there isn't much to list:

• SOW signing/project start date

• Development duration

• Date of delivery with training and UAT

User Acceptance Criteria (UAT)

This attempts to describe how we know we have delivered what we agreed to deliver.

Payton says we can test this using a loan he has using the following procedure.

• Enter Loan Amount

• Enter Loan Term

• Enter APR

• View Monthly Payment and verify it is correct

• View Interest Costs and verify it is correct

Pricing

This lists what amounts the customer agrees to pay and when.

After completing Research, Analysis, and Design phases (see Project Phases below) we will have a good idea of how long this should take. We can then multiply those hours by our hourly rate. Since this is such a small project we will ask for the money immediately upon delivery. We will also offer Payton annual maintenance at 18% of the contract price payable upfront.

Project Phases

If we were modeling this for ourselves we would skip creating a SOW, just enter a few formulas, and be done with it. And while a very simple project like this may seem too small to bother with a SOW, we should follow through with the process because when modeling for others there is more to do than just enter a few formulas. There is also the chance that we didn't quite understand what Payton really wants or Payton may decide that the project should include something else. The SOW will make it clear what we agreed to deliver and for what price.

To determine our SOW's pricing section we must estimate our project. To estimate our project we need to include all project phases. In general project phases include:

Research

If we are not familiar with loans, this is when we seek a subject matter expert (SME), read a book, take a course, or Google the topic. During our research we discover the difference between APR and Interest Rate is APR includes fees rolled into the amount borrowed. This can include:

• • Origination Fee which is the cost of issuing the loan

  • Points used to buy down interest rates

  • Other fixed fee closing costs and percentage based closing costs

We Google How to Calculate APR to get the formulas we will need. We also collect any input, output or process documents.

Analysis

In this phase we determine how the various items found in our research relate to each other. After a little thought we realize costs can be categorized as fixed fee or percentage based. Fixed fees add a fixed amount to the money we need to the loan amount. Percentage costs add a percentage of the money we need to the loan amount.

Design

In this phase we plan how to execute on what we learned in our analysis phase. We need a few more inputs and outputs than we originally thought.

Inputs

• • Money Needed

  • Fixed Fees

  • Percentage Fees (as a percentage)

  • Loan Term (months)

  • Interest Rate (without costs)

Outputs

• • Calculated APR (to confirm APR is correct from banker)

  • Monthly Payment as calculated from interest rate plus loan costs

  • Monthly Payment as calculated from APR on money needed (should be same as above).

  • Total Interest Costs

  • Total Loan Costs (Interest + fees)

Process

• • Amount Borrowed = Money Needed + Fixed Fees + (Money Needed * Percentage Fees)

  • Interest Rate Based Monthly Payment = PMT(Interest Rate/12 months, Loan Term, Amount Borrowed)

  • APR =RATE(Loan Term, Interest Rate Monthly Payment, Money Needed)*12 months

  • APR Based Monthly Payment = PMT(APR/12 months, Loan Term, Money Needed)

  • Total Interest Costs = Monthly Payment*Term - Amount Borrowed

  • Total Loan Costs = Monthly Payment*Term - Money Needed

Construction

In this phase we open Excel and create our model using our design specifications.

Testing

In this phase we test our model to make sure it delivers what we agreed to, that it is easy to use, and won't break.

Implementation

In this phase hand the model to our customer, train them, make sure they accept the project, and stick around long enough to make sure everything works as desired.

Lab

• 1. Calculate hours required

  2. Create a very brief SOW

  3. Create your version of this model and time how long it takes. How does it compare to hours you calculated?

Assignments

• 1. If needed read PMT() function reference.

  2. If needed read RATE() function reference

  3. Read Statement of Work

  4. Read Estimating Projects

  5. Complete the Lab

  6. Complete the Quiz.

Quiz

• 1. This project will take about five minutes

     1. TRUE

     2. FALSE

  2. A Purpose Statement is:

• • 1. Part of a SOW

    2. A Project Phase

    3. Both

    4. Neither

• 3. The SOW's purpose is to:

• • 1. Document what we will deliver

    2. List our work steps

    3. Identify what is in and out of scope

    4. Deign the model

• 4. Which is the correct order of project phases?

• • 1. SOW, Build, Deliver

    2. Purpose, Scope , Current Situation, Future State, Gaps, Tasks, Timelines, UAT, Pricing

    3. Research, Analysis, Design, Construction, Testing, Implementation

    4. Construction, Testing, Implementation

• 5. Physically typing formulas into Excel happens in which project phase?

• • 1. Research

    2. Analysis

    3. Design

    4. Construction

    5. Testing

    6. Implementation

• 6. Which project phase must not start before the SOW is signed?

• • 1. Research

    2. Analysis

    3. Design

    4. Construction

    5. Testing

    6. Implementation

• 7. After which project phase completes do we have enough information to estimate a model?

• • 1. Research

    2. Analysis

    3. Design

    4. Construction

    5. Testing

    6. Implementation

• 8. The construction phase typically takes no more than:

• • 1. 90% of the entire project duration

    2. 75%

    3. 50%

    4. 25%

Summary

SOWs clearly articulate the agreement between customer and modeler so both parties know when the project is complete and payment due. They also provide a basic project plan we can use as a roadmap for creating our model.