Articles

powerbiandsolver

If you have ever tried to build and maintain financial reports inside a visualization tool, the answer should be simple. Power BI works well for dashboards, but modern cloud-based corporate performance management (CPM) solutions are the best option for your financial reports.

BI vs. CPM

Let’s elaborate. Power BI belongs to a category typically referred to as Business Intelligence (BI) tools. While purpose-built, financial reporting and consolidation tools belong to a category referred to as Corporate Performance Management (CPM) Software.

Business Intelligence:

In the business intelligence category, you also find other well-known solutions such as Tableau and Domo. For the most part, BI tools are purpose-built for dashboards with rich visualizations. Also, BI tools are increasingly infused with artificial intelligence (AI) capabilities.

Corporate Performance Management Software:

On the other hand, CPM solutions are specifically designed for accounting teams to consolidate financial data. CPMs often produce professionally formatted financial reports such as: 

  • Profit & Loss
  • Balance Sheet
  • Cash flow statements

Most CPM reporting tools offer budgeting modules. And in recent years, the ability to do sophisticated planning such as sales forecasting and modeling.

If the difference between BI and CPM is so clear, why is ‚ÄúFinancial Reporting in Power BI or in a Corporate Performance Management Solution‚ÄĚ even a topic worth covering in a blog?¬†

Well, with the rapid rise in popularity of Power BI for financial reporting and its key competitors, an army of ‚Äúhungry‚ÄĚ consultants have emerged.¬† And, given a strong and flexible tool like Power BI, a sales person with the right technical skills can make almost anything, including formatted financial reports, look good.¬† Many companies have fallen into this trap lately of using Power BI for financial reporting. And, when you ask a skilled, revenue motivated consultant: Can you do that? Chances are that you will get a ‚ÄúYes!‚Ä̬† And after a few weeks‚Ķor a few months, and a sizable consulting bill, you may actually have good looking Power BI financial reports.¬† But, as excited as your executives may be to have a single cloud-based portal for both dashboards and financial reports, for most financial executives this has a tendency to turn into a small nightmare.¬† Why?

Because neither Power BI nor its BI competitors were designed to manage a financial reporting process.

A lot of hard coding and hacking is needed behind the scenes in Power BI’s modeling language. Of course, if you are ready to hire a technical expert or keep your Power BI consultant permanently retained to handle model changes as your chart of accounts grows, your roll-ups change or you need to write a new report, you could survive for a while. Eventually, we can say with certainty, you will be back in Excel to do your financial reporting where your accounting team is comfortable with formulas and formatting.

Alternatively, you can deploy a CPM solution that is built to streamline the financial process. Also, you can deploy a CPM tool to deliver professionally formatted financial statements.

With a CPM solution, you can also move your budgeting process into the CPM tool and run it all in the cloud.  Now, it does not have to be an either or.

The Best Way: Use BI Tools and CPMs Reporting Tools Together

An increasing number of CPM vendors offer pre-built integrations to leading BI tools like Power BI.

Two such vendors are Solver and Prophix. As an example, Solver is similar to Power BI in that it is an Azure cloud-based platform, and it comes with a pre-built connector to Power BI.  Below is an example of a comparison between Power BI and Solver. It clearly shows that companies looking for both dashboards and financial reporting should use a BI tool for their dashboards and a CPM tool for their financial reporting. Neither tool replaces the other.

power bi and solver

As organizations gear up for what can be the “Roaring Twenties,‚ÄĚ having the right tools for the job might be one of the smartest strategic moves a management team can make. On the other hand, trying to fit a square peg in a round hole, could lead to frustration. The frustration is delayed reporting and wasted money. It is increasingly accepted that ‚Äúdata is the new gold.‚ÄĚ When Power BI is integrated with a best-in-class CPM solution and both are running in the cloud, organizations are likely a step closer to success and industry leadership in the years ahead.

sage-intacct

If you have surveyed an audience of mid-market accounting and finance professionals, you almost always find that 90-95% primarily rely on manual budget models in Excel. How is this possible when most enterprise resource planning (ERP) systems, including Sage Intacct, have both basic native ERP budget functionalities? In some cases, they also have specialized add-on modules. For example, the Microsoft Dynamics on-premise ERPs had the now ‚Äúretired‚ÄĚ Forecaster product and for Sage, it is the Sage Budgeting and Planning. Other ERP systems have built various Excel export and import mechanisms to make it easier for users to budget in Excel. Then, re-import the GL portion of the budget.

While most ERP vendor’s budget add-ons do the job better then entering budgets directly into the ERP system, there still seems to be a large group of companies that simply need a full-featured budgeting and forecasting solution to get the workflow, formula and layout flexibility they need. These solutions are typically referred to as Corporate Performance Management (CPM) tools. They also include a reporting and consolidation functionality.

How do I know if my planning process is NOT streamlined?

Typically, there are a number of key motivators when an organization begins to look for a CPM solution to improve its planning process:

  • Effort spent exporting and importing data from Sage Intacct to home grown Excel budgets
  • Current process is error-prone and a resource strain on the finance team
  • Lack of time and resources to quickly re-forecast (for example if a virus hits the economy!)
  • Lack of automation to create many budget versions
  • Current tool too complex for staff to learn so ended up back with a manual Excel model
  • Find that the ROI of automation with a CPM tool is higher than the cost of manual processes

Which benefits should I expect from modern CPM Planning tool?

Because Sage Intacct is a cloud-based ERP system, pretty much all customers looking for a full featured CPM solution like Solver, Adaptive Insight or Anaplan, will also expect a high degree of functionality, including:

  • Cloud-based platform
  • Pre-integration to Sage Intacct and Sage Intacct approval as a certified add-on vendor
  • Complete workflow functionality to manage submissions, reviews and approvals of the budget
  • Highly flexibly budget form design to completely replace the company‚Äôs manual Excel models
  • Strong native report writer for variance reporting and budget consolidations
  • User administration and security that allow for any person to participate in the budget regardless if they are users of Sage Intacct or not
  • Usability features like spreading, break-back, lien items with comments, and more.

I am planning to implement Sage Intacct. What is the best timing for budgeting tool?

Accounting teams always have a lot on their plate. Therefore, it never seems to be a great time to implement another software. However, the best time seems to be in the 2-3 months after the most recent budget cycle. Not only will issues and needs be fresh in mind, but it also allows for plenty of time to select, implement and test a new planning solution before the next budget cycle starts. Some organizations even do a ‚Äúdry run‚ÄĚ of their new budget solution during the year for one of their forecasts.

Carefully selecting a CPM vendor is important

Turbulent Mergers & Acquisition (M&A) market

For example, when a vendor gets acquired, it usually ends less than good for customers. The most typical reason that issues arise is that acquiring companies are much larger than the target company. Therefore, politics and other internal priorities at the parent company tend to drive employees away, disturb the focus of product development, increase prices and more.

Eventually, many acquired products die slowly, and customers end up switching products and vendors. Comshare, Adaytum, SRC Software and Clarity are a few vendors that suffered this during the M&A spree that took place in the CPM space about 15-20 years ago. Over the past 3 years, another wave of M&A has happened, including Workday acquiring Adaptive Insight, a PE firm acquiring Host Analytics from its old investor, Wolters Kluwer acquired Tagetik, and PE-owned Insight Software acquiring Jet Global, Atlas, BizNet and other players in the game.

Protecting the significant financial investment

There are more CPM solutions that are cloud-based. Customers can sign up for one or more years of subscription versus having to purchase the software upfront. However, even if a subscription is reasonable, the amount of time and effort from the internal staff (often with the help of costly consultants) has to be put in to get a CPM solution fully up and running. Also the reports, budget model and dashboards that management need are very significant. You can expect anywhere from twenty thousand dollars and to several hundred and thousands of dollars in direct and indirect costs for full blown implementations. In other words, picking the right vendor and the right product carries a much larger costs than simply a year’s worth of subscription.

Protecting your job

While a good vendor selection and successful implementation can be a significant boon to the careers of project management, it can be the opposite if it all does not work out well. Let alone the stress and long hours that often come from enterprise software implementations,  should it not end well,  can be a scar that follows you for a long time in your career.

Be Strategic

Whether there is too much pain from spreadsheet budgeting or a tactical hint for competitive advantages with faster and better decision-making that drives your search for a CPM solution, a final piece of advice is to be strategic about it. Don’t look at a CPM solution as a temporary band-aid, but view this technology for what it can be when properly chosen and implemented with care. One of your most important decision-making tools that can help drive growth and success for your organization in the 2020s.

Payroll Forecast

Forecasting is always an essential part of any business, and the workforce is typically the largest expense. 

Workforce planning includes salaries, commissions, benefits, taxes, retirement, and much more.  On average, workforce expenses comprises over 30% of gross sales, but it can be over 50% depending on the industry.  Workforce forecasting should always be the top priority for any organization.  Our guide will cover many alignments with strategies, access to data, and best practices on workforce planning and forecasting.

How to Develop a Strategic Plan for Workforce Forecasting

There are many questions to ask prior to starting any type of forecasting, and workforce-related items are often some of the most central questions.  Companies will not be able to forecast reductions, increases, or changes accurately for the workforce without a strategic plan. Data varies, but the estimation is that approximately 90% of all organizations fail to execute their strategies successfully.  

There are many reasons for the failure and it includes lack of communication, not linking strategy to planning, top down approach only, and failed implementation strategy.  Ensure that the organization has a clear strategy and communicate it clearly to the company that there is a well-defined execution path.  

Access to Data for Workforce Planning

The ability to access accurate and timely data for analysis is a necessity.  The data must be accessible in order to build workforce demand forecasting models. There can be a lot of data and below is an example of some of the data by employee by month that would be beneficial in creating a model:

  • Salary
  • Commissions
  • Bonuses
  • Promotions
  • Taxes
  • Benefits
  • New Hires
  • Title
  • Terminations
  • Overtime
  • Headcount by Position
  • Hours Worked
  • Benefit Eligibility

The first question to ask is whether you can access the data.  Determine where the data is coming from and create a process to integrate the data so there is a seamless process monthly or quarterly depending on how often forecasting happens.  

Spot check the data to ensure that the data is accurate as workforce accounts for over 30% of expenses, so a small variance can have a large impact.  For example, if a benefit comprised 1% of gross revenue of $100 Million and the assumption was off by 50%, then the variance would be approximately $500,000 for a single benefit.  This is a substantial variance for just one benefit, which can affect decision-making.   

Generally, workforce demand forecasting models are the most complex templates that organizations have.  The more accurate the data is, then the less complex the models need to be and many of the assumptions go away.  A model allows for enabling quick and accurate decision-making for managers and executives around changes that may need to happen.  They can quickly create multiple what-if scenarios that provide the foundation for the best decision making possible.

How to Forecast Change in the Workforce

There are many ways to go through planning for changes in the workforce.  First, as stated above, set a strategy to provide everyone using the model the clarity to make decisions that meet the strategic plans.  

If management expects production to increase by 50% in manufacturing, then typically there would need to be an increase in workforce or a plan about using more robotics, which may decrease workforce to meet the demand.  However, without this information, the accuracy of the plan will not be accurate.

Decide whether to use a top-down, a bottom-up, or a hybrid of the two approaches.  A top-down approach is when senior management determines the plan and pushes it down to the rest of the company.  A bottom-up approach is when line managers plan and then it rolls up to a consolidated plan.  A hybrid is using both methods and then comparing as different versions. 

Executives can provide a top-down version as a guideline for the managers.  The key to a top-down approach is a model that provides quick what-if scenarios based on adding new hires, modifying benefits, or terminating a percentage of the workforce as an example.

The bottom-up approach typically has two methods and uses the approach that best fits your organization.  One way is to forecast at the employee level.  Managers would go in and enter in new hires, possibly terminations, raises, overtime, and any benefits that a manager would have information on.  This method is very accurate but may have flaws if there is a lot of turnover.  The second method is to plan by headcount by position.  This method would list a job title and how much headcount along with an average salary.  This is not as accurate as it uses an average salary, but works for large organizations that have many people in similar positions. 

Finally, it is important to understand how the organization has been in the past regarding their workforce.  Ask questions such as the following:

  • Is the organization good at hiring or firing?
  • Does it hire too early or too late?
  • Does it usually run a very lean organization or does it get too large?
  • Do certain departments get more budget than other departments?

Understanding this information is vital as it should be included in the plan.

 

The image below offers an example of a partial forecast form where a user can enter a goal, make changes, and see real time changes.

Payroll Forecast

Workforce Forecast – Reporting Process

Reporting is the last step and this comes down to two main parts.  

First, have reports to determine the variances of the workforce forecast against the actual by department.  Next, analyze and document the variances.  Determine if the variances were due to assumptions being incorrect and then modify the model so that it can be more accurate going forward.  If the variances are due to changes in decisions, then document it so that it can be accessible in the future, in case questions come up.

Second, determine the workforce metrics that are important for the organization.  Below are some metrics that may be useful:

  • Revenue/Employee: tracks productivity of the organization over time.
  • Employee Turnover: number of terminations divided by average number of employees.¬† Note modify to separate out voluntary and involuntary terminations.
  • Benefits Cost/Employee: determine trend by dividing all benefits by employee.¬† A variation is dividing this by total payroll.
  • Overtime Percentage: overtime divided by total payroll.
  • Time Since Last Promotion: average time in months since last promotion.¬† This can signify an issue if many top employees are leaving.
  • Time to Hire: the number of days it takes from posting a position to signing the offer letter on average.
  • Engagement: use a survey to ask questions of employees annually and compare over time.

 

This dashboard example is provided by Microsoft shows visual workforce analysis Рhttps://docs.microsoft.com/en-us/power-bi/sample-human-resources. 

Solver offers an array of workforce demand forecasting models to help set you up for success. You can review some examples in the images below.

Human Resources Dashboard

Contact Solver to Learn More about Workforce Forecasting 

Workforce forecasting is an imperative function for all organizations and it all starts with a good strategic plan. After that is complete and communicated, then provide data access and create models that can enable world-class decisions. Finally, analyze the reports, review the metrics, and make changes based on the analysis.

Our team at Solver can help set you up for workforce planning and forecasting success. Contact our team today or request a demo for more information about our corporate performance management tools.

Example of an automated statistical forecast

At the core of every business strategy for products and services is to provide optimal management of the supply chain. A leak or inefficiency in the supply chain eats away at the organization’s bottom line. Supply chains are becoming more complex with more variations in products, distribution channels, and material planning. Accurate supply and demand planning are essential for optimal productivity and profitability.¬†

How does a business obtain an optimal supply chain? It comes down to how well an organization can develop a demand plan. Demand is never linear and rarely easy to predict. A planning team needs to have the right historical data that can be used to create a statistical forecast, achieve consensus from the stakeholders, and be quick to pivot on changing internal or external market trends. In this article, we seek to define and discuss the elements of demand planning, analyze the cost of failure, and outline the steps to success.

What is Demand Planning?

Demand planning is a multi-step process to forecast demand, improve accuracy of forecasts, and align inventory with peaks and troughs of demand. In other words, demand planning is the process of forecasting demand for a product or service. 

Successful demand planning is defined by having the right balance of inventory levels to meet customer needs while minimizing inventory surplus or deficiency. 

Here are the four crucial aspects of demand planning in order of importance: 

  • Product Portfolio Management – Product portfolio management oversees the entire product lifecycle. It starts with the introduction of a new product to the eventual end of its product life cycle. Upkeep and maintenance of product data is key to statistical forecasting.
  • Statistical Forecasting ‚Äď Build a forecast with past inventory data, sales data, and appropriate product history to predict future data or trends.
  • Trends (Internal and External) ‚Äď Build into your forecast an estimate of casual influences from internal and external trends. Internal trends include seasonality of your products and hiring talent to scale. External trends include unexpected economic crisis, competition, socio-cultural, legal, and political forces.¬†
  • Events and Promotions ‚Äď Once a forecast is generated with the above factors in mind, events and promos can be used to help hit your S&OP targets.

The aspects of demand planning go beyond the statistical components of a demand forecast. Demand planning leverages accurate demand forecasts to create action plans for the organization while being privy to internal and external factors that affect supply at all steps of the chain and consumer demand. 

Implementation of demand planning is using analytics of product data and trade promotions to hit sales and inventory targets. Organizations must be quick to pivot and adapt to changing market conditions even after starting a demand plan. 

Demand planning is an ongoing effort to ensure peak profitability management. 

Importance of Automated Demand Planning

Failure to adopt an automated statistical forecasting and demand planning approach can lead to a wide range of issues such as missed deadlines, unhappy customers, Inventory surplus or deficiency or delayed response to market dynamics. 

Delayed response means your business can lose a competitive edge or fall behind competitors. Inability to act quickly on supply chain disruptions has material impact on both top-line and bottom-line numbers and you can end up losing market share to competitors. Below is a list of some of the business impacts of not utilizing automated planning strategies: 

Lost credibility

Losing credibility means lost business. Inability to fulfill customer orders due to bad inventory planning will lead to permanent damage to trust that customers have with your company. This will impact future orders and leave your brand with a significant damage to reputation. 

Wasted Resources

Overestimating customer demand for products leads to significant waste in time, money, and personnel. If turning over inventory fast enough becomes difficult, your business’s cash flow will be impacted. Having high levels of excess or obsolete inventory can lead to significant financial losses. 

To mitigate high costs of failure, businesses now more than ever need to trust the numbers and adopt a sophisticated demand planning strategy that leverages data and market insights. Adopting automated demand planning strategies will lead to actionable forecasts.

Aspects of Demand Planning

Understanding the work required within each element of demand planning will allow you to create the most accurate, up-to-date forecasts that will better inform your Sales and Operations Planning (S&OP).  

1. Product Portfolio Management 

Many times, past sales performance can be used to forecast future sales performance. It is important to regularly upkeep and cleanse product data. Relevant data might include inventory, stockouts as they occur, seasonality, sales, and consumer demand through peaks and troughs. The difficulty here usually is the number of systems keeping these data sets as isolated transactions. 

 

2. Statistical forecast 

Forecasts need a reference point, historical data in sales, inventory, and demand. Basically, what was actualized in the past can be a good indication of future sales. But not all data is useful, old data is typically not as useful as more current data as it might not correlate with future demand. The same bad situation happens when you do not use enough data to create a forecast. The right amount is typically trailing 24 months of most recent data. 

 

 

Example of an automated statistical forecast

Example of an Automated Statistical Forecast

 

Example of demand planning of weekly sales by item

Example of Demand Planning of Weekly Sales by Item

3. Internal Trends 

Internal trends relate to staffing issues at a level in the supply chain, seasonal demand due to product type, frozen capital, slow turnover, stockouts and general unpredictable sales volatility. Internal trends affect even the best steered businesses which makes it imperative to factor these causal influences into the forecast.  

 

4. External trends

External trends are another form of causal influence, but less predictable and usually harder to build into the demand planning forecast. External trends usually force a business to reforecast whereas internal trends are less likely to lead to a new forecast. Businesses that do reforecast and act on changing external trends like an economic recession or changing political climate are best positioned to succeed.  

 

COVID-19 has disrupted the majority of supply chains around the world in unprecedented scale. Amazon is probably one of the most recognizable organizations that has put tremendous effort in shifting their supply chain to prioritize shipping of essential items.

 

In light of COVID19, Amazon quickly refocused shipping priorities and product fulfillment to consumer essential goods. They have been quick to scale, pulling personnel and distribution resources from nonessential consumer goods and hiring 175k new workers in two months. 

 

Demand planners must be quick to identify factors that can impact demand such as natural disasters, news events, internal and external unanticipated issues. To do so, an organization needs to be armed with a central repository of all their information to generate an accurate forecast and adapt to changing market conditions to meet customer demand. 

 

5. Events and promotions

A time bound product promotion might lead to more sales in that time interval at a lesser margin. Holidays like Black Friday and Christmas can generate more sales in those few days than a whole month. 

Once a forecast is set, there needs to be a consensus on the actionable plan that comes out of the forecast. Part of this actionable plan is using events and promotions to hit sales and inventory targets. You want the right balance of inventory turnover, sales, while reducing COGS, and reducing waste in resources. Promos and external sales initiatives can help you get there. 

The Future of Demand Planning

Demand planning is becoming increasingly digital with advances in technology and machine learning. Demand planning software is being developed to better position businesses to adapt and update forecasts real time. Increasing number of businesses are now using CPM tools integrated with their ERP system to create multi version forecasts that are constantly updated and refined to estimate future sales. 

A successful demand planning action will lead to countless benefits including: 

  • Lower inventory costs
  • Decrease in stockouts
  • Waste reduction (obsolete inventory)
  • Increase in on-time, in full deliveries
  • Decrease in expedited shipping costs
  • Better pricing negotiation with suppliers

Contact Solver to Learn More about Demand Planning Software

Solver offers a flexible planning solution where powerful input forms are designed in Excel and deployed in the cloud. Solver can fit any business needs from a manufacturing company trying to forecast sales by month to a  retail business looking to forecast SKU based on historical data. 

Solver’s cloud  CPM solution is fully customizable to fit your demand planning needs. Contact our team today or request a demo for more information about our corporate performance management tool.

Back-view-image-of-businessman-with-arms-crossed-behind-head

This article will help you navigate budgeting and planning with corporate performance management solutions.

Man in suit looking at analytics for his budget and planning project

Image taken from Shutterstock.

Lire la suite