Now for part 3! I hope that part 2 will make a little more sense in this part. We left off making the tithe tracker for those of you who tithe part of your income. Next we'll work on setting up each month and making totals for the end of the year.
21. Starting in cell C2 you'll want to type in January. Then click on the bottom right corner of the cell and drag until cells are filled with February through December. To do this you want to make sure your cursor looks like a small plus sign (+).
22. Then you'll want to scroll to the bottom of the spreadsheet and you'll want to copy over from column B all the way to column N for each of the totals. This is what your spreadsheet should look like.
23. Now scroll up to the top of the spreadsheet and we'll work in column O. You'll name cell O2 Expected Yearly. And then in cell O3 type in the formula =B4*12 and copy this down to the cell right above Total Income Before Tithe. This will tell you what your budgeted yearly amounts are in each category.
24. Next you can copy the totals formulas in rows 24 - 41 over to column O as well.
25. Next you'll want to name cell P2 Actual Yearly, this will show the total spent in each category over the whole year. The formula in cell P4 will be =SUM(C4:N4) or the sum of January through December in that category. Then you can copy down to the cell above Total Income Before Tithe. And then you can copy over the Totals at the bottom over to column P as well.
26. The spreadsheet is almost complete but you'll want to make a couple changes in columns O & P, scroll to the bottom to view the lines Made More and Spent Less. You'll want to change the formulas in these columns. O31 should have the formula =O24-$O$24 and then you can copy over to P31.
27. Next change the formula in O33 to =$O$27-O27 and then copy over to P33.
And that's the whole spreadsheet! I hope the pictures helped further explain the steps. I don't think it's too hard to make once you get started. Each month, Josh and I used our tracked expenses on Mint to fill in the income and expenses for that month. Then we make sure that we didn't spend more than we made, that we use any money we made more than we thought to make a contribution to our goals, and we check to see if we spend less than we thought. The spreadsheet helps us look at what Mint tracks for us. I'll do another blog soon to show our monthly process. Please comment or send me an email or direct message if you have any questions, I'm happy to help!
Click here to look at the spreadsheet created in this tutorial.
No comments:
Post a Comment