Prior to business school, I dabbled in a start-up business
in the racing and exercising industry. During my MBA education, I was involved
in assisting with the Miller New Venture Challenge (BYU’s business competition),
interning with Pelion Venture Partners (a top decile VC firm in Salt Lake
City), and serving as part of Cougar Capital (BYU’s student-run venture capital
fund). In all of these endeavors I
worked extensively with revenue projections and felt that the tools to which I
had access were lacking. After taking this VBA class, I knew I could do
something about that problem.
An entrepreneur relies heavily on her revenue projections.
The majority of her business decisions revolve around what will bring in the
most revenue. In the business world, the only person more concerned about her
revenue projections is perhaps the Venture Capitalist who invests in her
business. The VC often uses the revenue
projections to determine how much the company is worth. Unfortunately, the
company value is only as accurate as the assumptions driving the projections’
model. If one adjusts these assumptions even slightly they give an entirely
different projection and could completely change whether or not a business is predicted
to be profitable and whether or not Venture Capitalists are likely to get a return
on the money they invest in the company.
Several years ago, BYU’s Business Plan Competition Director created
a simple model to help entrepreneurs determine their revenue projections. This
rudimentary model is driven by customer acquisition rates through email. The
assumptions behind the revenue are calculated by merely having the entrepreneur
arbitrarily list three inputs: (1) how
many people she thinks will open her emails, (2) how many of those people will
click the link in the email, and (3) how many of those people will actually
convert to a user of the entrepreneur’s product. Then, those inputs are used to
predict yearly revenues over five years. These revenue forecasts are then
transmuted into a balance sheet that calculates owners’ equity. This equity is
used to calculate price per share each year, determining how much the company
is worth. Because the entire model is driven by the entrepreneur’s inputs for
marketing information around customer acquisition rates, this model is very sensitive
to change, making it highly subjective to the entrepreneur’s assumptions around
various conversion rates.
In order to make this model a better estimate of what might
actually happen to the business’ value over the next five years, I coded a
ribbon modification that runs a Monte Carlo selection procedure of the
entrepreneur’s three inputs. The simulation then projects probable revenue ranges
and share values to analyze the forecasts. The inputs for the simulation are
recorded through a user form that gathers each marketing assumption’s initial
value and low and high ranges for those values. It also asks for how many
iterations the user would like to run. Obviously, a more thorough and accurate
analysis will require more iterations. Nevertheless, the user form has an exit
button to allow the user to exit the procedure in the event that the user
inputs too many iterations (e.g. near or outside the Integer variable type
range) and the processing ends up taking too long. After each iteration the
balance sheet rebalances in order get the updated ownership percentages and
share price. Each iteration of data is recorded, and the code creates a chart
object to visually display a histogram that continually updates while the
simulation is running. Incidentally, this chart is added into a worksheet and
formatted entirely programmatically – this was more difficult than either
creating a chart through the Excel interface or creating a chart object as its
own worksheet object. The user form gives the user the option of viewing the
histogram changes as each iteration runs or unchecking this option to speed up
the simulation and only display the final results. Either way, the user can
look at the histogram and see the likelihood of a range of various five-year
share price growth rates. From these growth rates, she can determine the most
likely scenario, input the data in the assumptions tab and rebalance the data.
This data will now be more than just a random guess, allowing both her and the
VCs to make better decisions.
As part of creating this tool to build more accurate
projections, I also fixed the existing code (only five lines of goal seek
function calls) in the original model because it was clunky in that it required
the user to manually run a balance sheet macro several times in order to
balance the balance sheet. Now the balance sheet macro is called automatically
at the end of each iteration in the simulation.
With this VBA coding, the model now gives a much more
accurate forecast of revenues that in turn give a much more robust analysis of
the share price. Using this code the entrepreneurs can better see if their
business is likely to be profitable and VCs can better estimate their return on
investment.
No comments:
Post a Comment