How to Build a Monthly Budget Spreadsheet with Formulas

To create a monthly budget spreadsheet with formulas, answer these key questions: how much money came in, how much went out, what remains, and where you overspent. You can accomplish all this using a single spreadsheet without any paid apps. Follow these steps to build the spreadsheet:

1. Set up the layout: You will have one tab with four sections - income, expense plan, transaction log, and summary block.

2. Enter total income: Use the SUM function to add up all income sources. For example, with columns A:B for income and B10 reserved for the total, enter "=SUM(B2:B3)" in B10. This formula will automatically calculate the total income based on the sample data provided.

3. Create an expense plan: In columns D:G, list categories (D), planned expenses (E), actual expenses (F), and variance (G). Use SUM to calculate total planned expenses (in E10) and actual expenses (in F10). The sample data shows planned totals of $2,605 and actual totals of $2,679.55.

4. Calculate remaining cash: Subtract actual expenses (F10) from total income (B10) to get the remaining cash. In a summary cell, enter "=B10-F10". For the sample data, this would result in $970.45.

5. Determine savings rate: Divide the savings transfer amount by total income to get your savings rate. If the income is zero, the formula should return a blank cell. Use the IF function to handle this scenario: "=IF(B10=0, ,F8/B10)". In the sample, the savings rate is 13.70%.

6. Identify planned vs actual spending: In column G, subtract actual expenses (F) from planned expenses (E) to see whether you spent more or less than planned. A positive number means you spent less, zero means you were on plan, and a negative number means you overspent. For the sample data, groceries and dining out were overspent, while transport and utilities were under budget.

7. (Optional) Use SUMIF for a separate transaction log: If you prefer to log transactions in a separate section (columns H:I), you can still calculate actual expenses using the SUMIF function. For example, in F2, enter "=SUMIF($H$2:$H$200,D2,$I$2:$I$200)" and copy this formula down through F8. This method should not be mixed with the original SUM method within the same month without a clear rule.

8. Calculate share of income by category: To see what percentage of your income each category represents, create a column (J) next to the plan and enter the formula "=IF($B$10=0, ,F2/$B$10)" in J2. Copy this formula down to cover all categories. Format this column as a percentage. In the sample data, rent represents 32.88% of your income, while dining out represents 4.60%.

By following these steps and using the provided formulas, you can easily build a monthly budget spreadsheet that answers the four key questions mentioned earlier. Remember, this tutorial uses sample data for demonstration purposes only, so replace it with your own numbers when building your own budget.