Company and Problem Background
VitalSmarts is a privately held
corporate training company with headquarters in Provo, UT, which sells training
programs, such as Crucial Conversations
Training, that teach communication and leadership skills to a company’s
employees. VitalSmarts’ executive leadership
has recently asked that the Sales department provide monthly sales forecasts from
each sales representative and client.
This data is used by sales management and the company leadership to
understand what revenue they should expect in a given month, depending on the
percentage of confidence the sales rep has assigned to the given sales
opportunity. In this way, management
knows whether they need to push sales reps for more revenue, and they also know
how to manage budgeting for the next month.
The Sales department had been
creating sales forecasts for only 2 or 3 months, and the process was very
manual. A sales rep would type their
forecasted sales opportunities into an Excel workbook template, then send that
workbook to their manager. The Sales
Operations team would somehow aggregate the data from the 20+ sales reps’ worksheets
and present it to Sales management and the CEO each month.
VBA Solution
The solution I have created for
this sales forecasting problem automates much of the reporting process in a
clean user-friendly interface. The solution
consists of two user-facing workbooks.
One is a workbook for sales reps that allows them to enter, edit,
delete, move, and track all their forecasted sales opportunities for the
current month plus three months forward, as well as past opportunities. The second workbook serves as a “dashboard”
for Sales management and the CEO, which lists all sales opportunities for all
sales reps for the current month plus three months forward. The workbook allows management to filter the
opportunities by month and by sales rep, and to see the total forecasted
revenue plus the total adjusted forecasted revenue (adjusted for the percentage
of expected close that the rep has assigned to the opportunity). Each sales rep will have their own workbook
hosted on their computer, and each of those workbooks will send their data (the
sales opportunities) to the managers’ workbook through a third workbook that
just acts as a sort of intermediate transfer database.
The new program created with VBA
adds a lot of functionality for the sales rep without creating any more complexity. For management, the solution provides a
clean, simple, automated dashboard where they can quickly understand the
forecasted revenue for the month and can drill deeper if they want information
for a specific sales rep’s accounts.
Files
(you have to download the workbooks to the same folder!)
Transfer workbook: http://files.gove.net/shares/files/15w/cmatthew/transfer.xlsx
No comments:
Post a Comment