A basic forecast helps you explore what could happen when a few important assumptions change. It does not predict the future with certainty. Its value comes from making the assumptions visible, carrying the calculations forward consistently, and showing how the result changes under different planning cases.
In this project, you will build a 12-month Microsoft Excel forecast for a made-up subscription service. The model begins with 100 customers, adds new customers each month, subtracts customers who leave, and calculates monthly revenue.
By the end, you will have base, downside, and upside planning cases, a month-by-month customer schedule, a revenue calculation, model checks, and a decision log explaining the choices behind the forecast.
What the finished forecast includes
The downloadable workbook contains three main working areas:
- Assumptions: the starting customer count, monthly additions, monthly churn rate, price, and selected planning case.
- Forecast: a 12-month schedule showing beginning customers, additions, churned customers, ending customers, price, and revenue.
- Decision Log: the assumption or choice, alternatives considered, reason selected, and signal that would cause an update.
The practice workbook includes three planning cases:
| Planning case | Starting customers | Monthly additions | Monthly churn | Price |
|---|---|---|---|---|
| Base | 100 | 12 | 5% | $25 |
| Downside | 100 | 8 | 8% | $23 |
| Upside | 100 | 16 | 3% | $27 |
Use the accompanying Basic Forecast Model workbook to follow the project and inspect the finished setup. It includes made-up practice assumptions, working formulas, planning-case controls, a customer chart, model checks, a decision log, instructions, documented checks, a failure-and-repair example, and version history.
Workbook note: The file uses made-up practice assumptions and contains no real customer information. It is provided for educational and general informational use. Review every assumption, formula, limitation, and compatibility issue before adapting it to real financial or business decisions.
Start with the question the forecast should answer
Before entering assumptions, decide what the model is for. A useful question for this project is:
How could customer count and monthly revenue change over the next 12 months under three different planning cases?
This keeps the model focused. It does not attempt to forecast cash flow, expenses, taxes, payment timing, customer segments, annual contracts, or profit.
Keep assumptions separate from formulas
Open the Assumptions sheet. Inputs should be easy to find and change without editing the forecast formulas.
For each assumption, record:
- The value
- The unit, such as customers, percentage, or dollars per month
- The planning case
- Why the value was chosen
- What new information would cause it to change
The workbook uses a drop-down to select Base, Downside, or Upside. The forecast formulas use INDEX and MATCH to retrieve the assumptions for the selected case.
A planning case is a consistent set of assumptions. It is not a probability unless you have separately estimated and supported the likelihood of that outcome.
Build the monthly customer schedule
The Forecast sheet uses one row per month. Each row contains:
- Beginning Customers
- New Customers
- Churned Customers
- Ending Customers
- Price
- Revenue
- A calculation check
The customer calculation is:
Beginning customers + new customers – churned customers = ending customers
The formula follows this pattern:
=MAX(0,B6+C6-D6)
MAX prevents the displayed customer count from falling below zero. It does not correct a poor assumption, so unusually high churn should still be reviewed.
Work through the first base-case month
The Base planning case starts with:
- 100 beginning customers
- 12 new customers
- 5% monthly churn
- $25 monthly price
The workbook rounds churned customers to a whole number:
100 × 5% = 5 churned customers
The ending customer count is:
100 + 12 – 5 = 107 ending customers
The revenue calculation uses ending customers multiplied by price:
107 × $25 = $2,675
This is a deliberate simplification. A more detailed model might use average customers during the month, daily billing, contract start dates, or deferred revenue. The Decision Log explains why the simpler method was selected for this learning project.
Carry the ending value into the next month
Month two should begin with the 107 customers from month one. Each later month follows the same pattern: the prior month’s ending customers become the next month’s beginning customers.
The workbook advances the date with EDATE:
=EDATE(A6,1)
EDATE moves the date forward by one calendar month. This avoids the uneven results that can occur when a model adds a fixed number of days to represent a month.
Compare the three planning cases
Each planning case should tell a coherent story.
- Base: moderate additions, moderate churn, and the middle price.
- Downside: fewer additions, higher churn, and a lower price.
- Upside: more additions, lower churn, and a higher price.
Do not change assumptions randomly just to make the chart look different. Explain why the values move together and what real information would support the case.
The workbook displays one selected case at a time. Change the selection on the Assumptions sheet, then review the customer schedule and chart.
Run a one-assumption-at-a-time check
A useful forecast should respond in the expected direction when an assumption changes.
Start with the Base case, then change only monthly churn from 5% to 8% in a copy. Leave starting customers, additions, and price unchanged.
You should expect:
- More customers to leave each month
- Lower ending-customer counts
- Lower monthly revenue
If the result does not move in that direction, the assumption may be disconnected from the formulas or a reference may be wrong.
After the check, restore the original value or close the test copy without saving.
Use model checks
The final column confirms that the customer calculation works:
=IF(B6+C6-D6=E6,"OK","FAIL")
Review the model if a row displays FAIL. Also look for:
- Negative prices
- Churn below 0% or above 100%
- Missing assumptions
- Broken links between months
- Negative new-customer values unless they have a clearly defined meaning
- Numbers displayed with more precision than the assumptions justify
Document decisions, not just numbers
The Decision Log explains the choices behind the workbook. The practice version records:
- Why Base monthly additions are set to 12
- Why Base monthly churn is set to 5%
- Why the model uses ending customers multiplied by price
For each important choice, record:
- The assumption or method
- The alternatives considered
- The reason for the selected approach
- The new information that would trigger an update
This makes the forecast easier to revise when better information becomes available.
Avoid false precision
A forecast built from rough assumptions should not appear more certain than those assumptions allow.
- Use whole customers when partial customers have no practical meaning.
- Use sensible currency rounding.
- Do not label a planning case with a precise probability unless that probability has been estimated separately.
- Display limitations near the result rather than hiding them in a note.
The model is a planning tool. It does not guarantee customer growth, revenue, retention, market demand, or business performance.
Try five practical checks
| Check | What to do | Expected result |
|---|---|---|
| First base month | Use 100 starting customers, 12 additions, and 5% churn. | Ending customers equal 107. |
| No change | Set additions and churn to zero in a copy. | The customer count remains stable. |
| Invalid churn | Try to enter a churn rate above 100%. | The input rule blocks or warns about the value. |
| Monthly calculation | Check beginning plus additions minus churn. | The result matches ending customers and displays OK. |
| Higher churn | Raise churn while holding the other assumptions constant. | Ending customers and revenue decline. |
Customize the forecast
After you understand the practice workbook, make a copy and adapt it carefully.
- Replace the practice assumptions with values supported by your own evidence.
- Update the Decision Log when an assumption changes.
- Keep assumptions separate from formulas.
- Add checks for values that should not be negative.
- Use planning-case names that readers can understand.
- Confirm that later months begin with the prior month’s ending value.
- Review the chart after changing the forecast period.
- State what the model leaves out.
The workbook is a completed learning example rather than a complete financial model. It does not include expenses, taxes, cash timing, customer acquisition cost, contract terms, profit, or financing.
Common mistakes to avoid
- Mixing assumptions with formulas: Keep editable inputs visible and separate.
- Calling planning cases probabilities: A downside case is not automatically a 25% outcome.
- Using hidden hardcoded values: Readers should be able to find every important assumption.
- Breaking the month-to-month link: The next month should begin with the prior month’s ending value.
- Changing several assumptions during a single check: Change one at a time when you want to understand cause and effect.
- Showing false precision: Match the displayed precision to the quality of the inputs.
- Treating the forecast as a promise: Results depend on the assumptions and may not occur.
What this project helps you practice
This project gives you experience with separated assumptions, drop-down case selection, INDEX and MATCH, monthly customer calculations, EDATE, IF checks, charts, one-assumption-at-a-time review, and decision logs. It also shows how clear limitations make a forecast more useful rather than less useful.
Continue the project workflow
- Start with a reliable monthly reporting template when historical results should inform the forecast assumptions.
- Prioritize decisions from the forecast cases when the model reveals several possible actions.
- Present the forecast model responsibly if you want to explain the assumptions, formulas, checks, and limitations.