One of the most recent projects finished was a self calculating quote sheet for a car repair company. Initially it sounded straight forward and relatively simple, however, the quotes were based on old hand written tables calculating the amount of time required in units of 6 minutes to fix a particular dent of size x and number y.
The sheet was also to be created to be used on an iPad so the employee could move around the car and add up the number of dents per panel and average size of the dents. This would then look up the number in the relevant tables and give them amount of time it would take the mechanic to repair the panel. The employee also had to have the ability to add 3 different types of finish to the particular panel or a discount if the panel was not to be painted on completion.
In order to facilitate the input of data we added a hidden sheet in order to complete the complex calculations without taking up valuable room on the finished product. The yellow tab “Calculation Sheet for Formulas ” is hidden on the final version used by the client on all platforms including PCs, Macs and iPads. Not only does it create a more professional finish it also reduces the risk of the client inadvertently editing the fundamental calculations which are the backbone of the spreadsheet.
The finished product can be seen below, it included a self updating quotation sheet, an invoice driven by the information quoted (including the name and address of the client) and also a sheet which had a diagram of the car panels with the number of dents shown in each panel.
Do you have a similar task that needs completing or an issue you need resolving in Excel – Then get in touch with Bespoke-Excel here.
