Automating Financial Reporting and Forecasting Process
The Situation
A manufacturing Company with no existing budgeting / forecasting process and little visibility on cash flow profitability was undergoing internal changes and facing external pressures (closure of plants, decline in demand, increase in major material costs, system integration after an acquisition, pricing pressure from customers, etc.). The Company was facing a significant decline in profit and had insufficient cash to pay vendors and operating expenses. Lenders were asking the Company to provide a detailed root cause analysis and cash flow forecast to decide on additional funding. However, data was poorly organized in the Company’s systems and they were unable to generate reliable information and perform any requested analysis. Accordion was engaged to assess and reorganize the entire database, develop a financial reporting and forecasting model, perform detailed root cause analysis, provide strategic options, and quantify the impact.
Services
Finance Function Automation
Budgeting and Forecasting Process Improvement
13-Week Liquidity Forecasting
The Execution
- Used SQL and business intelligence tools to cleanse / extract / manipulate data sets that were in non-standard formats.
- Created 12 mapping tables to reorganize information allowing for a more structured way to generate data from 1,500+ tables in the system.
- Developed a master data pool that integrates general ledger, external sales report, external steel purchase report, vendor report, SKU details, etc., and enables easy drill-down and ad hoc analysis.
- Re-built the monthly financial package using SQL / Power BI / DAX code to streamline and automate the entire bank reporting process.
- Built a detailed bottoms-up forecasting model (by product, by customer, by SKU) that allows for an automated monthly updating process and easy manipulation of assumptions.
- Redesigned a 13-week cash flow model.
- Performed detailed root cause analysis on the missing revenue and profit.
- Delivered profitability analysis and cash flow forecast under different funding scenarios.
- Prepared and delivered initiatives and strategic plans on steel procurement, plant closure, pricing improvement, supply chain reconfiguration, and operating expense rationalization.
- Quantified the impact for each plan listed above.
The Results
With support from the Accordion team, the Company was able to provide a monthly financial package and forecasting model in an automated fashion. The new lead time was reduced to less than one day from one month, thus satisfying the lenders reporting requirements.