How to Make a Budget in Excel: Our Simple Step-by-Step Guide - Free Printable

Problem Description:

The image shows an Excel spreadsheet that tracks expenses across different categories. The columns include:
- Category: The type of expense (e.g., Mortgage, Groceries, Utilities).
- Due Date: The date when the expense is due.
- Planned: The planned amount for each expense.
- Actual: The actual amount spent for each expense.
- Difference: The difference between the planned and actual amounts.
- Percentage: The percentage difference between the planned and actual amounts.

The task is to solve any issues in the spreadsheet, particularly focusing on the Difference and Percentage columns, and ensure the calculations are correct.

---

Solution Approach:

#### Step 1: Analyze the Difference Column
The Difference column should calculate the difference between the Planned and Actual amounts for each category. The formula for this is:
\[
\text{Difference} = \text{Actual} - \text{Planned}
\]

Let's verify the values in the Difference column:
- Mortgage: Actual = $1,500, Planned = $1,500 → Difference = $1,500 - $1,500 = $0 (Correct)
- Groceries: Actual = $600, Planned = $450 → Difference = $600 - $450 = $150 (Incorrect, should be $150, not -$150)
- Utilities: Actual = $290, Planned = $300 → Difference = $290 - $300 = -$10 (Correct)
- Loans: Actual = $700, Planned = $700 → Difference = $700 - $700 = $0 (Correct)
- Childcare: Actual = $1,000, Planned = $1,000 → Difference = $1,000 - $1,000 = $0 (Correct)
- Car Insurance: Actual = $250, Planned = $250 → Difference = $250 - $250 = $0 (Correct)
- Health Insurance: Actual = $500, Planned = $500 → Difference = $500 - $500 = $0 (Correct)
- Gas: Actual = $350, Planned = $250 → Difference = $350 - $250 = $100 (Incorrect, should be $100, not -$100)
- Car Payment: Actual = $500, Planned = $500 → Difference = $500 - $500 = $0 (Correct)
- Phone/Internet: Actual = $250, Planned = $250 → Difference = $250 - $250 = $0 (Correct)

Issue Identified: The Difference values for Groceries and Gas are incorrect. They should be positive ($150 and $100, respectively).

#### Step 2: Correct the Difference Column
To fix the Difference column, use the formula:
\[
\text{Difference} = \text{Actual} - \text{Planned}
\]
Apply this formula to all rows:
- For Groceries: Difference = $600 - $450 = $150
- For Gas: Difference = $350 - $250 = $100

After correction:
- Groceries: Difference = $150
- Gas: Difference = $100

#### Step 3: Analyze the Percentage Column
The Percentage column should calculate the percentage difference between the Planned and Actual amounts. The formula for this is:
\[
\text{Percentage} = \left( \frac{\text{Difference}}{\text{Planned}} \right) \times 100
\]

Let's verify the values in the Percentage column:
- Mortgage: Difference = $0, Planned = $1,500 → Percentage = \(\frac{0}{1,500} \times 100 = 0\%\) (Correct)
- Groceries: Difference = $150, Planned = $450 → Percentage = \(\frac{150}{450} \times 100 = 33.33\%\) (Incorrect, should be 33.33%, not 10%)
- Utilities: Difference = -$10, Planned = $300 → Percentage = \(\frac{-10}{300} \times 100 = -3.33\%\) (Incorrect, should be -3.33%, not 10%)
- Loans: Difference = $0, Planned = $700 → Percentage = \(\frac{0}{700} \times 100 = 0\%\) (Correct)
- Childcare: Difference = $0, Planned = $1,000 → Percentage = \(\frac{0}{1,000} \times 100 = 0\%\) (Correct)
- Car Insurance: Difference = $0, Planned = $250 → Percentage = \(\frac{0}{250} \times 100 = 0\%\) (Correct)
- Health Insurance: Difference = $0, Planned = $500 → Percentage = \(\frac{0}{500} \times 100 = 0\%\) (Correct)
- Gas: Difference = $100, Planned = $250 → Percentage = \(\frac{100}{250} \times 100 = 40\%\) (Incorrect, should be 40%, not 6%)
- Car Payment: Difference = $0, Planned = $500 → Percentage = \(\frac{0}{500} \times 100 = 0\%\) (Correct)
- Phone/Internet: Difference = $0, Planned = $250 → Percentage = \(\frac{0}{250} \times 100 = 0\%\) (Correct)

Issue Identified: The Percentage values for Groceries, Utilities, and Gas are incorrect.

#### Step 4: Correct the Percentage Column
To fix the Percentage column, use the formula:
\[
\text{Percentage} = \left( \frac{\text{Difference}}{\text{Planned}} \right) \times 100
\]
Apply this formula to all rows:
- For Groceries: Percentage = \(\frac{150}{450} \times 100 = 33.33\%\)
- For Utilities: Percentage = \(\frac{-10}{300} \times 100 = -3.33\%\)
- For Gas: Percentage = \(\frac{100}{250} \times 100 = 40\%\)

After correction:
- Groceries: Percentage = 33.33%
- Utilities: Percentage = -3.33%
- Gas: Percentage = 40%

#### Step 5: Final Adjustments
- Ensure all formulas are applied consistently.
- Format the Percentage column to display two decimal places for better readability.

---

Final Answer:

After correcting the spreadsheet:
- Difference column:
- Groceries: $150
- Gas: $100
- Percentage column:
- Groceries: 33.33%
- Utilities: -3.33%
- Gas: 40%

The corrected spreadsheet will now accurately reflect the differences and percentages for all categories.

\boxed{\text{See corrections above}}