MS Excel is commonly used for tracking of business activities. As a result we often have to assist clients in setting up tracking spreadsheets in Excel. Typically there is already a spreadsheet in place, but due to its structure it is difficult to update and report on.
Not only must the tracking spreadsheet capture the raw data, but it needs to be able to easily generate reports. This typically includes ways to notify users of upcoming issues.
Commonly these assignments will involve some information already stored in spreadsheets (not ideal) and other tracking systems. Combining multiple sources of information is typically a slow, cumbersome process. With planning and Excel, you are able to keep your tracking spreadsheets up to date and relevant.
Below some of the tracking spreadsheets we have been involved in. For more information email info@AuditExcel.co.za .
Table of contents
- Foundry- Tracking spreadsheet of jobs from order to delivery in Excel
- Tracking allocations of legal cases with Excel spreadsheets
- Tracking document flow, regulatory timelines and KPI’s on property management
- Retail sales tracking- monitoring shops
- Sales lead monitoring spreadsheet (stop overlapping leads)
- Sales pipeline tracking and capacity planning
- 13 week cash flow forecasting spreadsheet
- Contact us about your tracking spreadsheets in Excel
Foundry- Tracking spreadsheet of jobs from order to delivery in Excel
The client required a way to capture and follow a job from order to delivery. We also needed to keep track of key document numbers and dates. We created a simple logistics tracking spreadsheet.
Based on the tracking spreadsheet, they were able to generate reports for:
- problem jobs to be reviewed,
- number of jobs exiting one stage/ entering the next stage,
- long outstanding jobs, and
- lost jobs.
By utilizing this information they were better able to plan their work flow. There was the added benefit of predictive ordering of raw materials.
Tracking allocations of legal cases with Excel spreadsheets
Multiple lawyers working on multiple cases with no clear, easy way to get an overview of case activity. By customizing their existing tracking spreadsheet we allowed them to:
- Create a new sheet for each month BUT have it summarize (automatically) onto a database in the spreadsheet. This allowed each lawyer to monitor their own activities in their preferred way (a sheet for each month), but have the data in a consolidated format so the reporting could be done across months.
- Have all the individual lawyers spreadsheets pull up automatically to a central spreadsheet that allowed for detailed reporting including information about types of cases, geographic distribution and status of each case across periods and lawyers.
Tracking document flow, regulatory timelines and KPI’s on property management
A client needed to monitor the KPI’s on there management of various properties. For various reasons it needed to be in an Excel tracking spreadsheet.
We needed to combine data from 4 different systems including financial and document workflow systems. All properties had multiple periods that need to be tracked. From approval of budgets, to finalization of AFS’s, regulatory submission dates and completion of required meetings, everything needed to be tracked.
Required turnaround times had to be monitored against actual performance. A ranking system was developed to prioritize buildings where the property management activities were falling behind schedule.
As a result, it was further possible to compare branches and individual teams to identify possible bottlenecks and problem areas.
Retail sales tracking- monitoring shops
A client had a manual system, using spreadsheets, to track activities in the shops on a daily basis. Due to the setup though it was very manual- lots of emails, versions of spreadsheets and copy/ pasting.
Without changing the main spreadsheet, we set them up to easily connect to each other so that, on a daily basis, the manager just needed to open the consolidation spreadsheet and all information, from all the retail branches (including number of visits, pickups by day etc) was readily available and sent to directors.
Sales lead monitoring spreadsheet (stop overlapping leads)
Multiple sales people following multiple leads runs the risk competing against yourself.
The lead tracking spreadsheet was set up in Excel to record the details of each lead. This allowed identification of possible duplicate leads, even if the spellings of the various names were not exact.
Sales pipeline tracking and capacity planning
The client needed the ability to monitor their sales pipeline to assist with capacity planning in the factory.
Included in the tracking spreadsheet needed to be confirmed jobs and sales pipeline based on various levels of confidence. This was then matched to known capacity of both the equipment and staff in order to identify bottlenecks.
Also assisted in pricing of the jobs based on estimated factory usage. See more on our Capacity Planning with Spreadsheets case study.
13 week cash flow forecasting spreadsheet
Assisting in converting a forecast into a detailed, 13 week cash flow that tied into the monthly reporting and allows comparisons to the budget.
The spreadsheet also needed to be flexible to allow for daily updating. It was also possible to run What- If’s through the spreadsheet. .
Contact us about your tracking spreadsheets in Excel
For more information on how we can improve your tracking spreadsheets, send an email to info@AuditExcel.co.za.