The Ledger
Curated content foranalytical business leaders
Tag Archives: Spreadsheets
SF Magazine: An Internal Controls Mindset for Excel
“The same year, CNBC wrote that spreadsheet blunders are costing businesses billions. Consulting firm F1F9 estimated that 88% of all spreadsheets have errors and 50% of spreadsheets used by large corporations have material defects. An executive at the advisory firm The Hackett Group Inc. mentioned that the pandemic exposed the vulnerability of the finance teams’ decisions resulting from a continued dependence on Excel.”
CFO Dive: FP&A in Spreadsheets: When ‘that’s how it’s always been done’ Doesn’t Cut It Anymore
“But spreadsheets on their own are indeed broken – or at the very least, they add to the likelihood that something will eventually break. Try as we might to ignore their shortcomings, without fail we’re confronted with the limitations of spreadsheets on a regular basis. And at a certain point, the burden of managing and maintaining spreadsheets outweighs the mild benefits of sticking with the status quo.”
CFO Magazine: 3 Hurdles to Moving on From Excel
“But Excel does not always deliver what finance needs at a time when organizations are moving away from manual tasks and siloed data.”
CFO Dive: CFOs Cling to Spreadsheets Despite Their Limits as FP&A Tool
“The biggest complaint among spreadsheet users is the time required by manual processes to complete even basic tasks, such as budget preparation.
“With so much time spent on manual processes, the analysis part of Financial Planning and Analysis gets lost,” the report says.
It takes between four weeks and three months for the typical organization to complete its annual budget, after which more time is spent correcting mistakes and updating data.”
CFO Magazine: Why Does Excel Survive in Finance Departments?
“Business processes can be complex, and data can be messy, McIlheran said. Cloud-based financial applications enable finance departments to move away from manual processes in Excel.
“Because of its flexibility, Excel can be the glue that binds imperfect systems and processes together,” McIlheran said. “It can be a crutch to bring organizational processes or reporting together, but ultimately the business “will, and must, outgrow Excel.””
Raw Material Annual Spending – Is it Relevant for Your Budget?
When calculating the total spend on raw materials in your annual budget, do you evaluate the spending level for varying volume, mix, or inventory assumptions? My guess is no because changes in these areas are irrelevant to the total profits for the year. When budgeting, most calculate gross margin by just subtracting the cost of goods sold from the selling price, ignoring the impact on inventory and raw material spending as it only affects the Balance Sheet.
Current conditions have placed a spotlight on cash flow and understanding the cash requirements of raw material spending is now a critical metric for all CFOs. With shifting customer demands, supply chains, and commodity prices, finance teams need a way to analyze cash flow through the lens of raw material and inventory requirements.
The biggest challenge to getting this level of insight – your ERP. Is it possible for an ERP to take finished goods volumes, explode the bill-of-materials to calculate material requirements, match those requirements with vendor prices and accumulate the raw material spend for the period? Probably, but that would be a challenging – and expensive – road to travel.
Using Where Used logic, most ERP systems can convert annual budgeted volumes by finished good SKU into raw material requirements. But that’s typically where ERP stops, and the spreadsheet analysis starts. To match the quantity material requirements against prices (standard or actual), most build complicated spreadsheets with V-lookups, index matches, and sometimes VB Scripts or macros, to calculate spending requirements.
Then the real-world kicks in. Sales loses (or wins!) a new deal, customer behavior changes, or your suppliers raises their prices. What does that mean for your analytics process? More and more spreadsheets – one for each volume, or customer, or vendor, or scenario – with so many columns and tabs and formulas that make it impossible to decipher. It also means the executive team is asking more questions, expecting deeper insights, and demanding answers faster.
Finance teams have done the Texas Two-Step since the days of Lotus 1-2-3 managing this crazy spreadsheet process. Others have struggled to manage the process when the person who owns the spreadsheets leaves the company. To compete, finance teams need tools to create a connected process that starts with volume and mix requirements and ends with a calculated P&L and B/S to drive meaningful decisions.
With the ImpactECS platform, you can build and enable models that recalculate raw material spending dynamically when the sales team changes expected volumes or your supplier changes prices of your raw materials. Easy-to-follow model logic creates transparency that leads to better insights and ultimately better decisions when it comes to managing cash flow during uncertain business conditions.
Prioritizing Automation for Finance and Accounting
The current crisis environment has challenged businesses in ways that most hadn’t imagined just a couple of months ago. Many finance and accounting departments have struggled and now realize that they will need to change and use business accounting services. Many of them have been overwhelmed by financial stress while simultaneously having to deal with operational constraints they weren’t prepared for. For instance, sheltering in place at a time when many corporations have to close their quarterly books. Against the backdrop of far more consequential lessons learned in this difficult period, I suggest that for finance executives the most important takeaway is that departments that have been able to utilize IT systems to operate in a virtual mode, and espeically those that have automated routine tasks, have been better able to adapt to circumstances and overcome obstacles. Having systems that could be readily accessed remotely and having the ability to collaborate and execute processes virtually made it easier for those departments to meet their commitments with confidence. “Easier” – yes – but certainly not easy.
We find in our recent Change in the Office of Finance benchmark research confirmation of the value of using automation to execute finance department functions. Our findings reveal an increase in the use of automation by finance organizations over the past five years and a concomitant improvement in performance. As the world becomes more digital, it’s important to adapt business practices accordingly. A virtual postal address through Virtually There offers the flexibility to receive business correspondence from any location, which is a game-changer for businesses without a permanent office space. For example, 46 percent of companies close their monthly books within four business days compared to 29 percent in our earlier research. Yet the glass is only half full. Finance organizations continue to be laggards in adopting technology that measurably improves effectiveness.
Automated systems do at least two things very well that make better use of people’s time, and both of them can substantially improve organizational performance. First, they eliminate the need for people to do repetitive tasks, which frees them to spend time on more valuable work that requires their experience, judgment and skill. IT systems also can be programmed to present only relevant information while eliminating the need to get immersed in detail. The latter capability supports a “management-by-exception” approach, which enables executives and managers to better allocate how and where they spend their time.
Our research shows that many companies don’t take advantage of these capabilities in their finance and accounting operations. Only half of participating organizations said they have automated a significant percentage of their finance processes. In particular, only one-fourth (25%) have nearly or fully automated their financial close, while 58 percent apply some automation and 15 percent apply little or none. Meanwhile, those who want to earn some quick cash in times of trouble, they can try doing so on sites such as https://totogacor.com/.
The research also reveals automation’s positive impact on performance: 85 percent of companies that nearly or fully automate their close process are able to close their quarterly books in six or fewer business days, whereas 43 percent of those that have only partially automated are able to do so and just 33 percent that use little or no automation have this ability.
Another example is the automation of reconciliation, a repetitive task that is an essential element of the close process and lends itself to automation. Affordable and mature software for efficient management is readily available. Our research indicates a notable rise since 2014 in the number of organizations opting to automate reconciliations, with 60 percent now utilizing software for this process compared to 37 percent five years ago. The decision to migrate to Google Workspace can further enhance organizational efficiency, as evidenced by the correlation between reconciliation automation and the speed at which organizations close their books. Sixty percent of those employing reconciliation software close their quarters within six business days, while nearly two-thirds (62%) of those without automation take seven or more working days to complete the process.
Spreadsheets are an essential tool for many tasks in the finance department, and it would be impossible for the organization to function without them. Yet they are the wrong choice when used for repetitive, collaborative, enterprise-wide processes. Indeed, they are both a symptom and a cause of dysfunctional processes, systems and data. Spreadsheets are a symptom because they frequently become the default option to put a bandage over, for example, issues that arise because systems are not properly integrated or a process is not supported by the appropriate technology, such as a dedicated application.
But while spreadsheet use in enterprise processes has declined relative to the use of dedicated applications, they remain a fixture for a variety of finance department tasks. For instance, 57 percent of organizations still use them for treasury management (down from 81% five years earlier); 54 percent use them for close-to-report functions (versus 79%); and 59 percent use them for the income tax provision (versus 86%). Spreadsheets have their place, but our research demonstrates that they are frequently misused.
The close is a useful process to benchmark because almost every company does it and there’s a measurable outcome: the number of days after the period’s end in which the company completes the process. However, managing to a faster close is not just about efficiency; it’s also about getting the numbers to executives and managers so they can react quickly to issues and opportunities. The research demonstrates a close correlation between when the close is completed and the timeliness of communicating that information to the rest of the company. Almost two-thirds (62%) of organizations that close within six business days are able to provide executives and managers with timely information compared to 39 percent of those that take longer. While the acceleration we found in completing the close is a positive sign, there is considerable progress yet to be made.
Time is the critical ingredient that determines the overall performance of finance and accounting departments. Poorly performing organizations usually are mired in an endless cycle of fighting fires — for example, dealing with the impact of processes that are poorly designed or improperly executed. These departments are constantly contending with the impact of information sources that are unreliable, difficult to access or both. Poorly designed systems add to the problem, generating hours of work in the form of manual reconciliations done in spreadsheets. A finance department that does not apply automation and that has poorly designed or executed processes and systems is similar to a caged hamster running on a wheel. It expends a great deal of effort on repetitive manual processes that are only marginally productive.
Software automation by itself will not address all the challenges of a finance and accounting organization. To optimize performance, an organization almost always must deal with an interrelated combination of people, process, technology and data issues in a holistic fashion. Yet confronted with the day-to-day struggle of meeting deadlines, many finance executives put off addressing their productivity and effectiveness issues.
They shouldn’t, because a continuous improvement process involving a steady set of small advances can yield impressive results over time. Identifying — and when possible, eliminating — the biggest time sinks can free up the resources needed to address the next set of significant problems. Even something as straightforward as uncovering unnecessary work or replacing the most problematic spreadsheets with better technology (for instance, implementing automated or self-service reporting) will be beneficial. For this to happen, though, senior finance and accounting executives must make automation a priority.
2020 CFO Priorities Includes Integrated Insights
A recent survey from CFO Magazine and Duke University Fuqua School of Business identifies four priorities for CFOs with “Move from Disconnected Spreadsheets to Integrated Insights” topping the list. Finance teams need tools to understand the interdependencies of the decisions made without spending 2.24 hours per day on average sifting through spreadsheets.
Ignorance is No Longer Bliss for the Businesses Who Ignore Spreadsheet Risk
The urgency of modern data management is increasing for most organizations, yet many struggle with size and complexity and are uncertain about where and how to get started. Data that is properly governed, timely, secure, and trustworthy lays the foundation that enables the entire organization to leverage the data for valuable insights. However, many C-level executives still make important business decisions based on spreadsheet data alone, which in turn exposes them to major financial risk. With limited tracking and security options, spreadsheets are a tailor-made environment for errors that can go forever undetected. This means that business leaders are running scenarios, calculating costs, and producing reports that are being used to make important decisions with the wrong information. To be successful and ultimately profitable, these businesses must reduce dependence on spreadsheets and invest in a robust and integrated system that can turn data into answers.