Home ∙ Bespoke Spreadsheet Design

Past Project List

These are just a few sample projects worked on over the years.  There has been plenty of other projects of various sizes.

For reconciling a sales ledger for a factoring company monthly report.  [More...]   

Performance related bonus scheme for staff.  [More...]   

Shift allocation system.  [More...]   

Job time tracking for invoicing consultancy fees.  [More...]   

Franchise random tools audit list.  [More...]   

Management Accounts automated spreadsheet.  [More...]   

[Top of page] 

Each spreadsheet system for completing these projects is built like a normal software package, for the end users, it's not like using a normal spreadsheet, no spreadsheet knowledge is required to use them.

Also, most  of these spreadsheets have been developed further over the years as with all businesses the goal posts move as time goes on.

[Top of page] 

For reconciling a sales ledger for a factoring company monthly report

For a  company who factors their Sales Ledger to a Factoring company.

Designed a spreadsheet where the Sales Ledger transactions for the month are imported from their accounts package in a very simple way.

From within the spreadsheet, this imported data is automatically organised into various pages of the spreadsheet depending on the transaction type, being added to unreconciled transactions from the previous month.

From the Factoring company statements, the end users mark transactions off as they are reconciled.

At the end, any transactions remaining on the Factoring company statements are added to a page of the spreadsheet.

The spreadsheet then produces a report which allows the end users to investigate unreconciled transactions and make the necessary changes in the new month on their accounts package.

Finally, the spreadsheet produces a report in the same format as required by the Factoring company to send back.

[Top of page] 
 

Performance related bonus scheme for staff

A series of spreadsheets were designed that monitored income figures by branch broken down by each member of staff and from each team as a whole.

Actual income figures are compared against target figures.

Staff are able to see their figures on a daily basis which helps with motivation.

Management can also see 'live' at anytime what is happening by branch, by team, by consultant and as a company consolidated.

Performance reports are also automatically produced each period comparing consultant by consultant, team by team, manager by manager and branch by branch, all period by period with year to date figures.

Built into these spreadsheets is the Bonus System which monitors performance against target by consultant, team and manager.  This produces reports at the end of accounting periods for payment of bonuses.

[Top of page] 
 

Shift allocation system

We built a spreadsheet system for a major transport company who needed a system to allocate staff to shift patterns without double booking the staff or to have a member of staff booked on overlapping trips.

The system monitors the number of hours a person is booked to work in a day and over a week ensuring that the hours worked are within the regulations.

Shift details are printed out for each member of staff and for shift managers.

Management reports are produced.

Staff complete timesheets which are then entered into the spreadsheets against booked shifts.

Variation reports are produced and more importantly reports for the payroll department so that the staff can get paid.

[Top of page] 
 

Job time tracking for invoicing consultancy fees

For a consultancy company, we built two spreadsheets, the first called 'Timesheet' recorded the time spent working for each client giving a running total.  The spreadsheet then produced a Timesheet Statement which would then be attached to the client invoice.

The second spreadsheet called 'Timer Sheet' ran in the background on a computer, and when a client rang for support, a button on the spreadsheet was clicked which recorded the start time to the second.  When the support call finished, another button on the spreadsheet is clicked which recorded the finish time to the second.  Details were then entered of the client and the topic.  The spreadsheet then produced a Timer Sheet Statement which would then be attached to the client invoice.

[Top of page] 
 

Franchise random tools audit list

For a major car manufacturer, amended an existing spreadsheet which produced random listings of tools kept by franchisees.

This printed random list would then be used to audit the franchise.

The selection for the random list depended on the type of franchise.

[Top of page] 

Management Accounts automated spreadsheet.

A company wanted to have an automated Management Accounts system put in place so that once the accounts have been finalised for the accounting period, they could 'just press a button' to see all their management information such as Profit and Loss statements and Balance Sheet.

The spreadsheet was linked to their Exchequer Enterprise accounting software using OLE technology.  This is a live link so as soon as a figure changes in the accounting software, it changes in the spreadsheet with no human intervention.

When opening the spreadsheet, the user would select which period of the accounts they would like to look at.

The Profit and Loss reports were put in different formats, all as summary and detailed:

Figures for each period going across the page.

Comparison figures against targets and last years figures for current period, year to date and predicted year end.

Revenue detailed by branch.

Since we have done the above, we have done the same for other companies with the spreadsheets either linking directly into their accounting software, or where trial balances are entered into the spreadsheets to produce all the reports automatically.

[Top of page]