Departmental Budgeting
This spreadsheet provides a template for planning the expenses and forecasting the revenues of a company over a 12 months period. A company may consist of several divisions or departments, thus this spreadsheet supports the planning and forecasting of up to 5 different departments.With input values provided by the different departments, a budget will be generated and will be used to compare with the actual revenues and expenses subsequently. The differences between the estimated values in the budget and the actual values can be calculated to help identify the discrepancies quickly and easily.
PDF Specifications - Purchase - License
Price USD$69
Priority Technical Support, Free upgrades for a year
ConnectCode provides excellent priority email support to our registered customers. Customers are also entitled to a one year free upgrade of the software.System Requirements
- Microsoft® Windows 7, Windows 8 or Windows 10
- Windows Server 2003, 2008, 2012 or 2016
- 512 MB RAM
- 5 MB of Hard Disk space
- Excel 2007, 2010, 2013 or 2016
Spreadsheet Details
Overview
A budget is simply a list of planned expenses and a forecast of the revenues of the business. It provides a basic model of how a business will perform financially in a specific period of time. If certain business strategies are carried out, the budget allows the business stakeholders to see the impact on the business financially.After a budget is created, tracking of the actual revenues and expenses are usually carried out and the actual values are compared to the forecasted ones. This allows the company to measure the differences or variances between the actual and the estimated values.
Discrepancies between the forecasted and actual values can be used to identify problems and unexpected events in the business. Resolving these problems will allow the company to operate more efficiently in the future. On the other hand, the discrepancies can be simply due to mistakes in the estimation of the values in the budget. By identifying and reducing these mistakes, it allows the business stakeholders to forecast and track their business results more accurately.
Spreadsheet Overview
The diagram below illustrates the process of using this spreadsheet. As shown in the two "square boxes" in the above diagram, the different departments simply provide the values of the forecasted Sales, Costs and Expenses. A departmental budget will then be automatically generated by this spreadsheet and can be used by the department for subsequently tracking of the budget.The different departmental budgets will subsequently be consolidated into a master budget to provide an overall view of the company. This enables the different stakeholders of the company to understand the business via a common platform. Overhead expenses in cost centres can also be allocated to the different departments.
The spreadsheet assumes no accounting knowledge and guides you through the steps in successfully creating a departmental and consolidated budget.
Worksheets outline
Input Worksheets
The following table outlines the worksheets that require inputs. Different stakeholders are provided different worksheets. For example, each of the departments will only need to enter values in the Departmental Level worksheets.| Level | Worksheets | Notes |
| Company Level |
General Inputs Overhead Expenses Inputs Allocate Overhead Expenses |
A maximum of 50 expenses categories can be defined and a maximum of 5 departments are supported. The spreadsheet is unlocked and thus can be customized to support more expenses categories and departments. |
| Departmental Level |
Dept Sales Inputs X Dept Expenses Inputs X |
A maximum of 5 departments is supported currently. The different departments are identified by the department numbers X.
The estimated value for the budget and the actual values can be entered in the same worksheet. |
Output Worksheets
The following table outlines the worksheets that are automatically generated.| Level | Worksheets | Notes |
| Departmental Level |
Dept Budget Dept Actual Dept Variance |
Both a Monthly Budget and a Cumulative Budget will be generated.
The Dept Actual worksheet is similar to the Dept Budget worksheet except that it is used for tracking the actual business sales and expenses. The Dept Variance worksheet is used for identifying the discrepancies between the forecasted and the actual values. |
| Company Level |
Consolidated Budget Consolidated Actual Consolidated Variance Summary Budget Summary Actual Summary Variance |
The Consolidated Budget combines all the values of the different departmental budgets.
The Consolidated Actual and Variance worksheets are use for tracking the differences between the forecasted and the actual values. The summary worksheets provide a one page overview of the budget's forecasted and actual values. |
Input Worksheets
It is important to note that all worksheets with names ending with "Inputs", for example, "Overhead Expenses Inputs" requires inputs to be entered. The values in other worksheets are automatically generated. Fields marked with "*" in the input worksheets require input values, other fields are generated or used for interim calculations.It is also important to note that all worksheets with names beginning with "Dept" are departmental level worksheets.
General Inputs
General Inputs
- Company Name* - The name of the company.
- Date* - The start date for the financial plan.
Expenses Categories
This spreadsheet allows you to define up to a maximum of 50 expense categories. The expense categories will be automatically propagated to the Departmental, Consolidated and Summary Budgets/Actual/Variances worksheets.The most important expense categories should be defined first as the first 5 expense categories will be shown in details in the consolidated budget. Other expenses beyond the first 5 will be shown as a total value in the consolidated budget.
The Expenses No. field can be modified to be the accounting codes of the company if required. This will allow for better tracking of the expenses. This number does not affect other worksheets.
Departments/Divisions
The Departmental Budgeting spreadsheet supports up to 5 departments. The names of the different departments can be defined in this worksheet. The names will be used to identify worksheets belonging to the same department subsequently.Overhead Expenses Inputs
Overhead expenses refer to expenses that do not directly relate to the generation of profits in each of the departments but are required for the continual functioning of the business. This worksheet allows the estimated overhead expenses to be entered in the first section named "Plan".The next section "Actual" allows the actual overhead expenses to be entered and subsequently tracked. The diagram below shows the "Plan" section of the worksheet.
Allocate Overhead Expenses
The overhead expenses can be allocated to different departments. This worksheet allows you to specify the percentage point that the total overhead expenses to be allocated to the different departments. The only field that requires input is the Budget Allocation field which is marked with "*". Other fields are calculated automatically to assist in the allocation of the expenses.
The "Plan" section of the worksheet shows the allocation of the budget expenses while the "Actual" section shows the allocation of the actual expenses. The diagram above shows the "Plan" section of the worksheet.
Departmental Worksheets
The departmental worksheets allow the different departments to provide sales forecast and plan for expenses in the next 12 months. The actual sales and expenses can be entered subsequently to be compared to the estimated values.Dept Sales Inputs
A business earns profits by selling goods, products or services. This worksheet is used for forecasting sales for the next 12 months. Simply enter the values for the following fields and it will be incorporated into the budget worksheets.- Price* - The price of the goods, products or services.
- Volume* - The number of units of the goods or products sold.
- Cost* - The direct cost of the goods, products or services. This is the usually cost for manufacturing the product.
This worksheet allows the forecasted Price, Volume and Cost to be entered in the first section "Plan". The next section "Actual" allows the actual Price, Volume and Cost to be entered. The diagram above shows the "Plan" section of the worksheet.
Dept Expenses Inputs
Other than the direct costs associated with producing goods and services, other costs like salary, marketing and administrative expenses will need to be calculated to determine the profitability of the department. This worksheet allows all the selling, administrative and general costs of running a business to be entered. The expenses will then be incorporated into the budget worksheets.
This worksheet allows the forecasted Expenses to be entered in the first section "Plan". The next section "Actual" allows the actual Expenses to be entered. The diagram above shows the "Plan" section of the worksheet.
Output Worksheets
Departmental Worksheets
The Departmental Budgeting spreadsheet automatically generates the following departmental worksheets:Dept Budget Worksheet
- Departmental Monthly Budget
- Departmental Cumulative Budget
- Departmental Monthly Actual
- Departmental Cumulative Actual
- Departmental Monthly Variance
- Departmental Cumulative Variance
Dept Budget
This worksheet shows the Monthly Budget of a department for the next 12 months in the first section. Basically, it shows Operating Income as Sales minus Cost of Sales and Expenses. The Overhead Expenses are deducted from the Operating Income to derive the final income.
The Cumulative Budget is generated in the second section of this worksheet. The Cumulative Budget shows the cumulative total of the monthly figures. For example, the Cumulative Sales for February is the total of the Monthly Sales in January and Monthly Sales in February.
Dept Actual
This worksheet tracks the actual profitability of department over the next 12 months. The diagram below shows the first section of the worksheet that illustrates the Monthly Actual of the Department. The values are calculated from the "Actual" section from the Dept Sales Inputs and Dept Expenses Inputs worksheets.
The Cumulative Actual is shown in the second section of this worksheet. The cumulative total is calculated by summing the monthly values in the first section of this worksheet.
Dept Variance
This worksheet calculates and illustrates the Variance of the profitability of department over the next 12 months. The Variance $ is Actual minus Budget. The Variance % is calculated as the difference (Actual minus Budget) divided by Budget. The first section shows the Monthly Variance; the second section shows the Cumulative Variance.
Consolidated Worksheets
The Consolidated Worksheets combine the values from the different departments to provide a consolidated Budget, Actual and Variance for the company. The following consolidated worksheets are generated automatically to provide a detailed view of the financial performance of the different departments in the company:- Consolidated Budget
- Consolidated Actual
- Consolidated Variance
Consolidated Budget
The following diagram shows the Consolidated Budget. The Sales, Cost of sales and Gross Profit are further breakdown by the different departments. This allows the different departments to be compared easily.
The following diagram shows the breakdown of Expenses by the different departments. The first 5 Expenses are provided in a detailed breakdown by departments. The rest of the expenses are shown as a total. This is the reason why we emphasized on defining the most important Expenses in the General Inputs worksheet first.
The Operating Income by departments is calculated as shown below.
Consolidated Actual
The Consolidated Actual is very similar in layout to the Consolidated Budget. It is a worksheet that combines the actual revenues and expenses of the different departments. The values are consolidated from the following worksheets.Dept Sales Inputs 1 ("Actual" section)
Dept Expenses Inputs 1 ("Actual" section)
.
.
.
Dept Sales Inputs 5 ("Actual" section)
Dept Expenses Inputs 5 ("Actual" section)
Consolidated Variance
The Consolidated Variance allows the discrepancies between the Consolidated Budget and the Consolidated Actual to be compared easily in one single worksheet. A breakdown by the different departments is also provided as shown in the diagram below.
The estimated and actual Sales for the different departments are shown above. Variance $ is calculated as the difference between Actual and Budget and Variance % is calculated as the difference between Actual and Budget divided by Budget.
Summary Worksheets
The Summary Worksheets are similar to the Consolidated Worksheets in the previous sections. They simplify the figures of the different departments and show the figures as a total instead of a detailed breakdown by departments. The financial performance of the company can be viewed easily from the Summary Worksheets. The following worksheets are generated.- Summary Budget
- Summary Actual
- Summary Variance
Back to Excel Templates and Add-In