Paycheck Expense Forecast

[1Email2RuleThemAll]

Overview

If you find value in this solution, please upvote the topic in the upper left. It helps other community members find the solution, as well as makes it eligible for an award!

What this workflow does

The Paycheck Expense Forecast is a report that allows you to plan for future paycheck expenses. This solution:

• Generates 1-6 paycheck periods from pay info entered into my Income Settings solution, and displays them in dedicated “paycheck lanes”
  • Freely look at past, present, and future paycheck periods at will
• Dynamically generates all recurring expenses that will occur within these paycheck periods using values entered into my Recurring Expenses solution, and places them under the lane in which they will be due
• Displays the total amount available per paycheck after accounting for all recurring expenses
• Displays aggregated totals for any number of paycheck lanes, starting from the first lane
• Provides a Credit Card View that, when selected, uses my Credit Card Settings solution to move expense totals set to be paid from a credit card to the appropriate statement
  • The totals will display under the paycheck lane in which that statement will be due
• Provides an option to Include All Transactions that, when selected, will also display all transactions that occurred in a given period that was not a recurring expense

Goal of this workflow

The goal of this report is to better understand your upcoming available cashflow, which in turn allows for better savings planning. This report takes away the guesswork from when an expense or a credit card will need to be paid, and allows you to see a clear picture of your short-term finances.

You can use the various different views to answer questions like:

• How much will I have left over from each check when accounting for recurring expenses?
• How much will I have left over from each check when accounting for credit card totals?
• Can I move $200 to savings and still cover everything needed until next payday?
• Do my paychecks consistently cover my recurring expenses?
• Will I have a period coming up where more expenses fall onto one check than the other?
  • Should I pay an expense early to help free up some cash for that period?
• Will I have a period coming up where I can save more money than I initially planned?
• How many transactions have I made that are outside of my normal recurring expenses?
  • How did these purchases affect my total available amount?
• Am I putting more expenses onto credit cards than I have cash flowing in?

Dependencies

The below required dependencies must be present. If they are not present, they should be installed and set up in the order that they appear below. If you already have one of my community solutions mentioned below installed, check to make sure that your version meets the minimum version requirements listed. If it does not, look for the instructions in that sheet’s documentation to upgrade the sheet before proceeding.

Required

Tiller Core Sheets

The following Tiller core sheets are required. Copies of these sheets are present in this solution. Their tabs are colored blue:

• Categories - Used to pull back a list of Categories
• Accounts - Used to pull back checking, savings, and credit card account info.
• Transactions - Used to determine if a transaction has been tied to an expense, marking it paid.

Tiller Community Solutions

The following Tiller Community Solutions are required for full functionality of the dashboard. Copies of the below sheets are present in this solution to show full functionality. Their tabs are colored purple.

Install in the order they appear:

• Income Settings - (min. version 2.0): Used to determine your pay cycle.
• Credit Card Settings - (min. version 2.1): Pulls in custom metrics calculated for credit cards.
• Recurring Expense Entry - (min. version 2.0): Used to pull in a list of Recurring Expenses.
  • This solution has a dependency on the Credit Card Settings sheet, so install that sheet first.

Optional

Tiller Community Solutions

The following Tiller Community Solutions are optional. Copies of the below sheets are present in this solution to show full functionality. Their tabs are colored orange:

• Upcoming Recurring Expense/Account Health Dashboard - (min. version 1.0):
  • When this dashboard is installed, and the first paycheck lane is the next upcoming pay period, the amount pulled back for checking accounts will be the available amount on this dashboard.
  • See the Notes section below for an explanation of how I use these sheets together.
  • If you named the Upcoming Expenses sheet to anything else, you must either rename it, or enter the new name for the sheet in cell BZ62
  • 

Solutions that have a dependency on this solution

There are no current solutions that have a dependency on this solution

Installation

Dependencies must be present in your solution before installation. The Tiller core sheets should already be present, but you have to ensure that any required community built solutions present above are installed (click on the solution name to be taken to its install documentation). Afterward, the below steps must be followed in the order listed.

To install

1. Make sure all dependencies outlined above are installed
2. Open the attached workflow
3. Add the Paycheck Expense Forecast sheet to your workbook
   a. Right click on the Paycheck Expense Forecast sheet and copy to your Tiller workbook
   b. Go to your workbook and rename the sheet
   • From: Copy of Paycheck Expense Forecast
   • To: Paycheck Expense Forecast
     • *Or whatever else you’d like

To Uninstall

• Right-click and delete the Paycheck Expense Forecast sheet.

Setup & Usage

The only setup required is in installing/setting up the dependency solutions according to their documentation, and installing the new sheet for this solution. Afterward, the report should display the data you want in order to keep you on track! After everything is installed and your info has been entered into Income Settings, Credit Card Settings, and Recurring Expense Entry, you can go to the report page and start viewing the data!

How it Works

Report Settings

• Paycheck Amount - Controls if the amount displayed for each paycheck is the Average Gross or Net pay, calculated on the Income Settings sheet.
  • You can enter a custom amount in the cell instead of selecting Net or Gross, and that value will be used instead.
    
• View Start Date - You can select any past, present, or future pay date here to see a list of expenses that occurred, or will occur, during that timeframe.
  • If left blank, the start date will default to the next paycheck period. This can be changed to default to the current paycheck period instead in the Additional Settings section.
  • If you select a date that is not a pay date, it will automatically adjust to the closest pay date.
    
• # of checks to Generate - Controls how many paycheck lanes should generate, from 1 to 6.
• # of checks to Aggregate - Controls how many paycheck lanes to include in the totals section.
  • The selections available depend on how many checks are being generated. You cannot aggregate more checks than you generate.
• Credit Card View - Controls the view of the report. When selected, any expenses that are set to be paid from a credit card will disappear, and credit card totals will appear in blue. The totals for the removed expenses will be included in the appropriate credit card statement.
  • Note: These statement totals are calculated off of the current Prior Close Day on the Credit Card Settings sheet. Since statement end dates can change, the totals for future credit card statements can be slightly off if they do not close on the same day.
  • When selected, current and prior pay periods will also display any credit card payments made within the given period. These entries will show up as a teal-like color.
• Include All Transactions - Selecting this option while viewing the current period, or any period in the past, will additionally display all transactions that occurred during the given period that are not tied to a recurring expense. These values appear in grey italicized font.
  • When the Credit Card View option is selected, transactions that were paid on a credit card will not display. Instead, their totals will be included in the appropriate statement for that credit card.

Import Starting Balance

This section allows you to import a starting balance from a checking account, which will be added to the final totals box at the top of the report. If the Upcoming Expenses dashboard is installed, and the first paycheck lane is the next occurring paycheck period, the balance that is pulled back will be the available balance on the Upcoming Expenses sheet for the given account. This balance is the current balance less the expenses that are still due in the current pay period.

• Manual +/- Adjustment - Allows you to enter a manual amount to add or subtract from the total. This can be used with or without a starting checking balance present. The amount entered here will be included in the final totals calculation.
  

Report Data

All recurring expenses will be displayed under the paycheck lane in which they are due, along with their budgeted amounts. When the Credit Card View is selected, recurring expenses set to be paid from a credit card (on the Recurring Expense Entry sheet) will move to the appropriate statement for the credit card they are set to be paid by, and show their total under the paycheck lane in which that statement is due.

• When the correlating transaction in the Transactions sheet is tagged using the Expense Tag column (introduced when installing the Recurring Expense Entry workflow), the expense will turn green, and the actual amount in the Transactions sheet will be displayed in place of the budgeted amount.
  • There are certain windows of time that each expense frequency is looking at in order to determine if it has been paid or not. If an expense is due weekly, for example, it will be marked paid and turn green as long as the posted date of the transaction that you tie it to in Transactions is within +/- 3 days of the expense due date. This is to avoid spilling over into the next expense window and marking things incorrectly. The expense window for each frequency is below:
    • Weekly: +/- 3 days
    • Bi-weekly: +6 / -7 days
    • Semi-monthly: +6 / -7 days
    • Monthly: +14 / -13 days
    • Bi-monthly: +/- 29 days
    • All Else: +/- 60 days
• Expenses that have not been tagged, and the due date has passed, will turn red.
• When the Credit Card View is selected, credit card totals will show up in blue.
  • Past credit card payments will show up a teal-like color
• When Include All Transactions is selected, transactions that have not been tied to a recurring expense will be displayed in grey italicized font.

Additional Options

To exclude a category from showing up when Include All Transactions is selected, expand the +/- symbol above column Z, Additional Settings. Here, you can choose to exclude all transfer categories and all hidden categories, as well as select individual categories to add to the exclusion list.

• Note: This only affects transactions in the Transactions sheet that are not tied to a recurring expense.
  
  

Notes

Companion Solution - Upcoming Expenses

Paycheck Expense Forecast is a great companion to my Upcoming Recurring Expense/Account Health Dashboard. If you have that solution installed, I highly recommend this report. If you do not have that solution installed, I highly recommend installing it along with this one.

I use the Upcoming Expenses dashboard to evaluate the current pay period and make sure I’m on track with paying expenses. I then normally keep the Paycheck Expense Forecast sheet set to start on the next upcoming pay period (which is the default view when no date is provided). When the report starts on the next upcoming pay period, it pulls the available balance from the Upcoming Expenses dashboard when importing a checking balance. This allows me to have a full picture of my short-term financial health. I then adjust the view start date every now-and-then to see more into the future, or to check how past pay periods shook out.

The difference between the current view on the Upcoming Expenses dashboard and the current view on this report is that paid expenses disappear from the Upcoming Expenses dashboard (because the amount was applied to another total on the dashboard after it was paid), whereas they turn green and display the total amount paid on the Paycheck Expense Forecast report(because the goal of this report is to see the total amount of money that has/will affect a given paycheck).

FAQ

Please ask any questions you have below and I will reply, and update this section accordingly.

Solution Link

Check Expense Forecast

Version Changes

The initial release version of all sheets will be version 1.0. When a change is made, this number will increment. If the version number jumps to the next whole number, this indicates the need to reinstall the sheet using the steps outlined below. If not, it’s a relatively minor fix that can be applied by following the steps outlined in the appropriate documentation below.

Changes to Base Sheets

When a base sheet has a minor change, it will usually not cause any issues in this sheet. If it does, a major or minor version change update in the below To This Solution section will indicate the steps necessary to take in order to get everything up-to-date. If a major update was made to a base sheet that required it to be reinstalled, some reference links could break in this sheet. To re-establish these references and get everything back in order, follow the below steps after reinstalling the base sheet.

Fix #REF errors arising from reinstalling a base sheet

Sometimes Google Sheets gets hung up and will not recognize that a sheet has been added to the workbook. This causes the dynamic reference section to generate #REF errors, and stops functionality. To fix, follow the below steps

Scroll to column Z and unhide the computational columns by pressing the highlighted arrow above. Scroll all the way to the end of the columns to the Sheet References section. Do not change values in any other sections. If any of the boxes in the Sheet References section contain #REF errors:

• Select the header cell that contains the sheet name and press Enter to enter into the cell
  • This will be the value in the dark blue field
• Press CTRL+X to cut the name out of the cell and copy it to your clipboard
• Press Enter to save the (now-empty) cell value
• Highlight the cell again and press enter
• Press CTRL+V to paste the sheet name back in and press Enter again

Once this is done, Sheets will have an “Oh yeah!” moment and recognize that the sheet is present. When there are no more #REF errors present, collapse all of the groups using the +/- arrows at the top and hide everything to the right of Additional Settings.

This simple fix will also resolve any issues that would arise if you installed this solution before any of the sheets that it depends on.

To This Solution

If you are jumping multiple minor versions, you can either apply all of the minor fixes individually and manually update the version number, or re-install the sheet using the instructions below.

For versions that require a re-install of the sheet:
Remember to check any solutions that have a dependency on this solution, and follow their documentation for when these sheets are replaced.

1. Delete Paycheck Expense Forecast sheet
2. Follow instructions in the Installation - To Install section above

[1Email2RuleThemAll]

@jjfelton4 I remember that you requested that I tag you when I release this solution. Sorry it took me so long! I’ve had a lot of things come up that required my full attention.

[MidnightPrzm]

Awesome solution yet again. I do have a question though. For people that are using the Paycheck solutions that allow us to add our tax deductions how would we incorporate any additional income. For example, for me I receive a non-taxed stipend that is apart from my gross so when I subtract my federal, state, Medicare, and social security deductions I get my net like normal, but I also add my stipend as an additional deduction (but positive) and that makes up my income. Only the full net is showing up as my paycheck, yet I should have a considerable additional income making up my net. I did mess around with the gross vs net dropdown within the report settings section. The gross amount seems to be far higher than the net amount. That should be opposite as my gross is lower than my net due to the addition of the stipend. Any advice on this?

[1Email2RuleThemAll]

Unfortunately, since this uses my base Income Settings sheet, it can only select/track one income source at a time. That is a limitation that I’m hoping to remove in the future with a different solution that I currently have in the works.

For now, though, there is a workaround in the report. You can override the net/gross value with a custom value, if you’d like. Enter the average amount of your paycheck net pay + stipend into column G3, Paycheck Amount. There is logic to automatically add the value you enter to the selectable list, so you don’t get an invalid value error.

The value you enter will be used as the average paycheck amount for all lanes instead of net or gross.

[MidnightPrzm]

Would I be able to edit the dropdown and add a new option. So gross, net, and then add a manual amount? Maybe even use a formula in reference to another cell on another sheet. I would play around with it or just type in that amount. Typing the manual amount would erase the gross & net options, right?

I am definitely looking forward to the new solution you have in the works.

[1Email2RuleThemAll]

No, entering a manual amount will not erase the option to choose gross or net. It will just append that amount as an option that can be selected. Just manually enter the amount in cell G3

If you want to change this to a custom formula that outputs an amount, it would be best to place that formula in cell AE16. The output of that formula will then be available as a selection instead of a manual entry override. To get to cell AE16, you have to unhide the columns past column Z. It will be in the Paycheck/Account Metrics group. The current formula in this cell is below, in case you want to go back to the manual entry logic.

=IF(OR(G3 = "Net", G3 = "Gross"), IFERROR(1/0), G3)

Also, make sure to collapse all the groups and hide them again to make things look nice

[MidnightPrzm]

Thank you. It seems to be showing the full amount now for some reason. Maybe it’s because I have my net pay as the only source of income, and I have the gross selection blank. I did have a week off in between my contract where I wasn’t paid so maybe that was why it was showing the lower amount.