I’ve been using https://projectionlab.com recently to do retirement planning. Super helpful to be able to model different decisions. Anyway, one of the inputs is Discretionary and Essential spending per year. I’ve never bothered categorizing my spending that way. I already have a rich set of Groups (18) and Categories (137). So Groups was not available as a tagging method for each category. I also use pivot tables for adhoc reporting on income and spending, and wanted to be able to include a Priority field in pivot tables -which meant that the Priority (Essential or Discretionary) for each category had to be on the Transactions tab.
So today I did this:
-
Added a new column to Categories table called Priority, just to the left of the Type column.
-
Assigned each of my many Categories an Essential or Discretionary value. Because I’m so granular, I can say that Group:Automobile/Category:Purchase is discretionary, but Group:Automobile/Category:Insurance is essential.
-
Added a new column to my Transactions tab called Priority, including a formula at the top to automatically populate it with the correct value based on that transaction’s category. Adding this column means it’s now available for reporting.
Now I can create pivot table reports and group by spending priority, group, etc. Some screenshots:
Adding the Priority assignment to each category:
Adding the Priority value to the Transactions tab:
This is the formula:
=arrayformula(if(row($D$1:$D)=1,"Priority",iferror(vlookup($D$1:$D,{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Priority",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Priority",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))
Now I can easily create a pivot table of just discretionary and essential spending:
Discretionary and essential with group drilldown:
Discretionary and essential with group AND category drilldown:
And finally, here’s my somewhat granular list of categories:
| Category | Group | Priority |
|---|---|---|
| Amazon | Amazon | Essential |
| Auto Insurance | Automobile | Essential |
| Auto Maintenance | Automobile | Essential |
| Car Purchase | Automobile | Discretionary |
| Gasoline | Automobile | Essential |
| License | Automobile | Essential |
| Parking | Automobile | Essential |
| Ticket | Automobile | Essential |
| Tolls | Automobile | Essential |
| Adjustment | Bank Charges | Essential |
| ATM | Bank Charges | Essential |
| Foreign Xfer Fee | Bank Charges | Essential |
| Fraud | Bank Charges | Essential |
| Rebate | Bank Charges | Essential |
| Service Charge | Bank Charges | Essential |
| Wire Transfer | Bank Charges | Essential |
| Yearly Fee | Bank Charges | Essential |
| Charity | Charity | Discretionary |
| Clothing | Clothing | Essential |
| Dave Clothing | Clothing | Essential |
| Sharon Clothing | Clothing | Essential |
| Education | Education | Discretionary |
| Dining Out | Food | Essential |
| Groceries | Food | Essential |
| Furnishings | Furnishings | Discretionary |
| Furniture | Furnishings | Discretionary |
| Kitchen | Furnishings | Discretionary |
| Linen | Furnishings | Discretionary |
| Storage | Furnishings | Discretionary |
| Family | Gifts | Discretionary |
| Friends | Gifts | Discretionary |
| Chiropractic | Healthcare | Essential |
| Dental | Healthcare | Essential |
| Eyecare | Healthcare | Essential |
| Insurance - Health | Healthcare | Essential |
| Labwork | Healthcare | Essential |
| Massage | Healthcare | Discretionary |
| Physical Therapy | Healthcare | Essential |
| Physician | Healthcare | Essential |
| Prescriptions | Healthcare | Essential |
| Reimbursement | Healthcare | Essential |
| Supplies | Healthcare | Essential |
| Therapy | Healthcare | Discretionary |
| Gardening | Home | Essential |
| HOA Dues | Home | Essential |
| Home Insurance | Home | Essential |
| Home Maintenance | Home | Essential |
| House Cleaning | Home | Essential |
| Improvements | Home | Discretionary |
| Purchase | Home | Discretionary |
| Rent | Home | Essential |
| Storage facility | Home | Essential |
| Tools | Home | Discretionary |
| Cabin | Income | Essential |
| Casita Rent | Income | Essential |
| Interest | Income | Essential |
| Investment | Income | Essential |
| Owner Draw | Income | Essential |
| Paycheck | Income | Essential |
| Pioneer | Income | Essential |
| Redmond | Income | Essential |
| Social Security | Income | Essential |
| Dividend | Investments | Essential |
| Reinvestment | Investments | Essential |
| Kids Activities | Kids | Discretionary |
| Kids Education | Kids | Discretionary |
| Kids Travel | Kids | Discretionary |
| Support | Kids | Discretionary |
| Therapy - Kids | Kids | Discretionary |
| Backpacking | Leisure | Discretionary |
| Biking | Leisure | Discretionary |
| Bird Watching | Leisure | Discretionary |
| Books | Leisure | Discretionary |
| Camping | Leisure | Discretionary |
| Canoeing | Leisure | Discretionary |
| Computer | Leisure | Discretionary |
| Cultural Events | Leisure | Discretionary |
| Diving | Leisure | Discretionary |
| Drumming | Leisure | Discretionary |
| Electronics | Leisure | Discretionary |
| Fun | Leisure | Discretionary |
| Horseback Riding | Leisure | Discretionary |
| Kayaking | Leisure | Discretionary |
| Music | Leisure | Discretionary |
| Newspapers | Leisure | Discretionary |
| Pets | Leisure | Discretionary |
| Photography | Leisure | Discretionary |
| Pickleball | Leisure | Discretionary |
| Playing Music | Leisure | Essential |
| Rafting | Leisure | Discretionary |
| Sailboat | Leisure | Discretionary |
| Sailing | Leisure | Discretionary |
| Sporting Goods | Leisure | Discretionary |
| Swimming | Leisure | Essential |
| Video Rentals | Leisure | Essential |
| Legal | Misc | Essential |
| Misc | Misc | Essential |
| Tax preparation fee | Misc | Essential |
| Unknown | Misc | Essential |
| Fitness | Personal Care | Essential |
| Hair Care | Personal Care | Essential |
| Laundry | Personal Care | Essential |
| Personal Care | Personal Care | Essential |
| Reimbursed job | Reimbursed job | Essential |
| Federal Income Tax | Taxes | Essential |
| Property | Taxes | Essential |
| Taxes | Taxes | Essential |
| Amex <> BECU Checking | Transfer | Essential |
| BECU <> Cash | Transfer | Essential |
| BECU <> Intercam | Transfer | Essential |
| BECU Savings <> BECU Checking | Transfer | Essential |
| BECU Visa <> BECU Checking | Transfer | Essential |
| BOA Alaska Visa <> BECU Checking | Transfer | Essential |
| Capitol One <> BECU Checking | Transfer | Essential |
| Capitol One <> Intercam | Transfer | Essential |
| Chase Amazon <> BECU Checking | Transfer | Essential |
| HSA | Transfer | Essential |
| Intercam <> Cash | Transfer | Essential |
| Scotia Bank | Transfer | Essential |
| Vanguard <> BECU | Transfer | Essential |
| Vanguard <> Capitol One | Transfer | Essential |
| Activities - Travel | Travel | Discretionary |
| Airfare | Travel | Discretionary |
| Campground | Travel | Discretionary |
| Car | Travel | Discretionary |
| Dining Out - Travel | Travel | Discretionary |
| Lodging | Travel | Discretionary |
| Taxi | Travel | Discretionary |
| Travel | Travel | Discretionary |
| Electricity | Utilities | Essential |
| Internet | Utilities | Essential |
| Utilities | Essential | |
| Office, Apps, Subscriptions | Utilities | Essential |
| Propane | Utilities | Essential |
| Telephone | Utilities | Essential |
| Water | Utilities | Essential |