Exercise 6 — Public Procurement
City of Northwood
Which Vendor Should We Choose?
Scenario
The Problem
The City of Northwood has received three vendor proposals for a new integrated permitting and records management system. Each vendor offers a different contract length, upfront cost, and mix of annual expenses and efficiency gains. Your job is to use NPV analysis to determine which — if any — proposals are worth accepting, and which is the best choice.
Your Analytical Framework
For each option, you will work through three steps:
- Step 1 — Estimate annual net cash flow: For each year of the contract, subtract annual costs from annual benefits. Year 0 is the upfront payment only (no benefit yet).
- Step 2 — Discount each year’s net cash flow: Multiply by the discount factor
1/(1.05)^tto convert future dollars to present value. - Step 3 — Sum to NPV: Add all discounted cash flows. A positive NPV means the investment pays off; a negative NPV means it does not.
Vendor Proposals — What Each Vendor Is Offering
| Item | Option A QuickTrack (5yr) |
Option B CityServe (8yr) |
Option C MuniSoft (10yr) |
|---|---|---|---|
| Contract Length | 5 years | 8 years | 10 years |
| Upfront Implementation Cost (Year 0) | $200,000 | $150,000 | $180,000 |
| Annual Software & Support Costs (Yrs 1–N) | $75,000/yr | $50,000/yr | $55,000/yr |
| Annual Staff Time Savings (Yrs 1–N) | $70,000/yr | $50,000/yr | $85,000/yr |
| Annual Error Reduction & Efficiency Gains (Yrs 1–N) | $40,000/yr | $30,000/yr | $45,000/yr |
| Total Annual Benefits (you calculate) | ? | ? | ? |
| Annual Net Cash Flow (you calculate) | ? | ? | ? |
Note: In the Excel file, costs are entered as negative numbers (cash outflows). Total Annual Benefits = Staff Time Savings + Efficiency Gains (positive). Net Cash Flow = Costs (negative) + Total Benefits. These are the columns you will build in Tasks 2–4.
5%
Discount Rate
3
Vendor Proposals
5–10 yrs
Contract Horizons
?
Best Choice
Task 1
Pending
Build the Discount Factor Table
Before you can discount any cash flows, you need a discount factor for each year. Create a table for Years 0 through 10. You will reference this in all three option worksheets.
Discount Factor Formula
Discount Factort = 1 ÷ (1 + r)t where r = 0.05
=1/(1.05)^A2
Year 0 = 1.0000 (no time has passed, so no discounting). Year 1 = 1/1.05 = 0.9524. Each year shrinks by another factor of 1.05.
Step-by-Step Instructions
1
On the Discount_Factors sheet, column A has years 0–10. Column B is blank.
2
In B2, enter
=1/(1.05)^A2. Year 0 should return 1.0000 exactly.3
Fill down through B12 (Year 10). Spot-check: Year 5 ≈ 0.7835, Year 10 ≈ 0.6139.
Hints
A discount factor converts a future dollar into its present-value equivalent. The further in the future, the smaller the factor. Year 0 = 1.0 because there is no time delay. Year 1 = 0.9524 because you would pay $0.9524 today to receive $1 a year from now at a 5% rate.
The denominator (1.05)^t grows exponentially. At Year 5, (1.05)^5 = 1.2763, so the factor = 1/1.2763 = 0.7835. At Year 10, (1.05)^10 = 1.6289, so factor = 1/1.6289 = 0.6139.
Formula:
=1/(1.05)^A2. Do NOT lock A2 with dollar signs — you want it to change as you fill down (A2, A3, A4...). The 1.05 is a constant so it does not need locking either.
Verify Task 1 — Discount Factors
Enter your computed discount factors (round to 4 decimal places):
Year 3 discount factor:
Year 7 discount factor:
Year 10 discount factor:
Task 2
Pending
Option A — QuickTrack (5-Year Contract)
Work through Option A year by year. First calculate the annual net cash flow from the scenario data, then apply your discount factors to find the present value of each year, and finally sum everything to get the NPV.
The Three Columns You Need to Calculate
Total Benefitst = Staff Savingst + Efficiency Gainst
Net Cash Flowt = Costst (negative) + Total Benefitst
PVt = Net Cash Flowt × Discount Factort
Net Cash Flowt = Costst (negative) + Total Benefitst
PVt = Net Cash Flowt × Discount Factort
=SUM(C5:D5) then =B5+E5 then =F5*G5
Costs in column B are entered as negative numbers (cash outflows). Year 0: no benefits yet, so Net Cash Flow0 = −$200,000 (the upfront cost). For Years 1–5 the negative cost plus positive benefits gives the net.
Step-by-Step Instructions
1
On the Option_A sheet, column B (costs) is pre-filled as negative numbers — cash outflows. Columns C (staff savings) and D (efficiency gains) are positive. In column E, sum the two benefit components:
=SUM(C5:D5) for rows with annual benefits (Year 0 stays 0).2
In column F, calculate net cash flow:
=B5+E5. Because B is already negative, adding it to total benefits gives the correct net. Year 0 should return −$200,000; Years 1–5 should be positive.3
In column G, enter the discount factor:
=1/(1.05)^A5. In column H, calculate PV: =F5*G5. Then in the NPV row, sum column H: =SUM(H5:H10).4
Interpret the result. Is the NPV positive or negative? What does that tell you about whether this option is worth pursuing?
Hints
Year 0 is special: the city pays $200,000 upfront but receives no benefits yet. So Year 0 net cash flow = $0 − $200,000 = −$200,000. For Years 1–5, there are both costs ($75,000/yr) and benefits (two components you add together). Make sure you sum the benefit components before subtracting costs.
Option A annual benefits: $70,000 (staff savings) + $40,000 (efficiency) = $110,000/yr. Annual net cash flow for Years 1–5 = $110,000 − $75,000. Compare that annual net against the $200,000 upfront investment — how many years does it take to break even, before discounting?
With only 5 years of net cash flow, ask yourself: does the sum of all discounted annual cash flows exceed the $200,000 upfront cost? Check whether your NPV is positive (the investment paid off) or negative (it did not). A negative NPV means the city would lose money in present-value terms by choosing this vendor.
Verify Task 2 — Option A
Enter your Option A results (round to nearest dollar; NPV may be negative):
Annual net cash flow (Yrs 1–5):
Year 3 PV of net cash flow:
Option A total NPV:
Task 3
Pending
Option B — CityServe (8-Year Contract)
Apply the same year-by-year process to Option B. Note that this contract runs 8 years, so your cash flow table will have rows for Years 0–8.
Option B Data (from the scenario table)
Upfront (Year 0): $150,000 | Annual Cost: $50,000/yr
Staff Savings: $50,000/yr | Efficiency Gains: $30,000/yr
Staff Savings: $50,000/yr | Efficiency Gains: $30,000/yr
=SUM(C5:D5) =B5+E5 =F5*G5 =SUM(H5:H13) for NPV
Column B costs are negative (cash outflows). Net cash flow = B + total benefits. Same structure as Option A.
Step-by-Step Instructions
1
On the Option_B sheet, follow the same column structure as Option A. Year 0 net = −$150,000 (upfront only).
2
For Years 1–8: sum the two benefit components into column E (
=SUM(C5:D5)), then net cash flow in column F (=B5+E5 — column B costs are negative), discount factor in column G, PV in column H.3
Sum column H (PV) in the NPV row. Is this NPV positive or negative? How does it compare to Option A?
Hints
Option B has a lower upfront cost than Option A ($150k vs $200k) but also lower annual benefits. The key question is whether 8 years of discounted net cash flows more than offset the upfront cost. Start by computing total annual benefits = $50,000 + $30,000.
Option B annual benefits: $50,000 + $30,000 = $80,000/yr. Annual costs: $50,000/yr. Net cash flow Years 1–8 = $80,000 − $50,000. Each year’s PV = that net × the discount factor for that year (which decreases each year).
Check your work: the discount factor for Year 8 is approximately 0.6768. The PV of Year 8’s net cash flow should be noticeably smaller than Year 1’s PV — discounting at 5% for 8 years means you only get about 68 cents on the dollar for Year 8 cash flows.
Verify Task 3 — Option B
Enter your Option B results (round to nearest dollar):
Annual net cash flow (Yrs 1–8):
Year 5 PV of net cash flow:
Option B total NPV:
Task 4
Pending
Option C & Final Decision
Complete Option C (10-year horizon), then fill in the comparison table on the Comparison sheet with all three NPVs. Based on your results, which vendor should Northwood select — and which should be rejected outright?
Option C Data (from the scenario table)
Upfront (Year 0): $180,000 | Annual Cost: $55,000/yr
Staff Savings: $85,000/yr | Efficiency Gains: $45,000/yr
Staff Savings: $85,000/yr | Efficiency Gains: $45,000/yr
=SUM(C5:D5) =B5+E5 =F5*G5 =SUM(H5:H15) for NPV
Same structure as Tasks 2 and 3. Column B costs are negative. Rows cover Years 0–10.
Step-by-Step Instructions
1
Complete the Option_C sheet: total benefits (two components), net cash flow, discount factor, PV each year, NPV total.
2
On the Comparison sheet, enter the NPV for each option. Identify which options have positive NPV and which (if any) have negative NPV.
3
Write your recommendation: which vendor offers the best value, and which option should the city reject? Justify using NPV.
Hints
Option C has the longest contract (10 years) and the highest annual benefits. Even though its annual costs are slightly higher than Option B, the benefit components are substantially larger. Start by computing total annual benefits: $85,000 + $45,000.
Option C annual benefits: $85,000 + $45,000 = $130,000/yr. Annual costs: $55,000/yr. Net cash flow = $130,000 − $55,000 = $75,000/yr. This net is more than double Option B’s, and the 10-year horizon means more years of discounted benefits.
When comparing all three, a negative NPV means the option destroys present value — reject it. Among positive-NPV options, higher NPV = better choice. The option with the highest NPV is the one where the city gains the most in present-value terms.
Verify Task 4 — Option C & Decision
Enter your Option C results (round to nearest dollar):
Annual net cash flow (Yrs 1–10):
Year 8 PV of net cash flow:
Option C total NPV:
Discussion
Analysis Questions
Option A has positive annual net cash flows ($110k benefits − $75k costs = $35k/yr), yet its NPV is negative. How is that possible?
- The $200,000 upfront cost is paid in full in Year 0, while the benefits trickle in over 5 years — discounted.
- In present-value terms, 5 years of $35,000 discounted at 5% is worth less than $200,000.
- NPV captures this mismatch. Undiscounted payback analysis would miss it.
Option C costs $180,000 upfront — less than Option A — yet delivers a far higher NPV. Why?
- Its annual net cash flow is much higher ($75,000/yr vs. $30,000/yr for Option B).
- The 10-year horizon means more years of high net cash flows to accumulate.
- Even with discounting, 10 years of $75,000 net cash flow at 5% generates significant present value.
- How would the result change if the city’s discount rate were 12% instead of 5%?
NPV gives a rigorous financial answer, but it is not the only input to a real procurement decision.
- Are the projected benefit estimates ($85k/yr staff savings for Option C) realistic and verifiable?
- A 10-year commitment to MuniSoft creates vendor lock-in. What happens if the vendor raises prices or goes out of business?
- Does the city have $180,000 available for the upfront cost, or would it need to borrow?
- How do political cycles (e.g., mayoral terms) interact with a 10-year contract?
Answer Key