Embedded Files
In this section we create those processes that read our inputs and produce calculated results. In this model, our calculations schedule when each budget item is due and when each income item is received.
Schedules require calendars which we do not have, but which we can calculate from the user's entry in the Model Properties table for Start. We will display these dates horizontally across the top of our calculations.
To schedule something we need to know when it happens. We can calculate that from when a budget items starts (First Due Date) and its Recurrence code.
We must do this for each budget item so to provide a means to link back to our Budget Items table we will bring forward its ID column.
Days
Days
Every year has 365 days except leap years which have 366. This calculate the correct number of days in the year no matter what year is entered in Start. Enter Days in Calcs!B3 and this formula in Calcs!C3:
=EDATE(Start,MPY)-Start
Select Calcs!B3:C3 then use Excel's Create from Selection option to name the result Days.
Date
Date
This provides the header for our budget calculations. The header contains all dates in the year. Type this formula in cell Calcs!B6. It will SPILL across automatically.
=Start + SEQUENCE(,Days,0)
Start is the start date entered by the user.
SEQUENCE() is an array function that creates a list of numbers (more on that function here).
Use Name Manager to name this cell: Date. as shown at right. NOTE! The # at the end designates this as a dynamic array reference to include the SPILL region.
ID
ID
This replicates the ID from our budget entries down the left side of our budget calculations. We will use this ID to link the calculations to budget entries in Power Query. Enter ID in cell Calcs!A5 and this formula in Calcs!A6. It will SPILL down automatically.
=tblItems[ID]
This is the structured reference from our budget items entry table for the ID column.
Amounts
Amounts
This is our main calculation. It places the estimated budget amounts under the dates in which they will occur. It is a monster. If someone has a better solution, please let me know! Enter this formula in Budget!B6. It will SPILL down and across automatically.
To better understand this formula we should first know that LET() has 2 major sections. The first defines the names we want to use in the second part. The second part is contains a formula that is made less complex by those names. In this LET() formula we have three names. Due, End, and SameDay.
LET(
Due,tblItems[First Due Date],
use in the formula that follows after the comma.
End,tblItems[End Date],
LET() allows defining as many named variables as we like and in this instance we are defining End to mean when a contract terminates or some expenditure stops.
SameDay,DAY(Date)=DAY(Due),
This is the third variable name we defined in our LET() section. SameDay means the same day of the month for our header's Date as our Due date.
Now that we have some names defined, what follows is the formula that uses those names. This formula has two sections. The first, and by far the largest are tests. These formulas are testing to see if the budget item in the current row occurs in the Date of the current column.
(Due<=Date)*((End>=Date)+(End=0))
This portion of our formula determines if the Date in the header is within the Due and End dates. It is conceptually the equivalent of IF(AND(Due<=Date, OR(End>=Date, END=0)), <true section>, <false section>). Unfortunately, AND() and OR() functions work differently in array formulas. They compare the entire array as opposed to by position within the array. We need to apply this test to each position in the array and so we turn to a quirk in Excel where TRUE equates to 1 and FALSE equates to 0. So if 2 comparisons return TRUE and we multiply them we get 1. If either is FALSE, we get 0. Functionally this is the same and ANDing comparisons. To OR() two comparisons we add them. So if either is TRUE adding them returns 1. This works as long as we know both conditions cannot be TRUE at the same time. In this situation, End cannot be both >= Date and = 0 so it is safe to use this method. When it is possible all comparison could equate to TRUE, adding them would return more than 1. We need it to return 1 or 0 and nothing else. So in the case where multiple comparisons could all be TRUE, to OR then we must add them, divide the sum by the number of comparisons, then ROUNDUP the result.
SWITCH(tblItems[Recurrence],
This portion of our formula applies the appropriate test as determined by the recurrence code. If the recurrence code is M for monthly then we have a formula for that. If it is Q for quarterly, we have a different formula for that. To make the switch between tests we use a new function: SWITCH() (Excel 2021 or Office 365).
"A", Date=DUE,
If the item recurs annually then the Date in our header that equals FDD (First Due Date) will be TRUE.
"S", SameDay*(MOD(MONTH(Date),Semi_Annually)=MOD(MONTH(Due),Semi_Annually)),
The Semi Annually test uses a function many may not be familiar with: MOD(). MOD() has been around a while and returns the remainder of a division. The first number is what is divided. The second number is the number by which we divide the first number.
If the item recurs semi annually then:
SameDay
Our header's Date must be the same day in the month as the DUE date AND
MOD(MONTH(Date),Semi_Annually)=MOD(MONTH(Due),Semi_Annually)
The remainder of our header's month divided by 6 (Semi Annually repeats every 6 months) must be the same as the remainder of item's DUE
"Q", SameDay*(MOD(MONTH(Date),Quarterly)=MOD(MONTH(Due),Quarterly)),
If the item recurs semi annually then:
SameDay
Our header's Date must be the same day in the month as the DUE date AND
MOD(MONTH(Date),Quarterly)=MOD(MONTH(Due),Quarterly)
The remainder of our header's month divided by 3 (Quarterly repeats every 3 months) must be the same as the remainder of item's DUE date divided by 3.
"M", SameDay,
If the item recurs monthly then our header's Date must be the same day in the month as the DUE date
"B", MOD(Date,Biweekly)=MOD(Due,Biweekly),
If the item recurs biweekly then the remainder of our header's date divided by 14 days must be the same as the item's DUE date.
"W", MOD(Date,Weekly)=MOD(Due,Weekly),
If the item recurs weekly then the remainder of our header's date divided by 7 days must be the same as the item's DUE date.
"X", WEEKDAY(Date,Mon2Sun)<=WorkDay,
The Week Day test uses a function many may not be familiar with: WEEKDAY(). WEEKDAY() has been around for a while and returns a date's day of the week. The number that WEEKDAY() returns depends on the second argument. In this case, we want to return 1 when the date is on a Monday and 7 when the date is on a Sunday.
If the item recurs only on week days then the day of the week must be less than or equal to the last workday (5=Friday).
"D", TRUE,
If the item recurs daily then return TRUE.
*tblItems[Estimate]
The tests will produce a 1 if they pass or a 0 if any fail. After performing tests, we multiple the results by the estimated amount. If all test pass, this formula will return the estimated amount. Otherwise, it returns 0. And because this is a dynamic array formula, the SPILL range applies the results for all dates and all budget items.
Incredibly, that is all the formulas we need to enter. And no matter how many budget items are required, or when the budget year starts, we will never have to do anything to these formulas as they will automatically resize themselves as needed.
Page updated
Google Sites
Report abuse