YMP Rulez

	A	B	C
1	Last update:	7/30/2020
2	By:	Patrick
3			TL:DR at bottom
4			This is a spreadsheet I created for myself to keep track of my spending and measure that against what I expected to spend, by category. It was initially created with just me in mind so only offers limited flexibilitybut could be used as a jumping off point for anyone who wants to have something similar. Here's how I use it.
5
6		Step 1: Set up projections	To do this I projected one item at a time on the "Projection" sheet. i.e I would add all of the "Income" items, then all of the "Mortgage" items, and so on. I get paid on and pay my mortgage on predictable bi-weekly schedules so it was easy to enter in the exact date for each. The easiest way to do this is to input the first two or three rows for the Date, Type, and Amount columns, then higlight them and use the "Fill handle" (the little box at the bottom right of any selection in the sheet) to drag down. The sheet will recognize the pattern in the date and automatically fill in the dates on whatever interval it recognizes. You can do this for all of your predictable expenses and income. For items that are only so predictable as to the month that they'll probably fall in, I just make it land on the 1st or 15th jsut to make sure the formulas capture them in the right month. You can sort by date afterwards if you like.
7
8		Step 2: Paste in Transaction Data	This is a part that may need some tweaking for different people depending on how you get your data from the bank/credit card company. I can download all of my transactiions in .csv format from my bank and they come out with the headers like I have on the "Chequing" and "Credit" sheets. Once pasted in to columns A-D of either sheet, columns F-H will reformat them into the correct arrangement for the transactions sheet.
9
10		Step 3: Paste relevant transactions to "Transactions" sheet.	These next two steps are the tedious part. From here I copy and paste the relavant line items from the "Chequing" and "Credit" sheets into the "Transactions" sheet. If you're a keener you can copy and paste in all transactions from all sources and do a full reconcilliation, but I don't. What I mean by that, for example, is that I will ignore transfers between my chequing account to my credit card since, to me, that transaction isn't representative of the actual expense, the multiple items on the "Credit" sheet that lead to it are.
11
12		Step 4: Label transactions	Once everything is pasted in, I will use the drop-down menus in the "Type" column of the "Transactions" sheet to label each line to an appropriate category. You can add and remove your own categories in cells AH4:AH23 of the "Cash Flow" sheet.
13
14		Step 5: Update cell C1	The date in cell C1 of the "Transactions" sheet affects the calculations in the Deviations section of the Cash Flow tab, allowing it to ignore months that haven't passed yet.
15
16		Step 6: See how you're doing	The "P vs A" sheet shows your projected expenses and then compares them to what you actually spent in a few separate tables. The bottom one shows you in dollars where you are ahead of or behind budget. To the right of the tables there is an "Upcoming Payments" table in cell AJ2:AL14 that shows you the next 12 days worth of expected expenses or income as per your projection tab for your cash planning needs.
17
18		TL;DR	Just use Mint or something.
19		TL;DR	I'm just a nerd who likes making spreadsheets that are objectively inferior to products that already exist
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100