Course / Challenges

Challenge: travel budget vs actual

Convert foreign spending with a locked exchange rate, compare it with the plan and flag the overspends. Intermediate · 9 min · ROUND, $ references, Variance, % of budget, IF, COUNTIF

You planned a week in Lisbon with a budget in US dollars, but every receipt was in euros. Now you're home and want to know how you did.

Convert the spending with the exchange rate in J1, compare it with the budget, and flag the categories that ran over. Keep the rate in its own cell: if your card statement shows a slightly different rate, you only have to change one number.

Steps

1. In D2:D15, convert each amount spent into dollars using the rate in J1, rounded to the cent.

2. In E2:E15, show how much of each budget is left over: positive means you came in under budget, negative means you overspent.

3. In F2:F15, show what percentage of each budget you used, formatted as a percentage.

4. In G2:G15, flag each category Over if you spent more than the budget, or OK if not.

5. Complete the Total row: B16:E16 should add up each column, and F16 should show the percentage of the whole trip budget you used.

6. In J3, count how many categories went over budget.

Remember

• Keep rates and other inputs in their own cell and lock them with $.
• ROUND(…, 2) gives clean cent values for money conversions.
• Variance is budget minus actual; % used is actual ÷ budget.
• For a total percentage, divide the totals rather than adding up the percentages.

Previous: Challenge: stock room reorder list · Next: Challenge: the league table