These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, April 15, 2014

In Search of a More Accurate Share Price

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

Blog Archive