Using Excel spreadsheets more efficiently to reduce manual input and speed up the generation of information
The Client’s problem
Client had a number of spreadsheets with totals needing to be copied from one to another to allow input of rebates from suppliers & calculation of final Gross margin. During the existing processes, sales significantly above or below expected Gross Margins were manually checked for errors & then adjusted to account for rebates which may have brought the margin into “acceptable” levels.
The analysis work didn’t start until the end of the month when sales reports were available as it was a time consuming process to get the information from the system reports into their Excel spreadsheets.
This meant that much manual effort was required to arrive at an accurate gross margin figure and any correcting invoices or credit notes were not processed until the following month
How we solved it
First of all, we ascertained that it was possible to run the system reports at any time during the month and that it could be saved to a text file (csv would have been better, but that wasn’t possible). We then developed a macro to split the text file across a number of columns and strip out the unnecessary (repeated header) information from the text file when opened in Excel.
We then built a number of lookup tables in a “master spreadsheet” for the amount of rebate available by product type from each supplier and also for customer/rep/area information together with the necessary formulas to allow the data from the latest report to be recalculated to generate both revised (and accurate) gross margins. This was then further analysed using filters and Pivot Tables to allow margins outside of agreed parameters to be quickly checked & sales & margin performance by customer, rep & area quickly identified
The benefits for the client
As the detailed information could be generated from the system at any time & much of the processing was automatic through macros, pre-populated formulas, filters & Pivot Tables, it was possible to perform the analysis on a weekly, rather than monthly basis.
This had a positive impact on cash flow as any necessary supplementary invoices or credit notes could be issued during the relevant month, rather than, as previously, in the following one; customers then had no excuse not to pay on time.
In addition, sales & margin analysis was much more timely allowing for management decisions to be made more quickly with more accurate information.