Forecast Attainment % in Excel (Booked vs Forecast)

Excel Walkthrough for Forecast Attainment

1. Confirm the required columns exist in your dataset

• Forecast close date expectation (Column O)
  
• Actual close date (Column T)
  
• Forecasted booking amount (estimated value weighted by probability)
  
• Actual booked revenue
  

2. Create the Forecast Month and Forecast Year columns

• In a new column, extract the forecasted month from the forecast close date using TEXT
  
  • Format: month name using mmmm
    
• In another new column, extract the forecasted year from the forecast close date using TEXT
  
  • Format: year using yyyy
    

3. Create the Closed Month and Closed Year columns

• Repeat the same TEXT approach, but reference the actual close date column (Column T)
  
• This is what will correctly account for slippage when deals close in a different month than forecasted
  

4. Build a unique list of Month and Year combinations

• Create a unique list from the forecast month and year values so you have a clean timeline (Sep 2025, Oct 2025, etc.)
  
• Use UNIQUE to generate the list quickly
  
• Copy the output and paste values (Ctrl + Shift + V) to remove the formula and keep the list stable
  

5. Add a combined Date label column for charting

• Insert a new helper column labeled Date
  
• Combine Month and Year into one label using & so your chart axis displays cleanly
  
  • Example logic: Month & “-” & Year
    
• Fill down the column
  

6. Sum the Forecasted Amount by forecasted month and year

• Use SUMIFS to sum the forecasted booking amount
  
• Criteria:
  
  • Forecast Month equals the month in your summary table (for example, B3)
    
  • Forecast Year equals the year in your summary table (for example, C3)
    
• Format as currency (Ctrl + Shift + 4)
  
• Fill down for all months
  

7. Sum the Actual Booked Revenue by closed month and year

• Use SUMIFS again, but sum the actual booked revenue column
  
• Criteria:
  
  • Closed Month equals the month in your summary table
    
  • Closed Year equals the year in your summary table
    
• Format as currency (Ctrl + Shift + 4)
  
• Fill down for all months
  

8. Calculate Forecast Attainment %

• In the attainment column, divide:
  
  • Actual Booked Revenue ÷ Forecasted Amount
    
• Format as a percentage (Ctrl + Shift + 5)
  
• Fill down
  

9. Create the chart

• Highlight the Date column through Attainment % for the full range
  
• Insert a chart using Recommended Charts
  
• The most useful setup:
  
  • Forecasted Amount as one series
    
  • Actual Booked Revenue as another series
    
  • Attainment % as a line series (on a secondary axis if needed)
    

10. Clean up the chart formatting

• Rename the chart title (example used in the video: Attainment Percent by Month)
  
• Adjust the line style for the attainment series:
  
  • Change color if desired
    
  • Adjust thickness
    
  • Keep points visible if you prefer
    
• Add data labels only to the attainment line if you want labels without noise
  
• Manually reposition a few labels if they overlap
  

11. Optional slicing ideas mentioned

• Repeat the same exact process by swapping the Month and Year criteria for:
  
  • Sales rep
    
  • Region
    
  • City
    
  • Lead source
    
  • System type
    

The formulas stay the same, only the grouping columns change