Strengthening Trust Fund Forecasting for South Carolina’s Unemployment Insurance Program.

About the Client

DEW is a cabinet-level agency of South Carolina state government, one of 16 such agencies, responsible for administering the state’s Unemployment Compensation Program, collecting unemployment taxes, helping residents find jobs, matching employers with candidates, and publishing state and federal employment statistics. Its core financial responsibility is maintaining the solvency of the Unemployment Insurance Trust Fund, which is funded by employer payroll taxes under state and federal unemployment tax law and pays benefits to eligible South Carolinians who lose their jobs.

Industry Context

State unemployment insurance trust funds sit at the intersection of actuarial forecasting, tax policy, and public accountability. South Carolina’s fund had direct experience with what happens when that balance breaks down: the Trust Fund was depleted during the 2008–2009 recession, forcing the state to borrow from the federal government, with those loans not fully repaid until 2015. Because South Carolina’s employer UI tax rates are set annually based in part on Trust Fund solvency projections, forecasting accuracy has real, direct consequences in both directions — understating needs risks a repeat of fund insolvency, while overstating them imposes unnecessary tax burden on the state’s employers. Like many state agencies, DEW’s forecasting relied heavily on an Excel-based model built up incrementally over time, a common but limiting approach compared with purpose-built financial forecasting and business intelligence software.

Current Situation

DEW released its request for quote in August 2017 seeking a review of the Agency’s current forecasting tools and processes, including documentation of the underlying assumptions used in the forecasts and an analysis of the work performed by the Finance Department to produce them. DEW’s Chief Financial Officer selected Iknow to perform the assignment.

• Significant, unexplained variance in financial forecasts. DEW’s Finance Department was producing forecasts with variability large enough to prompt an independent review, without a clear internal diagnosis of the underlying cause.
• An undocumented, spreadsheet-based forecasting tool. DEW’s central forecasting tool was an Excel spreadsheet whose underlying assumptions had never been formally documented, making its logic difficult to audit or improve.
• High-stakes downstream consequences. Because employer UI tax rates are set annually based partly on Trust Fund solvency projections, forecasting errors translate directly into either fund solvency risk or avoidable tax burden on South Carolina employers.
• An undocumented business process. The actual process the Finance Department followed to produce forecasts, including its data sources and assumptions, had never been formally documented, making it difficult to know where in the process the variance was originating.

• Review DEW’s current forecasting tools and processes.
• Document the underlying assumptions used in DEW’s financial forecasts.
• Analyze the work performed in the Finance Department to produce the forecasts.
• Identify the root causes of forecast variability and recommend concrete improvements, including software tools.

How Iknow Structured the Work

Iknow structured the engagement around five tasks designed to build a factual, evidence-based picture of how DEW’s forecasts were actually produced, rather than relying on how the process was assumed to work.

Key Activities & Decisions

• Onsite Interviews. Iknow conducted 10 in-person interviews with DEW’s finance and executive staff, reviewing the Agency’s forecasting business process, data sources, assumptions, and tools directly with the people who used them.
• Spreadsheet Analysis. Iknow analyzed the existing forecasting Excel spreadsheet in detail, examining its structure, formulas, and embedded assumptions.
• Report and Documentation Review. Iknow reviewed DEW’s forecasting-related reports and the documents received in response to Iknow’s own data requests.
• Historical Expense Analysis. Iknow analyzed a representative set of historical expense profiles for grant contracts to understand how actual spending patterns compared with forecasted figures.
• Root-Cause Synthesis. Iknow synthesized the interview, spreadsheet, and historical data findings into two clear root causes of forecast variability, then researched the commercial forecasting, financial modeling, and business intelligence software landscape to identify four specific product recommendations for DEW’s CFO.

Stakeholders & Collaboration

Iknow worked directly with DEW’s Chief Financial Officer throughout the engagement and interviewed finance and executive staff across the Agency to ground its findings in how forecasting actually happened day to day.

Diagnosing a Process That Had Never Been Formally Documented

Because DEW’s forecasting process had never been written down, Iknow first had to reconstruct how forecasts were actually produced before it could evaluate why they were inaccurate. Iknow addressed this by combining direct interviews across finance and executive staff with hands-on analysis of the spreadsheet’s actual mechanics and historical grant expense data, rather than relying on secondhand descriptions — producing a factual picture of how the process really worked rather than how it was assumed to work.

Balancing Rigorous Diagnosis With Practical, Fundable Recommendations

A purely academic critique of DEW’s modeling approach risked being difficult for a state agency to act on within real budget and procurement constraints. Iknow addressed this by pairing its root-cause findings with a curated shortlist of four specific commercial forecasting and business intelligence products, giving the CFO concrete, evaluable options rather than an abstract call for “better tools.”

Operational Outcomes

• Conducted 10 in-person interviews with DEW finance and executive staff as part of a five-task diagnostic completed within roughly six weeks.
• Identified two primary root causes of forecast variability: a lack of appropriate models that adequately characterized DEW’s expected revenue and expense streams, and incorrect simplifying assumptions embedded in the existing forecasting spreadsheet.
• Delivered a written final report with recommendations and next steps, including four specific commercial forecasting, financial modeling, and business intelligence software products for DEW’s consideration.

Strategic and Organizational Outcomes

DEW’s CFO accepted Iknow’s recommendations in full and asked Iknow to carry out the recommended improvements and systems implementation — converting a short diagnostic engagement directly into an implementation mandate. Iknow’s recommendations also called for making DEW’s financial modeling assumptions explicit, incorporating more sophisticated modeling approaches, and formally documenting the forecasting business process for the first time, addressing the underlying transparency gap that had made the original variance so difficult to diagnose.

Timeline to Impact

Iknow completed the full diagnostic engagement within roughly six weeks. The CFO’s decision to move directly from diagnosis into implementation followed immediately upon delivery of the final report, giving DEW a fast path from assessment to action.

Core Skills

• Financial Forecasting Assessment
• Business Process Analysis & Documentation
• Financial Systems & Business Intelligence Software Advisory
• Public-Sector Finance Advisory

Methods & Frameworks

• Stakeholder interview-based diagnostic methodology
• Spreadsheet and financial model forensic analysis
• Root-cause analysis
• Vendor and product landscape research and shortlisting

Technologies & Tools

• Excel-based forecasting model analysis
• Commercial forecasting, financial modeling, and business intelligence software evaluation