2 Excel templates:
1) Inventory planning
Inventories are a significant part of net working capital, and as such, they can greatly impact cash flow.
Therefore, we aim to optimize all processes related to procurement, production, and delivery to maintain an optimal level of inventory and ensure smooth cash flow.
In this simple model, we start with inventory drivers such as total material purchases, the time inventory is held in work-in-progress, and days on hand at the warehouse.
Based on the forecast of these variables, we obtain the inventory forecast.
Click here to get your Excel template
The model can be adjusted very easily to any company.
2) Operating expense budgeting
When budgeting costs, it’s important to understand their drivers, past trends, seasonality, cyclicality, pricing, and other parameters. Each cost has its own method for budgeting. Here are a few key methods:
- As a percentage of revenue, if we’re talking about variable costs that are directly influenced by sales, then this is generally the rule of thumb.
- As a percentage of salaries, this is typical for certain related personal expenses, taxes, and social contributions.
- According to the annual growth rate – this is characteristic of some fixed expenses, such as telephone costs in the central office.
In this model, you have many examples of budgeting. Hurry up, the budgeting season is starting.
Click here to get your Excel template
This model, with small adjustments, can be used for many industries.
Here are 2 Infographics for Today:
1) Net present value (NPV) explained simply
Get this infographic on a high-resolution PDF
2) Business valuation methodology
Get this infographic on a high-resolution PDF
Here’s today’s “How to” guide:
How to build a perfect report for management for SME.
In good reporting, we have three pillars: 1) data collection and processing, 2) report creation, and 3) drawing conclusions and recommendations based on the results.
- Data Collection and Processing
It’s important to have a solid foundation for reporting, meaning a clear and concise Chart of Accounts. Each account should be mapped to one or more dimensions to provide a basis for financial reporting.
At a minimum, cost accounts should be mapped to cost centers, revenue accounts should be mapped to revenue segments, and so on.
These settings, of course, should be done in the ERP system itself. Once monthly bookkeeping is complete and the month is closed, it’s necessary to extract data from the ERP, meaning having a good database is crucial.
In larger companies, reporting can be done directly from the ERP. However, in small and medium-sized companies, Excel is usually still the main tool.
A good database is key. Whether we work with an SQL database, use Power BI, Power Query, or simply export the trial balance with columns like cost centers depends on the company and the resources it has.
In any case, a good database is essential for the next step. Inputs for reporting, in addition to actual data, should include annual budget and forecast data. Therefore, it is important to do good mapping so that we can easily compare actuals with the plan.
- Report Creation
When we have the database and all relevant data (for example, a trial balance with columns like date, cost center, revenue segment, etc.), that data needs to be “repackaged” into a report.
A typical reporting package includes a functional P&L, Balance Sheet, and Cash Flow, as well as a dashboard that displays specially designed KPIs.
When we talk about reports, the most important thing is to have a functional report, meaning that the report accurately reflects business functions.
For example, if we are doing reporting for a company that provides professional services, the basic revenue segments in the report should allow us to view key services. Additionally, it is good to include the cost of service, for example, in this company, it would be the salary expense of employees who are directly engaged in the services.
This allows us to calculate the gross margin for each service line, which is the best indicator of the efficiency of a particular segment. Furthermore, regarding the report format, I prefer to compare actuals with the previous year, then with the budget, and with the forecast.
Reports are a resource for calculating KPIs. I also prefer not to overdo the number of KPIs, which should be packaged into an actionable dashboard.
- Drawing Conclusions and Recommendations Based on Results
The report itself is not the goal. It is necessary to analyze variances, trends, and differences, and answer the question of whether we are moving in the desired direction. In any case, it is important to examine the differences between the plan and the trends compared to historical periods, and based on the conclusions, create an action plan for management to ensure the company is moving towards achieving its goals.