Automating Revenue Forecast Sheet based on Period of Performance and Deal Close Date

I have a revenue‑forecasting sheet where I want to automate how many days (expressed as months or fractions of months) a deal generates revenue inside each quarter. The number of revenue‑generating days depends on two variables:

1. Period of Performance (POP) – the number of months the deal runs.
2. Close Date – the date when the deal closes. Revenue always starts the day after the close date.

Data Structure

• Column G – Period of Performance in months for the entirety of the project.
• Column I – Close date (dd/mm/yyyy). Revenue always begins the next day after the close date (e.g., close on 15 Feb → revenue starts 16 Feb).
• Columns K – N – Number of months of revenue within each quarter (between 0 and 3 months per quarter):
  • K = Q1
  • L = Q2
  • M = Q3
  • N = Q4

The picture below should help:

What my current forecast sheet layout looks like.

How I'm doing it today (manually)

Example 1

• POP: 1 month
• Close date: 15/02/2026

· This means that this deal will generate one month of revenue inside Q1 (16^th February – 15^th of March), so I manually insert “1” on K.

o I will then introduce a “0” on L – N, because there's no Q2 – Q4 revenue.

Example 2

• POP: 5 months
• Close date: 15/06/2026
• Revenue runs from 16 Jun → 15 Nov
• Approx. 0.5 months in Q2, 3 in Q3, 1.5 in Q4 → L = 0.5, M = 3, N = 1.5, K = 0.

Example 3

• POP: 12 months
• Close date: 30/04/2026
• Revenue runs 1 May 2026 → 30 Apr 2027
• 2026 impact: 2 months in Q2, 3 in Q3, 3 in Q4 → L = 2, M = 3, N = 3, K = 0.

What I must ensure when doing this manually

• The sum of K – N never exceeds the POP in Column G.
• Partial months are calculated accurately for K – N, when I'm not delivering full months.
• Quarters with zero revenue should show “0” for clarity.

Quarterly Definitions

• Q1: 1 Jan – 31 Mar
• Q2: 1 Apr – 30 Jun
• Q3: 1 Jul – 30 Sep
• Q4: 1 Oct – 31 Dec

Additional Clarifications

• Multi‑year POPs are fine – I only care about revenue in the year of the close date.
• Example: POP = 8 months, close date = 30 Jun → revenue starts 1 Jul → 3 months in Q3, 3 in Q4; remaining 2 months spill into next year and are ignored on my forecast sheet.
• Leap years don’t need to be accounted for precisely – one day of variance is acceptable.

What I need help with

1. A formula (for Columns K – N) that:
   • Reads the Close Date (Column I).
   • Calculates the exact fraction of the POP that falls within each quarter of the closing year.
   • Starts counting revenue the day after the close date.
2. Ensures the total of K – N never exceeds the POP in Column G.
3. Automatically outputs “0” for any quarter with no revenue.

Many thanks for any support you can offer!

Edit: I'm using Office 365.