What's the best way to automate client outputs from an Excel pricing tool?
If you've lived in the proposals and pricing world for long enough, you know the scenario:
The proposal deadline is "COB Friday". It's 4:30pm Friday and you just finished updating your proposal with the final budget. Then your head of sales calls and says the executive team needs 3 changes to be made before it goes to the customer. While it only takes a few minutes to update the Excel pricing tool, the client outputs need copied out again, formatted, links broken, etc. Your stress level just went through the roof, the deadline gets missed by 15 minutes, and you add "job search" to your weekend to-do list.
In my experience, the single best way to make this a 1 minute stress-free experience in Excel is to program a button that with one click provides a formatted, link-free, clean output that can then be used in Word, PDF, Excel, etc. to get the proposal out the door.
I know creating macros is easier said than done, but if your output process takes more than 5 minutes, you're wasting time and introducing opportunity for error at that critical final step in the process.
While every macro must be programmed specific to each tool, here are the key elements that you'll likely need to include in your programming:
The first step should always copy out the required worksheets to a new file. The macro should never modify the pricing tool itself.
Turn off screen flickering, alerts, possibly even calculations if the tool has hundreds of thousands of formulas.
"Break links" by delete all named ranges, conditional formatting, formulas, comments, shapes, etc. in bulk throughout the workbook.
Loop through each tab to delete rows and columns not needed, autofit text, and reset the view to the top left of the page (among potentially many other steps).
If you have extensive file naming conventions, prompt the file to save with a pre-populated file name and location.
Macros can be finicky- anticipate where errors may arise (mismatched tab names, protected sheets, etc.) and write error handlers for those scenarios with useful messages that instruct the user what to do before continuing on.
Assign the macro to a button the user clicks to generate the final output.
Structure your pricing tool so that the macro does the minimum amount of work necessary. The more work a macro performs, the higher the opportunity for error. Below are examples of steps you want to avoid having the macro do:
Print formatting- you should set this in advance in your pricing tool.
Deleting "sheet level" macros- a buggy process that requires users to change their system settings.
Applying intense filtering logic- instead, create a filtering column on the client output tabs so that the macro just applies that filter to delete what's not needed.
Adding new content, rows, columns- put everything you need in the pricing tool itself.
I wish I could attach a do-it-all universal macro that will work for every situation imaginable. Each pricing tool requires its own programmed solution and every advanced Excel user can learn how to create these.
Whatever you do, stop wasting time and sanity at this crucial final step of the proposals process.
----------------
I write advanced macros! Book a meeting or contact me to discuss how to automate your pricing tool.
