Constructing Guitar Applications in Mathematica, Part I

Setup

In the following, we’ll lay the foundation for any number of guitar-oriented functions in Mathematica. This post will focus on graphic representation of the fretboard. Later posts will focus on interactive fretboard design, as well as interactive musical applications generally.

We begin by calling Mathematica’s built-in Music package, and setting up the named tones corresponding with the guitar fretboard.

Needs["Music`"];

Clear[c2, cs2, df2, d2, ds2, ef2, e2, f2, fs2, gf2, g2, gs2, af2, a2, as2, bf2, b2, c3, cs3, df3, d3, ds3, ef3, e3, f3, fs3, gf3, g3, gs3, af3, a3, as3, bf3, b3, c4, cs4, df4, d4, ds4, ef4, e4, f4, fs4, gf4, g4, gs4, af4, a4, as4, bf4, b4, c5, cs5, df5, d5, ds5, ef5, e5, f5, fs5, gf5, g5, gs5, af5, a5, as5, bf5, b5, noteSharps, noteFlats, noteSharpsAll, noteFlatsAll]; {c2, cs2, df2, d2, ds2, ef2, e2, f2, fs2, gf2, g2, gs2, af2, a2, as2, bf2, b2, c3, cs3, df3, d3, ds3, ef3, e3, f3, fs3, gf3, g3, gs3, af3, a3, as3, bf3, b3, c4, cs4, df4, d4, ds4, ef4, e4, f4, fs4, gf4, g4, gs4, af4, a4, as4, bf4, b4, c5, cs5, df5, d5, ds5, ef5, e5, f5, fs5, gf5, g5, gs5, af5, a5, as5, bf5, b5} = {"C2", "Csharp2", "Dflat2", "D2", "Dsharp2", "Eflat2", "E2", "F2", "Fsharp2", "Gflat2", "G2", "Gsharp2", "Aflat2", "A2", "Asharp2", "Bflat2", "B2", "C3", "Csharp3", "Dflat3", "D3", "Dsharp3", "Eflat3", "E3", "F3", "Fsharp3", "Gflat3", "G3", "Gsharp3", "Aflat3", "A3", "Asharp3", "Bflat3", "B3", "C4", "Csharp4", "Dflat4", "D4", "Dsharp4", "Eflat4", "E4", "F4", "Fsharp4", "Gflat4", "G4", "Gsharp4", "Aflat4", "A4", "Asharp4", "Bflat4", "B4", "C5", "Csharp5", "Dflat5", "D5", "Dsharp5", "Eflat5", "E5", "F5", "Fsharp5", "Gflat5", "G5", "Gsharp5", "Aflat5", "A5", "Asharp5", "Bflat5", "B5"}; noteSharpsAll = {c2, cs2, d2, ds2, e2, f2, fs2, g2, gs2, a2, as2, b2, c3, cs3, d3, ds3, e3, f3, fs3, g3, gs3, a3, as3, b3, c4, cs4, d4, ds4, e4, f4, fs4, g4, gs4, a4, as4, b4, c5, cs5, d5, ds5, e5, f5, fs5, g5, gs5, a5, as5, b5}; noteFlatsAll = {c2, df2, d2, ef2, e2, f2, gf2, g2, af2, a2, bf2, b2, c3, df3, d3, ef3, e3, f3, gf3, g3, af3, a3, bf3, b3, c4, df4, d4, ef4, e4, f4, gf4, g4, af4, a4, bf4, b4, c5, df5, d5, ef5, e5, f5, gf5, g5, af5, a5, bf5, b5}; noteSharps = {c3, cs3, d3, ds3, e3, f3, fs3, g3, gs3, a3, as3, b3, c4, cs4, d4, ds4, e4, f4, fs4, g4, gs4, a4, as4, b4}; noteFlats = {c3, df3, d3, ef3, e3, f3, gf3, g3, af3, a3, bf3, b3, c4, df4, d4, ef4, e4, f4, gf4, g4, af4, a4, bf4, b4};

Unlike the piano, the guitar’s interface (the fretboard) is partially redundant. The arrangement of the fretboard, and the intervals corresponding with stardard tuning, are deliberate. This in keeping with the player’s limitation of 4 fretting fingers (or 5, if the player’s thumb is draped over the top of the neck, à la Jimi Hendrix or John Mayer).

We denominate a 21-fret fretboard as “standard,” consistent with the specifications of a Fender Telecaster and many other guitars.

For the sake of consistency, we assume standard tuning in every case, but alternative tunings can be accommodated with minor code revisions.

Clear[Estring, astring, dstring, gstring, bstring, estring]; Estring = {"E2", "F2", "F#/Gb2", "G2", "G#/Ab2", "A2", "A#/Bb2", "B2", "C3", "C#/Db3", "D3", "D#/Eb3", "E3", "F3", "F#/Gb3", "G3", "G#/Ab3", "A3", "A#/Bb3", "B3", "C4", "C#/Db4", "D4"}; astring = {"A2", "A#/Bb2", "B2", "C3", "C#/Db3", "D3", "D#/Eb3", "E3", "F3", "F#/Gb3", "G3", "G#/Ab3", "A3", "A#/Bb3", "B3", "C4", "C#/Db4", "D4", "D#/Eb4", "E4", "F4", "F#/Gb4", "G4"}; dstring = {"D3", "D#/Eb3", "E3", "F3", "F#/Gb3", "G3", "G#/Ab3", "A3", "A#/Bb3", "B3", "C4", "C#/Db4", "D4", "D#/Eb4", "E4", "F4", "F#/Gb4", "G4", "G#/Ab4", "A4", "A#/Bb4", "B4", "C4"}; gstring = {"G3", "G#/Ab3", "A3", "A#/Bb3", "B3", "C4", "C#/Db4", "D4", "D#/Eb4", "E4", "F4", "F#/Gb4", "G4", "G#/Ab4", "A4", "A#/Bb4", "B4", "C5", "C#/Db5", "D5", "D#/Eb5", "E5", "F5"}; bstring = {"B3", "C4", "C#/Db4", "D4", "D#/Eb4", "E4", "F4", "F#/Gb4", "G4", "G#/Ab4", "A4", "A#/Bb4", "B4", "C5", "C#/Db5", "D5", "D#/Eb5", "E5", "F5", "F#/Gb5", "G5", "G#/Ab5", "A5"}; estring = {"E4", "F4", "F#/Gb4", "G4", "G#/Ab4", "A4", "A#/Bb4", "B4", "C5", "C#/Db5", "D5", "D#/Eb5", "E5", "F5", "F#/Gb5", "G5", "G#/Ab5", "A5", "A#/Bb5", "B5", "C6", "C#/Db6", "D6"};

For ease of display, we also set up a 12 fret (one octave) fretboard.

Clear[EstringO, astringO, dstringO, gstringO, bstringO, estringO]; EstringO = {"E2", "F2", "F#/Gb2", "G2", "G#/Ab2", "A2", "A#/Bb2", "B2", "C3", "C#/Db3", "D3", "D#/Eb3", "E3"}; astringO = {"A2", "A#/Bb2", "B2", "C3", "C#/Db3", "D3", "D#/Eb3", "E3", "F3", "F#/Gb3", "G3", "G#/Ab3", "A3"}; dstringO = {"D3", "D#/Eb3", "E3", "F3", "F#/Gb3", "G3", "G#/Ab3", "A3", "A#/Bb3", "B3", "C4", "C#/Db4", "D4"}; gstringO = {"G3", "G#/Ab3", "A3", "A#/Bb3", "B3", "C4", "C#/Db4", "D4", "D#/Eb4", "E4", "F4", "F#/Gb4", "G4"}; bstringO = {"B3", "C4", "C#/Db4", "D4", "D#/Eb4", "E4", "F4", "F#/Gb4", "G4", "G#/Ab4", "A4", "A#/Bb4", "B4"}; estringO = {"E4", "F4", "F#/Gb4", "G4", "G#/Ab4", "A4", "A#/Bb4", "B4", "C5", "C#/Db5", "D5", "D#/Eb5", "E5"};

We’ll create a number of different fretboard graphics corresponding with frequencies (Hz), named notes, MIDI tones and a piano keyboard analog.

Clear[freqGuitar]; freqGuitar[openfreq_] := NestList[#*(1 + 0.05946309) &, openfreq, 21];

Clear[openE, opena, opend, openg, openb, opene, Enotes, anotes, dnotes, gnotes, bnotes, enotes]; openE = 82.4068892; opena = 110; opend = 146.832384; openg = 195.997718; openb = 246.941651; opene = 329.627557; Enotes = freqGuitar[openE]; anotes = freqGuitar[opena]; dnotes = freqGuitar[opend]; gnotes = freqGuitar[openg]; bnotes = freqGuitar[openb]; enotes = freqGuitar[opene];

Clear[midiE, midiA, midiD, midiG, midiB, midie, midiStrings]; midiE = Range[40, 62, 1]; midiA = Range[45, 67, 1]; midiD = Range[50, 72, 1]; midiG = Range[55, 77, 1]; midiB = Range[59, 81, 1]; midie = Range[64, 86, 1]; midiStrings = Transpose[{midie, midiB, midiG, midiD, midiA, midiE}] // Transpose;

Guitar Fretboard with Named Notes

The code below renders a standard fretboard with corresponding named notes.

Clear[strings3, fboard]; strings3 = Transpose[{estring, bstring, gstring, dstring, astring, Estring}] // Transpose; fboard = Grid[strings3, Background -> {{1 -> Green, 4 -> LightGreen, 6 -> LightGreen, 8 -> LightGreen, 11 -> LightGreen, 13 -> Green, 16 -> LightGreen, 18 -> LightGreen, 20 -> LightGreen, 23 -> LightGreen}, None}, Dividers -> {2 -> {RGBColor[0.8, 0.8, 0.8], Thickness[3]}}, Frame -> {All, None}, Alignment -> Center, ItemSize -> {5, 1}, BaseStyle -> {FontFamily -> "Verdana", FontSize -> 12}, ItemStyle -> {{Bold}, Automatic}]

Fretboard Notes

Guitar Fretboard with Frequencies (Hz)

The code below renders a standard fretboard with corresponding frequencies.

Clear[strings4, fboard]; strings4 = Transpose[{enotes, bnotes, gnotes, dnotes, anotes, Enotes}] // Transpose; fboard = Grid[strings4, Background -> {{1 -> Green, 4 -> LightGreen, 6 -> LightGreen, 8 -> LightGreen, 11 -> LightGreen, 13 -> Green, 16 -> LightGreen, 18 -> LightGreen, 20 -> LightGreen, 23 -> LightGreen}, None}, Dividers -> {2 -> {RGBColor[0, 0.5, 0], Thickness[3]}}, Frame -> {All, None}, Alignment -> Center, ItemSize -> {6, 1}, BaseStyle -> {FontFamily -> "Verdana", FontSize -> 12}, ItemStyle -> {{Bold}, Automatic}]

Fretboard Frequencies

Piano Analog Guitar Fretboard

The code below renders a piano analog fretboard in 12 frets.

Clear[rule, fBoardP]; rule = {}; Do[ If[StringMatchQ[stringsO[[i, j]], "*/*"] == True, AppendTo[rule, {i, j} -> Black]], {i, 1, 6}, {j, 1, 13}] fBoardP = Grid[stringsO, Dividers -> {2 -> {RGBColor[0.8, 0.8, 0.8], Thickness[3]}}, Alignment -> {Center, Center}, ItemSize -> {5, 1}, BaseStyle -> {FontFamily -> "Verdana", FontColor -> Red, FontSize -> 12}, ItemStyle -> {{Bold}, Automatic}, Frame -> {All, All}, FrameStyle -> Black, Background -> {Automatic, Automatic, rule}]

Piano Analog Fretboard

Guitar Fretboard with MIDI Tones

The code below renders a standard fretboard with corresponding MIDI tones.

Clear[fboardMidi]; fboardMidi = Grid[midiStrings, Background -> {{1 -> Green, 4 -> LightGreen, 6 -> LightGreen, 8 -> LightGreen, 11 -> LightGreen, 13 -> Green, 16 -> LightGreen, 18 -> LightGreen, 20 -> LightGreen, 23 -> LightGreen}, None}, Dividers -> {2 -> {RGBColor[0.8, 0.8, 0.8], Thickness[3]}}, Frame -> {All, All}, Alignment -> {Center, Center}, ItemSize -> {5, 1}, BaseStyle -> {FontFamily -> "Verdana", FontSize -> 12}, ItemStyle -> {{Bold}, Automatic}]

MIDI Fretboard

In Part II, I’ll show you how to render a realistic-looking interactive fretboard that produces tones of the corresponding frequency.

Posted in Mathematica, Music | Leave a comment

Real Options for Entrepreneurs and Small Business Owners

In this post, we’ll discuss a topic that’s strongly emphasized in all MBA programs – real options.

Real options receive heavy emphasis in business school for at least two reasons. First and most obviously, it’s strategically important that managers be able to assign a value to flexibility. In any business context, an option gives the option holder the right, but not the obligation, to take a certain action (termed exercise).

Naturally, choice and flexibility are most significant when the stakes are high. If the price of poker is $10 million, for example, the freedom to invest some or all of that amount over time is vastly preferable to a single Yes/No, now-or-never window of opportunity.

And therein lies the importance of real options.

Of course, real options are fundamental to quintessential high stakes enterprises such as petroleum exploration and big pharma. But opportunities to invest a significant portion of the company’s net worth are apt to come up in almost any business model. When these investments lend themselves to staged commitments, the discrete opportunities present a series of decision points (called levers). The process of strategic decision-making includes assigning the appropriate value to each lever.

Another reason why business schools emphasize real options is related to the option value equation. Naturally, business schools teach students how to value options (also known as derivatives) on publicly traded stock. As it turns out, options are options to a great extent, and the same basic equation that’s used to calculate the value of a vanilla call option on AAPL can be used to quantify the value of an option to purchase a competitor’s factory.

The Black-Scholes-Merton Equation

Throughout this discussion, we’ll focus on one particular method of valuing options: the Black-Scholes equation, as modified by Robert Merton (BSM). The BSM equation has enjoyed wide popularity ever since its creation for at least three reasons. First, it’s simple to code in any computer language. Second, it’s a closed-form representation. And third, provided the user understands and remains cognizant of BSM’s inherent limitations (especially in the financial options context), it mostly works.

Since this is an article written primarily for non-MBA entrepreneurs and business owners, I’ll forgo a discussion of the math that underlies BSM. Interested readers can find a good exposition in a paper that’s freely downloadable here.

In the world of financial options there are a number of other algorithms and methods that quantitative analysts use to calculate option value. It should come as no surprise that one can purchase an option to buy or sell publicly traded stock on almost any terms imaginable. For example, Mathematica‘s FinancialDerivative[] function includes several dozen option types that are built-in:


mma_builtin_options

Those financial options that include some combination of unorthodox payoff structures, valuation criteria, term length and/or exercise provisions are called exotic. Generally speaking, the more exotic the option, the less useful BSM is in valuing it. Most exotic options are valued using complex numerical calculation methods.

Part of BSM’s appealing simplicity is its use of only six variables. Within the VBA module below, I’ve added a seventh variable to capture whether you’re pricing a call or put option. For our purposes in this article, an option to acquire would be considered a call option, while an option to divest would be deemed a put option.

In the table that follows, I’ve set out each of the six along with the value that each parameter represents in the context of real options, and then financial options.


Parameter Real Option Financial Option
S Present value of future cash flows Current stock price
X Present value of future fixed costs Option strike (exercise) price
sigma Uncertainty of future cash flows Volatility of underlying stock
t Option life from today to expiry (years) Same as real option
delta Diminution of option value over time Dividend rate of underlying stock
r Risk-free interest rate Same as real option

These parameters are largely self-descriptive, but I’ll expound briefly upon each from the standpoint of real options. I’ll also explain how each parameter affects overall option value. In the interest of brevity, I’ll assume in each instance that we’re considering a call option. Just be aware that an increase in the value of any parameter would have the opposite effect on a put option.

S represents the expected net present value (NPV) of future cash flows from the investment opportunity. We’ve discussed NPV at various times in previous articles, but the easiest way to understand NPV is to actually calculate it. Here’s a spreadsheet I’ve written that calculates expected NPV (note that the workbook uses macros). An increase in S increases real option value.

X is similar to S, except that you’re aggregating the fixed costs associated with the investment instead of expected cash flow. As long as you start from scratch and substitute fixed costs for revenue, you can calculate X with the same spreadsheet you used to calculate S. An increase in X decreases real option value.

In many ways the parameter sigma is the key to BSM valuation. We use sigma to specify the volatility (or uncertainty) that you attach to your model. Somewhat counter-intuitively, the greater the uncertainty, the more an option is worth. Yet it’s uncertainty that makes flexibility so valuable. In the course of valuing your real options, I encourage you to play with different values for sigma and take note of its significance and influence. As noted, an increase in sigma increases real option value.

For purposes of real option valuation, t is ordinarily the time interval between today, and the final drop-dead date for exercising a real option. An increase in t increases real option value. You always want to express t in terms of years, so you would divide n number of days by 365 and use that value. You are of course free to specify some value for t other than the day on which the whole thing turns into a pumpkin. If you’d like to value only a certain lever in terms of real options, you can specify instead the deadline for that discrete investment (or other action). Just make sure that your other values are consistent with the same interim period.

The parameter delta is Merton’s contribution to the basic Black-Scholes equation, and it was originally designed to account for options to buy or sell stocks that pay a shareholder dividend. Not all stocks pay dividends, of course, so in many cases the value of delta is zero. The same is true in a real options context. With respect to real options, delta stands for all of those factors that chip away at the value of a real option, and which come about as a function of time. For example, perhaps you have an option to buy a depreciating asset. Or perhaps it will require interim investment on your part to either keep the option open or have it remain a viable strategy. Any tangential detractor of option value should be included within your assigned value for delta. An increase in delta decreases real option value.

The last parameter is r, or the risk-free interest rate. An increase in r increases real option value. Unlike our use of r in previous articles to cover a wider and more eclectic series of factors, r with respect to real options is intended to represent only the (risk-free) time value of money. As we’ve noted in earlier posts, the usual benchmark for r is the interest rate on 3 month U.S. T-Bonds, which has remained below one percent for many consecutive months. You’re free to prognosticate with respect to r; just remember that you’ll skew the calculation if you allow factors other than the expected risk-free rate to affect your estimate.

Black-Scholes-Merton VBA Code

As noted above, the BSM equation is remarkably easy to code. To use the VBA function below in Excel for Mac 2011, simply click the Developer tab, then the Editor button, then select Insert > Module and then copy-and-paste. Don’t forget to save your workbook in .xlsm format so that the VBA module is saved.


Public Function realOption(S As Double, X As Double, sigma As Double, _ t As Double, delta As Double, r As Double, cp As Long) As Double ' Uses Black-Scholes-Merton to calculate ' real option value ' Enter cp = 1 for Call option or cp = 2 for Put option Dim d1 As Double, d2 As Double With Application.WorksheetFunction d1 = (Log(S/X) + (r-delta+((sigma^2)/2) * t)) / (sigma * Sqr(t)) d2 = d1 - (sigma * Sqr(t)) If cp = 2 Then realOption = X * Exp(-r*t) * .NORMSDIST(-d2) - S * Exp(-delta*t) * _ .NORMSDIST(-d1) Else realOption = S * Exp(-delta*t) * .NORMSDIST(d1) - X * Exp(-r*t) * _ .NORMSDIST(d2) End If End With End Function

Here’s a graphic showing the setup in Excel:

bsm_setup3
Posted in Business, Excel | Leave a comment

Here’s an Annotated Business Plan Outline

In what follows, I’ll set forth an outline of a business plan – with annotations.

Except where self-evident, I’ll state the implicit question(s) that each section of your Business Plan needs to answer. Then below each section heading, in most cases I’ll provide a little more context and granularity for that topic.

You’ll note that I often use the term pain as synonymous with demand. I do this deliberately to emphasize the urgency that you must bring to your venture. Not only must you identify something that people urgently want, you must also convey your urgent dedication to providing it. Remember that potential investors will expect to see obvious signs of your passionate commitment to the enterprise at each step of the process.

As a business owner seeking outside investors, bear in mind that your audience is apt to be time-constrained and impatient. Keep things as simple as possible, and make every word count.

I. Executive Summary

Why should anyone take the time to read this whole thing?

Your Executive Summary is more than a written elevator pitch, but you should always limit it to one page, regardless of how complex your product or business model might be. The Executive Summary should:

  1. Capture the reader’s interest; and
  2. Convince them that the time it takes to read your business plan will be time well spent.

The sobering fact is that if you don’t accomplish these two things, the Executive Summary will probably be the only part of your Business Plan that’s ever read by anyone. Rewrite your Executive Summary as many times as it takes until you’re sure that it’s sufficiently compelling.

Here’s a very good article describing how to write an attention-getting Executive Summary.

II. Target Market

The Pain. What clearly established customer need (or better still, want) does your product or service address?

A. Market Demand

What specifically is the pain, and what’s causing it?

These aren’t always obvious, but it’s always crucial that you isolate and identify them. Not surprisingly, it’s tough to line up investors for a product if you can’t explain why people will need or want it.

B. Market Size

What is the scope and extent of the pain?

In terms of geography and demographics, as well as perceived magnitude.

C. Market Potential

How does II(A) together with II(B) constitute an unserved or underserved market?

Without knowingly exaggerating, say a few words about potential market scale. No one expects you to be clairvoyant, but at the same time whatever you say here has to pass the giggle test. If you can’t legitimately convince yourself that this could be a $100 million market in the near future, you won’t convince a potential investor.

III. Your Product or Service

What is this thing you’re selling, and why would anyone want to buy it?

A. General Description

What is your product or service, and what does it do?

This is critical: no matter how technologically sophisticated your product or service, you must explain it here in words and concepts that a senior in high school could understand. Feel free to make use of common analogies if they’re accurate and helpful.

As with the Executive Summary, you should be willing to rewrite this section as many times as necessary so that you cover the essentials in as few words as possible. Try to use simple, declarative sentences.

B. Market Fulfillment

How does your product or service alleviate the pain?

Make sure that the connection between your product or service and relieving the customer’s pain is crystal clear.

This step is also critical. You want your target market to make this connection without having to think about it.

C. Competition

Why can’t existing products or services alleviate the pain just as well?

Keep it general. We’ll identify your specific competitors in a later section. This is about distinguishing your product or service from the rest of the competitive landscape.

Marketing studies have long-since established that consumers will rarely switch just for the sake of change. Unless your product or service represents some tangible and obvious improvement over the status quo, inertia will prevail.

D. Business Process Generally

What steps are involved in producing and delivering your product or service?

Keep it high-level and succinct here. You’ll get into the details in a later section.

IV. Company Organization and Structure

How does your enterprise enable and support your product or service?

A. General Information

Who is your company, and what are its vital statistics?

This section should contain what Hoovers or Bloomberg would include on page 1 of their report about your company.

B. Company Mission

What is your company’s overall purpose?

A well composed mission statement is a positive signal that you’ve thought carefully about your idea and model in the greater context. Don’t make the mistake of thinking that all mission statements are corny and passé.

C. Company Function

What specifically will your company do, and what is your company’s unique expertise or capability?

This is the section where you should describe the in-house processes and functions that go into making (and delivering, if applicable) your product or service. You’ll go through the outsourced functions in the next section.

Keep in mind that your firm needs some uncommon (or better yet, unique) value-added capability that sets you apart.

V. Channels

How will others help you make and deliver your product or service?

A. Materials, Manufacturing and Finished Goods

i. Outsource Providers

Given Section IV, what steps from III(D) will you outsource, and why?

For anything other than back-office functions, make sure it’s clear how your outsourced tasks mesh with the functions you keep in-house. The main idea here is to demonstrate to the reader that not only do you know what your business model is, you also know what it isn’t.

ii. Suppliers and Distributors

a. Primary

Who are your primary suppliers and distributors, what are their respective roles, and why did you choose them?

Choose carefully. Ask for references and check them.

There’s no reason to get creative here. In most cases, you should start at the bottom of the supply chain and proceed upward through manufacturing to distribution and delivery.

b. Backup

In addition to all of the primary supplier questions, how will resorting to one or more of your backup suppliers/distributors affect your ongoing operation?

Just as crucial as choosing your primary suppliers and distributors. In making your selections, the goal is to permit as seamless a transition as possible from primary to backup. This means you’re looking for (at a minimum) similar direct costs and similar logistics. You need at least one pre-arranged and locked-down backup for every primary supplier or distributor.

B. Marketing and Advertising

i. Target Market

Given your answers to II(B) and II(C), what is your initial target niche?

Depending on the nature of the pain and its scope, your market may already be somewhat narrow. There’s no need to further segment the market if you can make a coherent, consistent marketing pitch across the entire spectrum.

ii. Target Market Parameters

a. Price Elasticity

How will the market react if you raise or lower your price?

Since even monopolies can price themselves out of a market, price elasticity of demand is a critical factor in the success of your business.

If there are existing products or services that are truly analogous, you may be able to extrapolate from those data, at least in the beginning. Check at Forrester for comparable data. If there are no sufficiently analogous products or services, you’ll need to obtain your own market research.

Solely to fix concepts, I’ve written an Excel worksheet that you can download. It sets out a much-simplified calculation of price elasticity and optimal price. Note, however, that this worksheet is far too general to use in conjunction with a Business Plan, and under no circumstances should it be used by anyone for that purpose or any other besides simple illustration.

b. Advertising Elasticity

How will advertising affect your sales?

To what extent is advertising expected to increase demand, and what’s the “sweet spot” in terms of advertising expenditure? This is another question that may require market research to answer.

c. Initial Pricing Structure

To what extent is your sales pitch based on price?

Relative to the competition. This is intended to be a high-level discussion related to whether you intend to price upmarket (almost always) and compete on the basis of added value and service, or downmarket and rely on lower price than the competition. You should also specify upmarket or downmarket in relation to which of your competitors.

iii. Excluded Markets (If Applicable)

What other potential market niches are likely to react negatively to your marketing plan?

In other words, what potential market segments are you prepared to sacrifice (if any) to ensure that you catch the attention of your target market? This may not be applicable, but don’t kid yourself. The more creative and unorthodox your marketing approach, the more likely that it will be met with differing reactions – some of them negative.

For example, despite the mass appeal and wide popularity of GEICO’s television commercials, some people find them irritatingly lowbrow and intelligence-insulting.

VI. Competition

Who else is able to address the same customer need or want?

Based on your answers to III(A) and III(B), who else is positioned to address the same pain?

A. Direct Competitors

Conclusively determining whether another firm’s product or service represents direct or indirect competition very often requires a studied analysis. As an initial rough-cut distinction, however, to the extent you believe price will be the main driver of consumer choice between your product, X, and a different product Y, that’s a good indicator of direct competition.

B. Indirect Competitors

This presents a golden opportunity to differentiate your business plan. Even among established firms, most have never bothered to identify all of their indirect competition. The main consequence of this error is its tendency to leave the business flat-footed and tunnel-visioned in the face of change. When market dynamics shift and cause these unacknowledged indirect competitors to begin competing with the firm more directly, the firm may be poorly situated to deal with the new threat.

To the extent it’s clear that you have creatively and exhaustively analyzed your indirect competition, this will greatly impress a potential investor.

C. Competitive Advantages

Why does your product or service offer greater value than any competing product or service?

Apart from transactional factors such as price and delivery, what intrinsic qualities of your product or service give you a true competitive advantage?

This is the section where you would describe any proprietary advantage you enjoy, and whether it enjoys legal protection through patent, trademark, etc., or is treated as a trade secret. If your competitive advantage is highly technical, you might think about creating an appendix for it and then referencing the appendix here.

Naturally, you should always consult with legal counsel and ensure that the appropriate non-disclosure covenants have been executed before divulging any proprietary information to a potential investor.

D. Positional Advantages

What makes your company especially well-suited to bring your product or service to market?

This is a subtle but important point. If what you have is essentially just a great product or service idea, you should think seriously about pitching your idea to an established company rather than starting a business.

VII. Projected Financials

How do your plans for producing and selling your product or service add up to profit?

Many start-up business plans contain elaborate balance sheet, income statement, and cash flow statement projections for the next five years. These reports all invariably show the same hockey stick curves for going-forward revenue and income. To the entrepreneurs’ surprise, potential investors rarely take them seriously.

Provided your business is still in the pre-revenue stage, you can dispense with the exercise of ginning up numbers to create a formal set of going-forward financials. Instead, the financial data your potential investors will be most interested in are:

  • Your pricing structure and the basis for it
  • Your revenue and cost projections along with the basis for them; and
  • Your detailed plans for achieving scale quickly

A. Projected Sales

With relevant assumptions.

B. Projected Costs

With relevant assumptions.

i. Direct Costs

This section has to be rock-solid in terms of both mathematical accuracy and inclusiveness.

ii. Indirect Costs

You’ll come off as naïve (at best) if you miss an obvious driver of indirect cost.

C. Pricing Structure

How did you choose the number that’s on the price tag?

With supporting data and relevant assumptions. In contrast with the high-level strategic discussion in a previous section, this is where you will identify the specific considerations that inform your pricing.

Here, you’ll want to rely heavily on price elasticity data. Unless you have some basis for determining the market’s price sensitivity, you won’t know how to price your product without resorting to trial-and-error. Few investors are willing to fund a trial-and-error process.

D. Timetable to Scale

How long do you expect it to take for an investor to get his money back, and what is his expected ROI?

With supporting data and relevant assumptions. A Gantt chart works well here.

E. Financing Requirements

How will you spend the capital that’s invested in your business?

You’ll probably include some of this within your Gantt chart, but it’s a good idea to state it again in stand-alone format. Make it clear to the reader how, where, and when you intend to commit any capital that you obtain.

VIII. Management Team

How does the composition of your management team ensure the success of this venture?

Make sure this section clearly and unambiguously sets out the experience and qualifications of your management team. Ideally, investors want the experience of your team to be such that this particular start-up venture represents little more than a sequel. Always put your team members’ related experience and pertinent expertise front and center.

Also, look for obvious holes in your team’s combined skill set and experience. If the team is lacking in some important area, you should think seriously about recruiting another team member with those capabilities before you seek investors.

Posted in Business, Excel, Finance & Investing | Leave a comment

Linear Programming in Excel 2011 for Mac

A Quick Intro to Linear Programming

Linear programming (LP) is a specialized form of matrix algebra that we can use to solve constrained multi-variable optimization scenarios. LP is part of a larger discipline known as Operations Research, which uses advanced mathematics to inform and optimize decision making.

The discussion and example herein are intended for intermediate-level Excel users who have little or no experience with using Solver to calculate LP problems.

Mathematically, we characterize LP as an equation of the form:

max cTx
s.t. Axb

As you may recall, for n = length, bold lower-case letters represent a 1 × n array, and bold upper-case letters represent an n × n matrix. The superscript capital T denotes a transpose; for example, turning a 1 × n array 90 degrees to form its n × 1 representation.

Also be aware that the term, matrix, is actually a generic expression that can apply as well to an n × n matrix, a 1 × n array, or a single-cell scalar. To avoid confusion, in addition to matrix, we’ll use the more specific terms array and scalar where applicable.

In the LP model above, the expression cTx is called the objective function. It signifies a matrix multiplication of the function to be minimized (or maximized), cT, by the decision variables, x. Decision variables are those elements of an LP problem that are discretionary; in other words, controllable by the decision maker.

Actually, LP is just a way of optimizing the objective function subject to the constraints A and b. And along the way, Solver adjusts the values of the decision variables, x, as necessary to produce the optimal outcome. It does this by ranking possible solutions according to the value of cTx they generate.

The variable A is a set of linear inequality constraints. We use A to limit the decision variables in x to being greater than or less than the corresponding values set forth in b. In this way, the relationship between c and x is defined and enforced.

Fortunately, the built-in version of Solver will allow us to calculate LP solutions for most simple (and even some moderately complex) LP scenarios. In setting up the calculation for Solver, we’ll also be using the built-in Excel functions MMULT and/or SUMPRODUCT to do our matrix arithmetic.

As you formulate your LP arguments, you’ll want to keep in mind the following:

  1. MMULT and SUMPRODUCT require at least two ranges separated by commas. For purposes of this article, we’ll assume exactly two ranges throughout.
  2. If the result of an operation will span more than one cell, you’ll need to set it up as an array formula. In Excel for Mac, you evaluate an array formula by pressing command-shift-return.
  3. SUMPRODUCT will return an error unless the dimensions of the ranges are exactly alike. In other words, SUMPRODUCT will not operate on a 1 × n array and an n × 1 array.
  4. MMULT will return an error unless the number of columns in the first range is equal to the number of rows in the second range. That means that if you want to use MMULT on the same two expressions that you successfully referenced in SUMPRODUCT, you’ll have to use the TRANSPOSE function on one or the other, like so:
=SUMPRODUCT(array1, array2) =MMULT(TRANSPOSE(array1), array2)

Note: in this article, we’ll forgo a review of matrix arithmetic/matrix algebra theory and the various mathematical operations that can be performed on arrays and matrices. Interested readers can find a good one-page review of the topic here. For the truly interested, all the materials for an undergraduate MIT course in managerial LP are available free online.

Formulating an Employee Work Schedule

In terms of practical application, a simple LP example might involve optimizing the schedule of a company’s workforce to minimize labor cost. Assume a scheduled airline operates seven days a week at a particular airport. The station’s daily labor demands (in terms of hours worked) for ground crew employees are as follows:

hours_reqd

We’ll assume the collective bargaining agreement in place specifies the following:

  • All employees are paid $20 per hour for straight time worked
  • The company operates only one 8-hour shift per day
  • Management is prohibited from hiring part-time employees
  • The scope clause permits management to assign any employee to perform any necessary task
  • Employees must receive two consecutive days off per week

To keep things simple, we will disregard seniority, bidding and the relative desirability of certain days off. We will also disallow overtime.

Based on the demand for labor in terms of hours, we can calculate the minimum number of employees required to work on a given day with the following argument:

=CEILING(D3/8, 1)

This argument divides the number of hours required by 8 hours per employee, and if there is a remainder, the CEILING function returns the next integer value greater than the fractional result.

One way to solve the scheduling problem is to create a tableau of employee schedules (see below). Note that we treat the tableau values as binary: 1 for days worked, and 0 for days off. Note also that the tableau accounts for every possible combination of consecutive days off.

tableau1

Next, we need an array of values representing the number of employees who are starting their 5-day work week on a particular day. We can specify any integer value here, since these are the values that Solver will change to optimize our solution. For the sake of simplicity, I’ve specified 20 employees for each day.

decision_variables

Next, we’ll create an array that sets out the number of employees who are scheduled to work on a particular day.

emp_avail3

We’ll generate these values from a matrix multiplication of the binary (1, 0) values for each day in the table, and the entire array of employees who start work on each week day. Assume the tableau of days worked and days off is located at D11:J17, and the array of employees who start their work week on a particular day is located in L11:L17. The argument for available employees on Sunday is:

=MMULT(D11:J11,$L$11:$L$17)

Note that I’ve included the dollar-sign designation in the second array because this range will be the same for all seven calculations.

After you’ve calculated the available employees for each day, it’s helpful to go ahead and recap the values for number of employees required just below. You’ll also want to designate a cell (L20) that captures the total number of employees, SUM(L11:L17).

emp_reqd2

We’ll create a cell (D23) to hold the value corresponding with the hourly rate of pay, and another cell (D24) to hold our objective function.

obj_fn_revd2

Our objective function in this case is the airline’s weekly payroll for ground crew:

=D23*8*5*L20

Or, hourly pay rate, times eight hours per day, times five days per week, times total employees.

Once you’ve entered all of the above, we’ll save time by naming the range L11:L17 as “Sked_Emp,” the range D20:J20 as “Emp_Avail,” and the range D21:J21 as “Emp_Reqd.” The best way to name a range of cells is to select all of the cells in question, and then enter the (unique) name in the window to the left of the argument window:

named_range2

We’re almost ready to calculate. Click on the Data tab and then on the Solver icon. When the Solver pane pops up, configure the LP problem like so:

solver_pane2

There’s one final thing to check. Click on the Options button next to the Solving Method combo box, and make sure the box marked “Ignore Integer Constraints” is unchecked.

Now click the Solve button, and Excel should return the following changed values:

changed_cells_revd
Posted in Built-In Functions, Business, Excel | Leave a comment

Optimization in Excel for Mac 2011 Using Solver

In what could be considered Part 1 of this article, we set up a VBA environment that allows us to run Monte Carlo simulations in Excel for Mac. Here, we’ll take the analysis one step further and use the Solver addin to find specific target values.

If you’ve purchased MS Office for Mac 2011 recently, then the Solver addin was installed automatically along with Excel. To activate it, go into Excel and click on Tools > Addins. Check the box next to Solver, and you’re good to go. If you purchased an early version of Office for Mac 2011, you may need to download and install Solver.

As with Excel for Windows, Solver also offers a premium version of its addin for Mac. I have no experience with the premium version, and I have no basis by which to compare it to, e.g., Matlab, for complex, non-linear problems. As you can see from the webpage I’ve linked, it’s one of those deals where you have to email Solver just to find out how much the premium version costs.

Parenthetically, the fact that Solver makes an addin for the Mac version of Excel means that addins are technically doable on the Mac side. Indeed, there are a few other third-party addins for the Mac version of Office as described here. Nevertheless, it’s unclear whether or to what extent it’s feasible for independent developers and hobbyists to create Mac-version addins.

Fortunately, the basic version of Solver will accomplish everything we’ll want to do today (and considerably more).

The Solver Window

To use Solver, click on the Data tab and then on the Solver button. The Solver window looks like this:

Solver Window

In order for Solver to work, you have to specify one particular cell (the “Objective”) that contains a formula rather than a static value. After that, Solver will begin changing the value(s) of whatever variable(s) you specify to either maximize or minimize the value of the Objective cell, or to make the Objective cell equal a certain value.

You can also limit Solver’s calculation by imposing constraints on the value of one or more variables. We’ll discuss this in more detail below.

Some of the items in the Solver window are self-explanatory, and some items are anything but. To understand more fully requires us to know something about the difference between linear and non-linear functions.

Linear vs. Non-linear Functions

Of course, the quickest way to determine whether a function is linear or non-linear is to assign numbers to all of the variables and plot it. When you do, most non-linear functions will show up as a clearly discernible curve.

But not always. Recall from Part I of this article that the plot of our Monte Carlo simulation looked like this:

ROI Graph

As you can see, the points all appear to lie on a straight line. Nevertheless, our Revenue, Income and ROI functions are all non-linear. Here’s how we know.

A function is linear when its rate of change is constant. To illustrate, let’s create a table. Using the same worksheet we created in Part 1, enter 3500 in cell J2, and then in J3 enter the following:

=J2+100

Now drag down to cell J12. You’ll see that we’ve created a list of billable hours that increases by a constant value, 100. In this problem we only simulated one variable. Consequently, if we change the billable hours variable by a constant amount, we can measure whether the change in value for the Revenue function is also constant.

Note that had we simulated more than one variable, to test for non-linearity we’d have to make the rate of change constant for those other variables as well.

Next, Copy cell F2 and Paste Special > Formulas into cell K2. Change E2 in the argument to J2, and then drag down even with column J.

Recall that the formula for change is (new – old)/old. In cell L3, enter the following:

=(K3-K2)/K2

and drag down to L12. You can now see from column L that the change in Revenue from one observation to the next is small, but definitely not constant.

Beginning in column M, repeat this process for Income and ROI. When you finish, you’ll have a table that looks like this:

Rates of Change

As you can see, none of our functions are linear, regardless of whether they appear to be straight when we graph them. Also note that all of the functions are decreasing in relation to Billable Hours, some faster than others.

The Minimum and Maximum of a Function

As we discussed above, Solver is designed to minimize or maximize the value of whatever function you specify. Generally speaking, however, only parabolic functions will have an intrinsic minimum or maximum value. For example, if we look at the plot of a convex parabolic function:

Convex Quadratic Function

it’s easy to see where the minimum value would be located. In the same way, if we look at the plot of a concave parabolic function:

Concave Quadratic Function

we can readily point out where the maximum value is located. The distinctive feature of a parabolic function is that the change in value is negative in some places on the curve and positive in others. As you may have guessed, the maximum or minimum of a parabolic function is the place on the curve where change, δ, is equal to zero.

Yet in many cases, the shape of the curve is more subtle. In our headcount analysis curve, change is strictly positive all along the curve – at least for the part we’ve plotted – and nowhere does δ = 0. This means we won’t be able to find a maximum or minimum value for the function unless we impose arbitrary constraints on the Billable Hours variable.

Obviously, we know there’s an absolute constraint on the number of hours an associate can bill that’s represented by 24 × 365 = 8760 hours in a year (insert appropriate lawyer joke here). We also know that there’s some practical limit on the number of billable hours an associate will generate that caps off somewhere around 2400 hours per year (except in Manhattan).

At the same time, we know there’s a practical constraint in the other direction that represents the minimum number of hours an associate must bill in order to keep from being fired.

So in reality, our headcount profitability function is already limited to a particular range. All we need to do is specify for Solver the minimum and maximum values for Billable Hours that we want it to consider.

Setup

Before we return to the Solver window, we need to set up worksheet cells that contain the functions we want Solver to work on.

Go to cell F2 and Copy, then Paste Special > Formulas in cell B10. Change E2 in the argument to B7. Then copy and paste the formula from cell G2 to cell B11, changing the reference from F2 to B10. Last, copy the formula from cell H2 to cell B12, changing the reference from G2 to B11.

Optimization Setup

Since Revenue is the only function that is directly dependent on Billable Hours, it’s the Revenue function that we’ll maximize.

In the Set Objective box, make sure it reads $B$10. Since we’re seeking the maximum, click the radio button labeled Max if it isn’t already selected. Then in the box labeled By Changing Variable Cells, you can either use the locator button to the right to select cell B7, or manually enter $B$7.

To include constraints, click the Add button. In the window that appears, designate that cell $B$7 should be <= 4800 (or whatever maximum value you prefer). It should come as no surprise that maximum Revenue will correspond with the maximum number of billable hours you enter.

Solver Constraints

Then click OK. Next, click Add again, and specify that cell $B$7 should be >= 3600 (or whatever minimum number of hours you want to specify). Click OK. You can ignore the box labeled Make Unconstrained Variables Non-Negative, because we don’t have any unconstrained variables in this example. Our Revenue function is non-linear, so for the solving method choose GRG Nonlinear, which should already be selected as the default.

One final thing – if your argument for Overhead in cell B8 contains a reference to B7, then replace B7 with the number 4000.

When you’ve completed all that, click Solve.

If all goes well, Solver will return a window that looks like this:

Solver Finish

Before you decide whether to keep the solution that Solver found, first look over at the changed variables and make sure that the value(s) is/are within the constraints you specified. Then look over at the Objective cell and make a determination as to whether the result is plausible. If both check out, then you can click OK. If not, it’s usually best to select the radio button labelled Restore Original Values before clicking OK.

You should now see the values below in cells B10:B12

Optimization Result

Next, to find the number of billable hours that correspond with a particular ROI, first bring back up the Solver window. Change the Objective cell to $B$12. Let’s assume that we want to know the number of billable hours that corresponds with a twelve percent ROI. Start by clicking the radio button labeled Value Of and enter 0.12 in the box to the right. Leave everything else unchanged, and click Solve.

You should see that Solver determined the number of billable hours corresponding with a 12% ROI is 3983.75.

In the same way, if you want to calculate the number of billable hours that corresponds with break-even, leave $B$12 as the Objective cell, and enter 0 in the box to the right. Then click Solve.

Posted in Business, Excel | Leave a comment

Relational Database Functionality in MS Excel for Mac 2011

The Most Significant and Powerful Excel for Mac Workaround I’ve Yet Encountered

Think of MS Office for Windows as a professional auto mechanic’s toolbox. You know what I mean – a big red locking tool box with dozens of sliding drawers. For any kind of auto repair, it has everything you’ll need, in metric and standard, Phillips-head, slotted, and hex. In fact, there are tools in there that 99 out of 100 people wouldn’t even recognize – let alone know how to use.

By that same analogy, you can think of MS Office 2011 for Mac as a shade-tree mechanic’s toolbox. There’s a lot of useful stuff inside, and if all you ever do is change spark plugs and oil, it’s more than enough. And in all fairness, for any repair more complex than that most people just take their car to a professional mechanic, anyway.

But if for whatever reason you find yourself needing or wanting to overhaul a transmission or rebuild an engine, your task will be much harder. You’ll discover that your shade-tree mechanic’s toolbox is missing a number of things that, at a minimum, would greatly facilitate matters.

Now, if playing video games has taught me anything, it’s this: there’s always a way. The real issue is whether you have enough time and patience to deal with finding and then implementing the necessary workaround. Naturally, some workarounds are less optimal than others.

Then once in a blue moon, someone actually figures out how to use a ball-peen hammer as a torque wrench. Less frequently still, that person is willing to share their discovery without charging anything for it.

It says here – without exaggeration – that this set of instructions is that same kind of monumental discovery in the context of Excel for Mac.

Relative to the Windows version of MS Office, the most significant omission in MS Office for Mac 2011 (apart from ActiveX) is MS Access. Without question, Excel is an outstanding application, but its real power only becomes evident when Excel is coupled with a database, for it’s then that real quantitative “heavy lifting” can take place.

You can think of this article as a foreword to Jim Gordon’s excellent set of materials linked above.

Office for Mac Preliminaries and Setup

Naturally, I’m taking it as a given that you’ve already purchased MS Office 2011 for Mac. I use the Home & Business version, but to my knowledge everything I’ll discuss and refer to herein is equally applicable to the Home & Student version.

To the extent it’s material, I’m running Maverick OS (OS X version 10.9), but unless you’re still using one of the very early versions of OS X, that shouldn’t matter, either.

Before you can begin to follow along with the articles linked above, you’ll first need to download and install ODBC Manager. This (free) application came pre-installed in the very early versions of OS X, but was disincluded by Apple several versions ago. Once you’ve downloaded the application, make sure it’s installed in your Applications/Utilities folder.

You’ll also need a set of database drivers. I’m not aware of any freeware drivers, but fortunately they’re modestly priced. I use the drivers from Actual Technologies, available here. They come in 4 flavors: (a) SQL and Sybase; (b) Oracle; (c) Open Source, including MySQL, PostgreSQL and SQLite; and (d) MS Access. At this writing, each of the four costs the same: $34.95, or $139.80 for all four. The AT website also has an easy-to-follow set of instructions for installing the drivers in your ODBC Manager application.

You can freely test these AT drivers before purchasing a license, but the limitation is that database query results will be limited to three rows.

Once you have your database drivers installed, Gordon’s article will begin walking you through the process of interfacing Excel with ODBC Manager.

Posted in Excel | Leave a comment

Monte Carlo Simulation in Excel for Mac 2011

Monte Carlo simulation isn’t something that Excel for Mac does naturally, but as I’ll demonstrate in this post, it’s certainly doable. Using a series of VBA modules, if we take the time to set it all up once, you can use the Excel workbook we’ll create as a template for future simulation projects.

Assume you’re the managing partner of a law firm, and you’d like to know whether it would be profitable to bring in another first-year associate. Experience has taught you that evaluating a new attorney is ordinarily a two-year process, so your window of analysis will be two years.

You already know what the market will bear in terms of billing rates for first and second-year associates, so your decision is likely to turn on the difference between expected revenue and estimated overhead in relation to a first-year associate. Overhead is largely a fixed cost, but expected revenue is inherently variable. As it happens, expected revenue is primarily a function of the number of hours billed.

By virtue of the fact that you’re considering a headcount increase in the first place, we’ve established that you currently have more work than your present staff can accomplish working full-time. What’s uncertain is whether the additional work would amount to something like 4000 billable hours over the next two years. Consequently, we’ll simulate various totals of billable hours to study how these affect profitability and return on investment.

Setup in Excel

First, let’s identify and assign a value to each of the variables we need to consider. You know from experience that you’ll have to write off about 35 percent of the hours billed by a first or second year associate, so let’s identify a variable called Collectible Billings, and assign a value of 0.65.

You expect to bill the attorney’s time at $225 per hour for the first year, and $245 an hour for the second year; we’ll average the two and assign $235 as the expected Billing Rate.

We’ll need to include the applicable Risk-free interest Rate (RFR), which is typically the quoted rate on 90-day T-Bills. This is a basic measure of opportunity cost for any type of firm project, including a headcount increase. It also represents the estimated effect of inflation (if any). Since the RFR is unusually low at this writing, 0.1%, we’ll arbitrarily assign a more conservative rate of 2%. Note that if your firm already has an established hurdle rate for evaluating new projects (e.g., eight percent), you should use the hurdle rate here instead of the risk-free rate.

If you ultimately decided to defer hiring a new attorney for now, you estimate that hiring one later would take about 60 days. The economic value of avoiding this 60-day lag is called “Convenience Yield,” and we quantify it as 60/720, or approximately 0.08. This parameter is optional, and if you feel that including it would make your simulations too rosy, you can simply enter 1 in cell B5.

Our time increment is two years, or 2.

Next, we’ll assume that the firm requires associates to bill 2000 hours per year. The value in cell B7 is simply your annual billable hour quota times 2 years, or 4000. We’ll end up making this value dynamic, but we’ll use the firm quota as the basis for certain other estimates, as well as our starting estimate for number of hours billed.

Finally, we’ll estimate (reasonably, if arbitrarily) that Overhead (i.e., salary, benefits, administrative support, equipment, etc.) for an additional associate will amount to sixty percent of expected receivables, or Billing Rate × 4000 × 0.6.

Once you enter these variables, you should have a column of values that looks like this:

Starting Values in Excel

To keep things simple, we’ll hold all other variables constant and express our expected profit from hiring the associate purely as a function of hours billed (actual demand). Because we also expressed cost as a function of expected billable hours, this is not a trivializing assumption.

Monte Carlo Simulation with Excel and VBA

For purposes of simulation, we’ll model the number of hours billed in accordance with the Log Normal distribution. Since we know that there is a stochastic element to simulation, we’ll set that up next.

As I’ve noted in other posts, Excel’s random number generator is notoriously suspect, so we’ll make use of the excellent Mersenne Twister module available here. For my own convenience, I’ve made some minor changes to the original code to facilitate random seeding, and you can download my edited mersenne twister module here. N.B.: Excel for Windows users can and should make use of the free random number generating addin available here.

In what follows, I’ll assume that you’re using my modified mersenne twister module. Once you’ve copied the mersenne twister code, go into Excel and click on the Developer tab (click to enlarge).

Developer Tab Visible

(Here are instructions on how to make the Developer tab visible if it isn’t already). On the top menu bar, click Insert, then select Module. When the new blank module comes up, paste the mersenne twister code you copied. Next, for ease of reference I recommend that you go to View, then select the Properties window. There you can specify a unique name for the module.

We’ll also need to specify a random seed function. For this, my mersenne twister module uses the same VBA function that I provided in an earlier post. This I’ve included separately in a second module.

Finally, we’ll need VBA code to generate Lognormally-distributed random values. Here, we’ll use the random number generation code that I also set out in an earlier post. This I’ve included separately in the third module.

If we simulate values from a Log Normal distribution with mean = 0.05, and standard deviation = 0.1, we obtain a range of values with a histogram that looks like this (click to enlarge):

Random Values Histogram

We can then multiply these simulated values by the firm’s billable quota to obtain a series of simulated billable hour totals.

Going back to our Excel workbook, in cell D2 we’ll enter the following argument:

=dynrngLN(0.05,0.1)

Look in the lower right-hand corner of cell D2 and locate the fill handle. Click and drag the fill handle down to generate as many simulated values as you wish. Generally speaking, when it comes to simulation, the more the better. Nevertheless, I strongly advise you to set your calculation preference in Excel to manual (Excel > Preferences > Calculation > Manual); otherwise, Excel will go through the often time-consuming chore of calculation anytime you change any of the cells on that worksheet. On the manual setting, you can force calculation by pressing command-equal.

Also, if at any point you’re satisfied with the values you’ve simulated in column D, you can always copy them and use Paste Special > Values to make them constant. This will avoid the lengthy re-simulation process every time you recalculate.

Here, we’ll simulate only 100 values to keep things manageable, but you could easily use this same procedure to run 10,000 or more simulations.

Next, in cell E2 we’ll enter:

=$B$7*D2

then drag down so that columns D and E contain the same number of values. Don’t forget the dollar-signs in the above formula; that’s what tells Excel to use the same starting value for expected demand in every iteration.

After completing this step, you’ll have a list of simulated billable hour values in column E. Take a look at the list and see if it strikes you as realistic. For example, it may be unrealistic to expect a first-year associate to bill as little as 75% of quota (because they’d be fired before the end of year 1) or as much as 125% of quota (unless your firm is located in Manhattan). If you want to bring the range of simulated values closer to the 4000 hour estimate, you can adjust the standard deviation value in cell D2 downward from 0.1 to 0.05 or even lower (as long as the number is greater than zero). I’ve deliberately left the estimates somewhat rough in this example to better illustrate the range of possible outcomes.

The heart of the analysis is in column F. Before we begin, go ahead and format the cells in columns F and G to accommodate currency values, and specify that negative values are to be in red. You’ll see why in a moment.

In cell F2, you’ll want to enter the following argument:

=($B$2*$B$3*$B$5*E2)/(1+$B$4)^$B$6

then drag it down as before.

There’s a lot going on in this calculation, so I’ll break it down. Between the first set of parentheses, we’re multiplying the average billing rate times the estimated percentage that’s collectible, then times the convenience yield multiple, then times the simulated number of billable hours. This yields a gross estimate of revenue. We then discount the gross estimate by the opportunity cost, which is represented by the gross figure divided by the quantity 1 plus the risk-free rate, to the second power (since we’re using a 2 year window). This in turn yields a net simulation of revenue.

In column G, we’ll calculate the resulting income from each simulation. Insert the following code in cell G2, then drag down:

=F2-$B$8

Last, we want to calculate our return on investment for each simulation. Go ahead and format column H to contain percentage values, and specify two decimal places. Then in cell H2, enter the following argument:

=G2/$B$8

and drag down as before. Column H now gives us a range of simulated ROIs, which for most purposes is the number that the firm’s executive committee is really interested in.

columns_final

Bear in mind also that the ROI simulations in column H are somewhat conservative, in that we’ve already discounted revenue by the RFR (or whatever value you specified in cell B4).

To visualize the data, select columns E through H and then click on the Charts tab. Select Scatter Plots, and then Smooth. When Excel creates the chart, right-click on any of the markers showing Simulated Revenue, and choose Delete. Then right-click on any of the markers showing Simulated Income, and choose Delete. You’re now left with a chart that plots billed hours versus ROI, which should be the analysis you’re looking for. Since we’re using simulation, your chart will be slightly different, but the one I created looks like this (I’ve added axis labels) (click to enlarge):

ROI Graph

As you can see, break even is somewhere around 3500 hours for the two-year horizon, or about 1750 billable hours a year. It’s also easy to visualize the required number of billable hours for various returns on investment; simply choose an ROI percentage on the left, draw a straight line right until it intersects the curve, and then draw a straight line down to the number of billable hours.

In part 2 of this article, I’ll show you how to use the Solver add-in to calculate the exact number of billable hours that correspond with break-even, as well as any hurdle rate you choose.

Posted in Business, Excel | 2 Comments

Building a Quasi-Random Password Generator in Mathematica

Here’s a simple, down-and-dirty password generator in Mathematica. I wrote it in version 9, but it should be backward compatible to . . . I don’t know, but way back. There’s nothing really fancy or artful here, it’s just something that I find useful.

You’ll note that the function takes two inputs; the first is a binary indicator of whether you want to include non-alphanumeric (i.e., special) characters (1 for alphanumeric only, otherwise 0), and the second input is the number of characters you want your password to contain.

cut[charlist_] := RotateLeft[charlist, RandomInteger[{10, 58}]]; shuffle[charlist_] := Flatten[Transpose[Partition[charlist, 34]], 1]; pwdFn[anonly_: 0, len_] := Module[{chars, charsX, nums, set, numcuts, inter, ret, fin}, charsX = {"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "!", "@", "#", "$", "%", "^", "&", "*", "-", "_", "+"}; chars = Drop[charsX, -11]; nums = {"1", "2", "3", "4", "5", "6", "7", "8", "9", "0"}; set = If[anonly == 1, Join[chars, nums], Join[charsX, nums]]; numcuts = RandomInteger[{233, 6765}]; inter = Nest[shuffle[cut[#1]] &, set, numcuts]; ret = RandomChoice[inter, len]; fin = StringJoin@ret; Return[Style[fin, FontFamily -> "Courier", FontSize -> 14]]];

pwdFn[1, 16]

gYf2RcR96Z7NYgKZ

pwdFn[0, 16]

02ktc%bl*U6v@+GD

Posted in Mathematica | Leave a comment

Excel Random Numbers from Various Distributions

As a sort of companion to the most recent post dealing with alpha-Stable random values in Excel, here’s a VBA module that will generate quasi-random numbers from any of the following distributions:

  • Standard Normal(0, 1) distribution
  • Normal(μ, σ) distribution
  • Log-normal(λ, ρ) distribution
  • Chi-square(n) distribution
  • Student’s T(n) distribution
  • Weibull(η, σ) distribution
  • Pareto(α, κ) distribution
  • Exponential(α) distribution
  • Rayleigh(α) distribution
  • Cauchy(μ, γ) distribution

Note that you’ll need to choose a method of generating Uniform random variates. At the same time, be aware that Excel’s built-in quasi-random number generator, RAND(), is notoriously weak. I prefer to use the Mersenne Twister VBA module available here, and the random number module linked above incorporates the Mersenne Twister module’s nomenclature.

You’ll also need to apply your own random seeding algorithm. Below is a random seed function that utilizes your computer’s internal clock:

Public Function randseed()
' Generates a positive integer value
' from the Excel Date/Time reading

    Application.Volatile
    Application.ScreenUpdating = False
    With Application.WorksheetFunction
    
    Dim currtime As Date
    Dim stime As String
    Dim sval As Double
    Dim inter As Double
    currtime = Now
    stime = CStr(Now)
    sval = TimeValue(stime)
    inter = (sval * 10 ^ 11) / .RandBetween(1000, 10000)
    randseed = Round(inter, 0)
    End With
    Application.ScreenUpdating = True
    
End Function
Posted in Business, Excel, Finance & Investing | Comments Off

alpha-Stable Random Values in Excel

It was a good day all around when Microsoft brought back macros in Excel 2011 for Mac.

Here’s a VBA routine that will generate a 1-D array of pseudo-random numbers from the alpha-Stable distribution. Note that you’ll need to include this excellent Mersenne Twister module to generate the uniform random variates. Please further note that you’ll need to provide the parameter values for alpha, beta, gamma, and delta; this routine won’t fit data.

It’s a helpful tool if you’re noodling on Excel and just want to run some quick return scenarios on a particular asset.

Posted in Business, Excel, Finance & Investing | Leave a comment