SUMMARY

9 Monthly Budgets brought back to Plan

First to notice a tiny "data pattern" clue

Resolving the issue by unearthing 100+ computers / printers

Collapsing unwieldy politicized bottlenecks within 2 Months

"Data inspect/correct rework" tasks, and "Rolling budget adjustments" scrapped

Delivering 8 monthly reports early vs. the practice of 3 months late

Bringing monthly combined budget back to plan

5 positions reassigned during Phase 3

'Budget Check' Process

Project Management Offfice (PMO) manages software changes and upgrades

PMO provides software sub-contract programming services to other corporate units

One of my Project Support Analyst (PSA) tasks was to monitor 800-1,400 monthly computer cost transfers

Receiving data inflows, checking data, reporting to eight managers and PMO unit supervisor

Final Revised Process

BACKGROUND BUSINESS PAIN

Three Months Late Reporting - Accepted Standard Practice

Each consultant uses a computer, and each asset has a cost

Costs shift between 8-10 budgets every month, alongside the consultants that they are tied to

My responsibility was to receive, collate and report the cost shifts for 800-1,400 assets

Using Excel spreadsheets, well before each manager’s budget due date

Reporting three months late had been accepted practice for 5 years

...and managers had stopped asking for deliverables by deadline.

Challenges

Internal Process - "As Is"

Cut & Paste inflowing excel data to master excel worksheet.

Tabulate all reports on master sheet, meet with 8 managers twice, report pre-budget costs

Consult to determine which consultants are going where, submit validation to supervisor

Ignore discrepancies as supervisor requests budget rollovers each month

Upload update to master monthly doublecheck report, which goes to division Director

Internal Culture

The data that I received every month was inaccurate; and no one knew why

Culture did not support taking initiative to find out why

The unit dealt with discrepancies with rolling monthly budget adjustment

Extremely difficult to deliver accurate reports by the official deadline

The practice was to ignore the official deadline as rollover practice worked

Reporting creep had created a 3-month accuracy gap which had become accepted practice for 5 years

INVESTIGATIVE ACTIONS

Investigative Action #1

Took Initiative

While reporting to the accepted practice, noticed oddities in the in-flowing asset data

Investigated the data inflows, discovering that my sub-task process had never been mapped

Result

Discovering that asset descriptions had never been updated when new types were purchased.

Reformulating and re-designing the worksheet unearthed the misplaced costs

Upgraded Process

Using this, we brought all eight budgets back to plan.

Receive reports - Transcribe data from inflowing excel sheet to master excel worksheet

Tabulate all reports to corrected master sheet

Meet 8 managers each once one month prior to manager's reporting deadline

If needed, deal with minimal discrepancies with a second check-in meeting

Investigative Action #2

Forensic study of original workflow

The master process had never been mapped, so followed up with this

Thoroughly mapping “As Is” procedure by interviewing data holders and forensically following data threads

Figuring out how the errors had originally crept in...

Investigative Action #3

Following the tangled data flow threads

By analyzing the threads, determining how the tangle increased error-making

Proposing to collapse the web of tangled data workflows:

By consolidating five positions into one role:

Making the PSA (the “final reporting person”) responsible for original input quality

Tying reward structure to data input quality

Selling the Change

Working the objections, selling the change to managers

Showing how recognizing and addressing process not previously recognized as antiquated . . .

(1) Builds a better team by forthrightly addressing a "wicked problem" in the company

(2) Creates career growth opportunities for staff in 5 units

(3) Cuts significant re-work and tangles out of the process

Impact

Five Manual Sub-processes consolidated into One efficient automated workflow

Management evaluated the proposed process changes

Managers grew the change to an automated new Crystal Reports project

Assigning a project manager (PM) to automate the workflow and data quality checking

PM offering compliments, saying I had ‘saved her months of mapping work’

PMO’s ability to provide budget-advice to our manager-customers improved

Accounting rework stopped; Reporting became timely and therefore useful

After Crystal Reports tool went live, staff in five units shifted to new responsibilities

SUBSEQUENT PROJECT WORK

Selected to Redesign Unit's Excel Master Budgeting Spreadsheets

PSA Work Unit - High-level view of Workflow

Each PSA monitors a large number of software projects

Working with contract Project Managers while monitoring compliance to finance policies

For me, at any one time, monitoring up to 11 projects budgeted at $75,000 to $8 million

Six PSAs and Supervisor, cutting-and-pasting finance data into Working Spreadsheets

Cutting-and-pasting collated Working Spreadsheet results to master reporting worksheets

Background to Upgrade

Working Spreadsheets had grown complex and unwieldy over the years

With monthly equation changes creating conflicting instructions, significant rework and rechecking

Inadvertently creating danger of errors in public funds' reporting

Many of our contract Project Managers had stopped using the PSA-level worksheets

Out of frustration, creating their own project budgeting spreadsheet tools . . .

Since at the end-of-the-day, all data got cut-&-pasted into upper reports anyway

But these issues extended to upper Reports

Example:

A cell holding a reporting sum in our level of reporting was coloured purple

This referred to a specific cost summary flowing from a policy instruction

This data was collated into a higher level purple-coloured summary report cell

But in that upper cell, the reported data was used to report to an exactly opposite summary

Many inconsistencies of this nature flowed through the reports

All data ultimately flowed to the Board for Budget Review

Prior to being selected

Took the initiative to address the issues at my level of reporting:

Redesigning a sample top-level summary worksheet used at my level

: Interviewing team members and project managers

: Throughly mapping PSA budget monitoring process from scratch

: Reviewing intent of colour usage in the "As Is" template

: Creating mini-changes (mini "agile sprints") to test rolling changes

Focusing on Ease of Navigation to improve accuracy and cut rework:

(1) Naturallness of Workflow

(2) Minimizing colours/ensuring consistent use as tool cues

(3) Matching visual workflow to actual steps in reporting workflow

With final redesign proposal, patterning the worksheet to ensure:

(1) Accuracy of Budget Reporting

(2) Removing monthly "new remembering" challenges

(3) Removing the need for constant busywork and rework costs

Subsequently

Being Selected to Redesign our Workheets to "Make them look Pretty"

"Pretty" meant that I had achieved my design goal

Producing an intuitive Naturallness of Navigation for complex data reporting

: Creating a useful combination of minimized colour use, cueing, and navigation flow

: Visual design cueing user to match actions to actual steps of our data entry/checking process

This result flows out of the Design Thinking process of "Form Equals Function"

Where "Pleasing to the Eye" is produced to satisfy a business need

: Step-by-Step Navigation along a consistent path vs. back-and-forth, up-and-down checking

: Intuitive ease-of-use, readability, upgradability, and minimizing rework

: Removing need for constant worksheet design re-checks and corrections

: All future equation changes easy to implement without significant time and rework cost

: Similar in practice to the use of web design "CSS3 url calling" to cut bandwidth and content change costs