Planning, including budgeting and forecasting, is an activity performed by companies every year. Whether it's top-down, bottom-up, or some other method, the ability to create plans is key to measuring how your company is doing. This article examines planning and budgeting using Solver corporate performance management tools and Microsoft Dynamics 365 Business Central.
Planning & Budgeting
Business Central
Business Central has easy-to-use functionality for creating a general ledger budget in Business Central. In short, navigate to the G/L Budgets screen and select “New” to create a budget. Then, you can add which dimension information to use for your budget.
Figure 1 G/L Budgets Screen in Business Central
Once you create a budget, you have options for getting data into it. First, select “Edit Budget.” Then, you should see a screen like the one shown in Figure 2.
Figure 2 Edit Budget Screen in Business Central
Here, you can enter data for each account/period/dimension combination. You also can import data from Excel.
Figure 3 Import from Excel
In addition to the Import from Excel option, there is the Export to Excel option. Learn more about exporting data on budget pages to Excel for further editing or processing in our FORsights™ article, “How to Create a General Ledger Budget in Business Central.”
So far, you have seen how easy it can be to create and edit budgets in Business Central. The challenging part is how you determine those budget numbers. Do you base them on actuals plus or minus an amount? Do you start with zero and enter an amount? Do you spread data? There are many options.
When you use the Excel spreadsheet method, you can use other sheets to determine the amounts to budget. You can download actuals from Business Central to one sheet and use those to determine the budget amounts. You also might use multiple sheets for different purposes, such as payroll and capital expenditures. Once you download and copy data into Excel, you can build a budget model that can be used to create budget data, which can be uploaded to Business Central.
If you’d like to streamline spreadsheet activity, this is where the next topic comes into play.
Solver
The Solver corporate performance management solution is a Microsoft Azure-based tool that includes reporting and planning, as well as connectors for multiple business management systems, like Business Central.
The next image illustrates how Solver is constructed. Notice that data can be pulled from various sources, including Business Central and payroll, and stored in the data warehouse. Using stored data, you can create reports and planning templates.
Figure 4 Solver Architecture
You can create many different types of plans using Solver. Some examples are:
- Payroll
- Capital Expenditures
- Revenue
- Production
- Operating Expenses
- Cash Flow
Figure 5 Examples of Solver Planning Templates
Planning templates are created using Excel with a Solver add-in. The add-in enables you to pull data from the data warehouse into the Excel template, like a mail merge document. Excel is used to structure the template, create formulas, and format the data.
Here’s the process for creating planning templates:
- Create a template.
- Assign it to users.
- Have users enter data online.
- Review and finalize the numbers.
To manage the planning process, you can take advantage of multiple pieces of functionality:
- Process Manager – Create lists of steps with hyperlinks to templates.
- Workflow – Create workflows with budget reviewers and approvers and assign templates to users for completion within optional date ranges.
- Input Flow – Automate budget updates where one template can update another template.
In addition, you can simply provide users an input form where data can be entered. The advantage of this cloud-based functionality is that it can be accessible anywhere you have an internet connection and can help users manage their numbers with management oversight.
Here’s an example of a simple expense template:
Figure 6 Other Expenses Planning Template
Several elements of this template are worth noting, including:
- Users can enter data directly into cells or paste data into them.
- Users can view actuals or other data as comparisons.
- Users can enter line item details and comments.
- Users can use spreading to have amounts spread across periods evenly or using rules.
One thing that’s important to note is security. Security permeates throughout the system. This feature can help a template creator, aka the person building the budget report, build a single template that can serve several companies, departments, locations, etc. For example, a user may be assigned to one or more companies and accounts. When the user opens the template, only companies and accounts assigned to the user will be visible.
Creating a Template
You can create a “New Blank” template with Excel using Solver. Then, you can perform these actions:
- Drag and drop elements from the data warehouse.
- Enter formulas.
- Apply formatting.
- Add filters to rows and columns.
- Publish the template so other team members can use it.
Figure 7 Planning Template Design
The image above is a behind-the-scenes view of the template in Figure 6. Note that while multiple accounts are visible in Figure 6, the report design only shows one row, Row 13, for entering data.
The secret behind the design is filters and grouping. Filters determine which accounts can appear in the template. In this case, the template designer selected the accounts in a range. Ranges also can be defined by using account groupings, like categories.
Grouping enables a single row to turn into multiple rows. It works like a pivot table. If you drag one field and an amount to a pivot table, the data is grouped by that one field. As you drag additional fields, the table expands to display more rows. The same thing happens in Solver templates.
In the example above, the rows are grouped by “Account.” For the range of accounts, Solver will display one row per unique account. This grouping enables a single row to expand automatically to many rows.
Figure 8 Grouping for a Row
In the example, you can see a section for accounts with no history. Sometimes, you may not want to display rows in the main section for accounts that haven’t been used. This design shortens the number of rows. However, you may have new accounts to budget, and that’s where the section can be used.
For line item details, comparisons, and comments, that functionality is available automatically. You don’t have to add it to the template. In addition, while the example only shows cells for January to December, you may have columns of other data that can be viewed by a user when entering numbers.
Once the template is designed, save it, publish it, and share it. If you use Process Manager or Workflow, you can assign it to users. As a Workflow administrator, you can monitor user progress.
Figure 9 Budget Workflow Status
Along with the other types of planning templates, you can create a process for each budget season where users are assigned templates to complete. Rather than requiring users to enter data in Excel files, users can enter data using templates. That data is saved in the data warehouse and can be reported against actuals immediately using Solver reports or within Solver planning templates.
Conclusion
Business Central has G/L Budgets for creating and maintaining budget data for G/L accounts and periods. That data can be entered or imported from Excel. Solver enables organizations to create budgets beyond G/L and use tools such as Process Manager, Workflow, and Input Flow to help automate the process and help the planning process stay on schedule. Further, users can enter data using templates at any time from anywhere.
In summary, if you and your team dread budgeting, tools like Solver can help. FORVIS and Solver Global, the publisher of Solver, have been partners for many years. FORVIS, an international accounting and consulting firm, is an award-winning partner of Solver Global, which itself is highly rated by G2, a leading software marketplace.
Learn more about Business Technology Solutions at FORVIS.