Spreadsheet: 752849d1635011332-scheduling-value-added-tax-payments-with-a-few-variables-to-spice-the-mix-vat-scheduling
Sheet: Vat

 | Financial Period
 | Begin | 2021-03-01 00:00:00 | Date
 | End |  | Date
 | Months per Period | 6 | Months
 | Days per Period |  | Days
 | Financial Year |  | Year
 | Development
 | Start Date
 | Duration
 | End Date
 | Start Date Flag
 | Period Flag
 | Period Number
 | End Date Flag
 | Cumulative Escalation Counter
 | Escalation Rate
 | Escalation Factor
 | Construction
 | Start Date
 | Duration
 | End Date
 | Start Date Flag
 | Period Flag
 | Period Number
 | End Date Flag
 | Cumulative Escalation Counter
 | Escalation Rate
 | Escalation Factor
 | Amount of Vat Payable in total for each Financial Period |  |  |  | 4 | 3 | 5 | 6 | 3.5 | 4 | 2 | 8 | 5 | 6 | 6.5 | 8
 | [NAME] | 1
 | For each 2 month period ending | 1 = Jan, [NAME], May, Jul, Sep, Nov
 | or | 2 = Feb, Apr, [NAME], Aug, [NAME], Dec
 | For each 1 month period ending | 3 = Jan, Feb, [NAME], Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
 | Payment due 1 month / 30 days after each period ending:
 | eg. [NAME] Type 1 for period Feb & Mar will be paid in Apr (column C and indicated by switch in column D)
 | eg. [NAME] Type 2 for period Mar & Apr will be paid in May (column C and indicated by switch in column D)
 | eg. [NAME] Type 3 for period [NAME] will be paid in Apr (column C and indicated by switch in column D)
 |  | Pay Period | Switch |  | Payment 1 | Payment 2 | Payment 3 | Payment 4 | Payment 5 | Payment 6 | Payment 7 | Payment 8 | Payment 9 | Payment 10 | Payment 11 | Payment 12
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  | Total Payable Per period
 |  |  |  |  | Problem:
 |  |  |  |  | I need to create a payment schedue incorporating each Financial Period, taking into account, the following variables
 |  |  |  |  | - the financial period should be variable between 1 and 12 months (input into C6)
 |  |  |  |  | - the Vat Payment type should be a choice of either Type 1, 2 or 3 (input into C36
 |  |  |  |  | - type 1 & 2 consists of 2 month blocks each staggered 1 month apart
 |  |  |  |  | - type 3 consists of a 1 month block
 |  |  |  |  | - all 3 choices are paid 30 days / 1 month after the end of each block
 |  |  |  |  | For Example:
 |  |  |  |  | - If the Financial Period chosen is 6 months
 |  |  |  |  | - The Vat Type 1 is chosen, then:
 |  |  |  |  | -  first payment to be made in Apr (Pay Period 4) and will include Feb from the prior financial period & Mar from the current financial period (one month set off)
 |  |  |  |  | -  next payment to be made in [NAME] (Pay Period 6) and will include both Apr & May from the current financial period (zero set off)
 |  |  |  |  | -  last  payment to be made in Aug (Pay Period 8) and will include both Jun & Jul from the current financial period (zero set off)
 |  |  |  |  | - The Vat Type 2 is chosen, then:
 |  |  |  |  | -  first payment to be made in [NAME] (Pay Period 3) and will include both Jan & Feb from the prior financial period and Zero from the current financial period (two month set off)
 |  |  |  |  | -  next payment to be made in May (Pay Period 5) and will include both Mar & Apr from the current financial period (zero set off)
 |  |  |  |  | -  last  payment to be made in Jul (Pay Period 7) and will include both May & Jun from the current financial period (zero set off)
 |  |  |  |  | My attempt so far:
 |  |  |  |  | I think I am clear about the variables.
 |  |  |  |  | I think I have been able to create a switch in column D indicating when each type is payable.
 |  |  |  |  | I know that the solution will involve some OFFSET functions.
 |  |  |  |  | That’s it . . .I have no clue how to go any further.
 |  |  |  |  | ANY HELP WOULD BE APPRECIATED.
 |  |  |  |  | PS FORGIVE ALL THE COLOUR -  I KNOW ITS BAD MODELLING PRACTISE. JUST THOUGH IT WOULD EASIER TO EXPLAIN