Free template · Excel

Group budget and latest estimate template

What it is

This free Excel template builds a group budget from up to eight companies, each budgeting in its own currency. It translates every budget into the group currency at a budget rate, takes the closed months as actuals, and shows the latest estimate for the year with the variance to budget. An example group is filled in.

What’s inside

• Up to eight companies and twelve months, budgeted in each company's own currency.
• One budget rate for each currency, with the group budget worked out by formula and rounded to whole units.
• Actual months entered in the group currency, with later months ignored.
• A latest estimate that uses actual for closed months and budget for the rest.
• Variance for the year to date and for the full-year estimate, for the group and for each company.

How to use it

1. Set up the group. Enter the group currency, the first month of the financial year and the last closed month. List your companies and their currencies, then enter one budget rate for each currency.
2. Enter each company’s budget by month in its own currency: revenue and operating profit.
3. Check the group budget. It is worked out by formula from the local budget and the budget rate, rounded to whole units.
4. Enter the actuals for each closed month, in the group currency, from your consolidation.
5. Read the Summary sheet. It shows the year-to-date variance and the latest estimate against budget, for the group and for each company.

Who is it for?

Group finance teams and FP&A analysts who set a budget in several currencies and want one view of the year. It works on any accounting system, because you enter the figures. Zoho Books budgets are made inside each organization, and Zoho’s help page does not describe a group view across organizations, so a group budget needs a place of its own. When the workbook grows heavy, Finconsol keeps the group budget and the latest estimate beside the actuals. See also presentation currencyPresentation currencyThe currency in which the group's financial statements are presented.Open the glossary entry and average rateAverage rateThe exchange rate for a period, usually the month, used to translate a foreign subsidiary's income and expenses into the group's presentation currency.Open the glossary entry.

Questions

Which rate should the budget use?

One rate for each currency, set once for the year. A variance then shows performance, not exchange rates. The actuals are translated at actual rates, so the variance still contains an exchange rate effect; keep the budget rate fixed so you can see it.

Why only revenue and operating profit?

They keep the sign of the variance simple: above budget is favourable for both. Add cost lines as negative numbers inside operating profit if you need them.

Does it eliminate intercompany sales?

No. Group totals add the companies before eliminations. Budget intercompany sales in one place only, or remove them in your consolidation.

Do I need Excel?

The file uses standard formulas and opens in Excel, LibreOffice and Google Sheets.