Consultants Can Deliver Custom Spreadsheets to Each Client

Share

Consultants confront a special challenge in business analytics: they must often deliver analyses that are the same yet different for each client. Customizing models for each client often generates large amounts of low-value work and opportunities for error. If this time is billed to the client, this dilutes the consultant’s value proposition; if the time is not billed to the client, the consultant’s overhead expense increases.

ModelSheet Software has a simple solution for consultants: use prebuilt spreadsheet models that you can customize by filling in a web form with information about your client’s business. You don’t edit formulas or change layout of spreadsheets by hand. You can include or exclude major features with one click of the mouse. You can change the time line with a few clicks. You can add new products, sales regions and other items by typing in the new names; or reorganize products into different families by rearranging the list of products and families.

The end product is a conventional spreadsheet that you can maintain and enhance by conventional methods. The process is 100% automated, so you save staff time, speed up turnaround, and results are more reliable. Because editing is easier, you can support clients with more realistic models.

Customized spreadsheets offer superior

  • model flexibility and realism
  • reliability
  • turnaround time and productivity
  • collaboration and auditing
  • deployment of your intellectual property

For more information on how customized spreadsheets help consultants, download this whitepaper for consultants.

We offer dozens of prebuilt yet customizable spreadsheet models that are likely to be useful to consultants. If you want more flexibility that our customizable models offer, we will add new features to our customizable models, or build new customizable models from scratch to your specifications.

For more information, download a whitepaper about customizable spreadsheets or go to our main webpage about custom development services.

View our portfolio of customizable models.

Posted in Uncategorized | Tagged , , , , , , , | Leave a comment

Make Versus Buy for Your Next Spreadsheet

Share
Man engaging in barter, paying his yearly newspaper subscription to the "Podunk Weekly Bugle" with various farm produce.  F.S. Church, published in Harper's Weekly, January 17, 1874, p. 61. United States Library of Congress's Prints and Photographs division digital ID cph.3c01495.

Man engaging in barter, paying his yearly newspaper subscription to the “Podunk Weekly Bugle” with various farm produce. F.S. Church, published in Harper’s Weekly, January 17, 1874, p. 61. United States Library of Congress’s Prints and Photographs division digital ID cph.3c01495.

Economists believe the prevalence of the division of labor is closely associated with the growth of total global output. It’s not a new idea. This quotation is from the 4th century BCE:

Of necessity, he who pursues a very specialised task will do it best.

– Xenophon in Cyropedia

We were pondering on this idea in an attempt to convey the value of our customized spreadsheets.

Suppose your time is worth $50 per hour ($100,000/year fully loaded cost and 2,000 hours/year). How much does it cost you to build a spreadsheet model compared to buying one that is customized to fit your needs? If it would take you two eight-hour days, that’s $800. Our custom spreadsheet for, say, $50 saves you $750. Furthermore our custom spreadsheets have been carefully laid out, have been throughly tested, include helpful Excel comments, and provide readable symbolic formulas. They come with a Quick Start Guide to explain the model to you. If you want more features added, you can tweak the spreadsheet easily. Or you can let us do it, using the highly structured spreadsheet authoring environment we use to build each of our customized spreadsheets.

Check out our complete catalog of custom spreadsheets today and free up some time for the things you do best.

Posted in Economics | Tagged , , , , | 1 Comment

The Economy, Stupid

Share
Waiting for relief checks during Great depression. Calipatria, CaliforniaMarch 1937 SourceUnited States Library of Congress's Prints and Photographs division digital ID fsa.8b31768.

Waiting for relief checks during the Great Depression. Calipatria, California, March 1937. Source: United States Library of Congress, digital ID fsa.8b31768.

Famously, James Carvill hung a sign outside Bill Clinton’s 1992 presidential campaign headquarters that read:

  1. Change vs. more of the same
  2. The economy, stupid
  3. Don’t forget health care

Although the sign was intended for campaign workers, a variant of #2 became the de facto slogan for the campaign.

It’s the primary election season here again in the USA and once more the candidates are taking about the economy, even though most of them are lawyers with little background in what economists proudly call their dismal science. (Proudly because Thomas Carlyle — who coined the pejorative against them in 1849 — was arguing for slavery and against the economists’ call for freedom of the labor market.)

ModelSheet doesn’t have any political affiliation but we do know a bit about business and the economy. We offer two economic model spreadsheets that you can use to inject some rationality to the heated rhetoric around jobs, investment, tax, and the economic cycle.

The first offers a dynamic macro-economic model of a national economy with production, consumption, capital investment, labor market with unemployment, price level, money supply, inventory cycles, and shocks.

The second offers dynamic Monte Carlo simulation of an economy with shocks and multiple trials. This economic model has production, consumption, capital investment, labor market with unemployment, price level, money supply, and inventory cycles.

Our Sales Plan model enables you to make your sales plan depend on values of market/macroeconomic indicators for each product, customer industry, sales location, and for the entire market. When indicator variables change, you can enter the new values to get an updated sales plan. This feature helps you estimate how much of variance from the original plan is due to changed market conditions.

We hope these give you more insight into the various candidates’ proposals.

Posted in Economics, Templates | Tagged , , , , , , , | Leave a comment

Come to a Determination Accordingly

Share
Benjamin Franklin portrait attributed to Van Loo

Benjamin Franklin portrait attributed to Van Loo, image credit: American Philosophical Society

The title of this posting is from a recollection of Benjamin Franklin about making difficult decisions:

“…my Way is, to divide half a Sheet of Paper by a Line into two Columns, writing over the one Pro, and over the other Con. Then during three or four Days Consideration I put down under the different Heads short Hints of the different Motives that at different Times occur to me for or against the Measure. When I have thus got them all together in one View, I endeavour to estimate their respective Weights; and where I find two, one on each side, that seem equal, I strike them both out: If I find a Reason pro equal to some two Reasons con, I strike out the three. If I judge some two Reasons con equal to some three Reasons pro, I strike out the five; and thus proceeding I find at length where the Ballance lies; and if after a Day or two of farther Consideration nothing new that is of Importance occurs on either side, I come to a Determination accordingly.”

(References: Benjamin Franklin: An American Life by Walter Issacson, p.74-75, 1772 letter to Joseph Priestley)

Although the founding father was a little skeptical about the “…Precision of Algebraic Quantities…” he wasn’t averse to innovation, so let’s imagine he lived today (instead of the 1730s when he developed his method) and had access to a spreadsheet…

Maybe he would have developed something like the ModelSheet custom spreadsheet for Decision Support, or the sister versions, which are simpler, more specialized tools for evaluating Job Opportunities and for rating Employment Candidates.

For example, the Job Opportunities custom spreadsheet supports your two-way decisions (like Ben’s Pro-Con situation: job offer versus current job, for example) or more complex cases with up an 18-way choice. You can take the default criteria as they are, or pick your own. I added three from Dan Pink’s book Drive: The Surprising Truth About What Motivates Us (in short: autonomy, mastery, and purpose). Then you can weight criteria as finely as you like, and score them for each possible outcome.

But there’s more: you can add risk of uncertainty, and your aversion to risk to the mix. For example, you might give a low but certain score to your present boss and a higher but more uncertain score to your (unproven) future boss. If you’re risk averse you might be better off with the devil you know.

We’ve launched a 7 day free trial period on these custom spreadsheets, so you’ve nothing to lose. Try one today!

Posted in Decision Support, Templates | Tagged , , , , | Leave a comment

Find It Fast

Share

We recently published new template pages with a cleaner, simpler design. The pages for each product are now tabbed together

Each page highlights in a few words ways how that model helps you to improve your business.

A big shout out to Anne Caborn and Clare O’Brien of the awesome WebWordsWorking in the UK for the redesign consulting.

Circle us on Google+

Posted in About ModelSheet | Tagged | Leave a comment

I Miss the Mob

Share

 

What? You miss the mob? The mob is bad, right?

Yes. But first watch this two minute video by Derek Sivers (founder of CD baby), then all will be clear. I promise.

The video is an excerpt from Derek’s excellent book Anything You Want. Click this link for info on how to get not only the book but also the 241 free MP3 tracks (hand-picked by Derek) that come with it.

The point is that the right metric can help drive your business. For a casino, it might be money in and money out, but for a manufacturing business the situation is often a bit more complex.

For example, one of my clients was running a complex silicon chip business and was concerned by poor competitiveness with respect to yield (a measure of what fraction of chips are functional). They had focused on a measure called “fatal defect density,” but were missing the big picture. I helped them create a new metric “silicon area in versus silicon area out” that captured all the points of drop out. Once we had that, we could break down the losses into subcategories. We found the most costly losses weren’t where they expected at all. Once the client focused on fixing those, the increased production value was over $100 million/year.

What’s the right metric for your business?

Our custom operations analysis spreadsheet model contains everything you need to analyze a wide range of manufacturing and business operations process flows with as few or many stages as is needed. You get the flow of units and costs through the process, with optional market demand, revenue and financial analysis. The model includes transient dynamic behavior, with a time grain and time range of your choice.

Posted in Operations Analysis | Tagged , , , , , , , , , , | Leave a comment

Battle of the Capitalization Tables

Share
Our capitalization table battles the competition like these sumo wrestlers

Image: Wikimedia Commons/J.Drevet/CC-BY-SA

For your small or medium sized enterprise, a capitalization table is an essential tool to communicate with your investors. Transparency in tracking transactions through multiple investment rounds builds confidence.

Recently, two customers contacted us asking for a feature comparison with competing products. We took a look and even surprised ourselves (see the comparison table below). How could businesses possibly express the real world complexity of their capitalization with such simple-minded products? Were they excluding valuable investment options based on their inability to capture them in their capitalization table? We hope not.

In contrast, our capitalization table has everything you need to track investments including:

  1. The common types of securities: common stock, preferred stock, convertible notes, options, and several series of each type of security to reflect changing terms at time of issue.
  2. Specification of key terms for each type of security: share prices, terms for conversion to common stock, option exercise prices and other terms of exercise, payout preference rights (seniority).
  3. Numerous investment rounds.
  4. Common types of transactions: sale of all types of securities, conversion of notes, exercise of options, stock splits.
  5. Investors and investor groups, with final payouts to each security and measures of return on investment.

We think our product wins hands down against the competition so we encourage you to try our Custom Spreadsheet for your Capitalization Table today.

How about you? What do you think of the various cap tables out there?

Comparison of Cap Tables from “A”, “B”, and ModelSheet

Capability Competitor “A” Competitor “B” ModelSheet

Basic Structure

Investment rounds One, cannot be changed Yes, 10 Yes, up to 18
Series of preferred stock One, cannot be changed No Yes, up to 12
Dividends on preferred stock No No Settable
Price of preferred stock Same as common. No Settable (premium over common price)
Series of convertible notes One, cannot be changed No Yes, up to 12
Interest on debt / notes No No Settable
Series of options Two, cannot be changed No Yes, up to 12
Stock splits No No Yes, multiple, settable split ratios
Investor groups Two equity, two debt. Cannot be changed Yes, 5 Yes, up to 15

Scenarios

Investment scenarios One One Yes, up to 20
Valuation scenarios One One Yes, up to 20

Measures of Return on Investment

Percentage of final value Yes Yes Yes
NPV No. Does not track time value of money No Yes
IRR No No Yes
Return ratio No (easy to add) No Yes

Treatment of Downside Scenarios

Conversion of preferred stock Assumed converted No Conversion decision & timing settable. Default: conversion occurs in last round.
Exercise of options Assumed exercised No Exercise decision & timing are settable. Default: exercise occurs in last round.
Liquidation preference for preferred stock No No Yes, settable, including ‘pari passu’
Liquidation preference for convertible notes No No Yes, settable, including ‘pari passu’

Other

Customizable No No Yes, in many ways.
Price $9 Unknown Free 7 day trial.
Standard ($49)Pro ($69)PowerPro ($99)
Spreadsheet Platform Microsoft Excel a new spreadsheet platform Microsoft Excel or Google Doc
Posted in Capitalization Tables | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , | Leave a comment

Improving Performance of Large Spreadsheets During Data Entry

Share
Struggling with mountains of data in your spreadsheets?

Struggling with mountains of data in your spreadsheets?

Spreadsheets larger than about 60 MB (xlsx; about 200 MB for xls) tend to have sluggish interactive response. Size is not the only factor – for example, the Excel database functions (dsum and friends) tend to make a model slower than its size would otherwise suggest. Slow response is particularly troublesome during data input.

The simplest way to improve interactive response of large spreadsheet models during data input is to turn off automatic computation during data entry, then manually re-compute when you need the full spreadsheet model updated (using the F9 key). This makes sense: generally, you don’t need an updated version of your model every time you enter one new number. You can turn off automatic recalculation in Excel 2010 by clicking on tab Formulas in the ribbon (at the top of the Excel window), then on Calculation options.

Using manual computations does not work so well if you want to update some content in the data input tables during data entry. We have found that a useful solution is to locate the data entry tables on separate worksheet(s), and to place the input worksheets in a separate workbook. You can open the data entry workbook by itself for data input, and enter data with very fast response times. The input workbook updates external links to named regions in the main workbook using cached values that it remembers from the last time it was able to communicate with the model in the main workbook.

If you are starting with one workbook, you can separate the input tables into a separate workbook.

  • First, design the workbook so that the data input tables are on worksheets that contain virtually no other computations that rely on the main model.
  • Move the input worksheets to a new workbook and save both workbooks in the same directory.

Excel will automatically tell each workbook how to communicate with the other one. If you need to update the links that enable the workbooks to find each other, you can do this using the Edit Links… dialog found under Data in the ribbon (the Data tab at the top of the Excel window in Excel 2010).

Excel has two kinds of links: local links in the same workbook, and external links that can link to other workbooks.

  • Local links are defined by references to cells or named regions in the same workbook. External links are defined by references that include the full path name (such as ‘c:/directory/filename.xlsx[sheet1]‘!region_name).
  • External links can be easily updated to point to a new file location using the “Edit Links…” dialog .
  • If your workbooks have local links, then manually convert them to external links by editing the references to the external addresses in each formula (with search-and-replace and copy operations).

If the input workbook and the main workbook are allowed to communicate and then are closed, then each workbook caches values for external named regions. When you open the input workbook by itself, you get fast response during data input (typically sub-second response), and the workbook can update its external links with cached values.

If you start out with two separate workbooks, you can get them to read named regions from each other using the same Edit Links… dialog.

You don’t have to choose between fast response time during data input and updating information on data input worksheets.

Posted in Spreadsheets | Tagged , , , , | Leave a comment

Are Support Costs Weighing You Down?

Share
Do you feel support costs are crushing you?

Do you feel support costs are crushing you? (Image: Lee Lawrie’s Atlas sculpture, Rockefeller Plaza, New York, New York Source: Wikipedia/Michael Greene/Creative Commons)

In Greek mythology, the defeated Titan, Atlas, was condemned by Zeus to stand at the western edge of the Earth and support the sky on his shoulders. Sometimes it feels like your tech support department is condemned to a punishing task of equal magnitude. For example, working with one of our clients, we discovered a certain category of their product had very high support costs, and their return on sales was much lower than was widely believed. This alert galvanized the product team to fix the documentation and online help, isolate troublesome features, streamline installation, and invent new features to automate the complex tasks that were causing users to “phone-a-friend.” Executive management had a firm basis for tasking the product team and measuring their progress.

How can you determine which products and which support issues would benefit most from such an intervention? Our new custom spreadsheet for customer support analysis can help you make that decision.

This model analyzes customer support operations by tracking support loads, revenue, costs, and margins. It includes operating measures such as customer count, support calls, staff headcount and utilization.

The model includes these features.

  • Deferral and accrual of support revenue in order to line them up to the corresponding expense in each time period.
  • Support load measured in number of calls, distribution of call lengths, and labor costs.
  • Support by issue, such as installation, maintenance, application support. This information is very helpful for identifying opportunities to reduce costs and improve customer satisfaction.
  • Product support costs, including direct labor headcount and costs, direct material units and costs, and direct overhead costs.

This analysis should lighten your load.

Posted in Operations Analysis | Tagged , , , , , , , , , , | Leave a comment

Avoid These Five Mistakes When Planning and Tracking Sales Commission

Share
Fuller Brush salesman Fred Wetstine making call on housewife by Alfred Eisenstaedt

Fuller Brush salesman Fred Wetstine making call on housewife. Photo: Alfred Eisenstaedt, Google LIFE photo archive © 1946 Time Inc.

A motivated sales force is the heart of any business, but the law of unintended consequences can derail your enterprise if the commission incentive drives the wrong behavior. You don’t want to misalign the sales force with the interests of your company.

Some common pitfalls are:

  1. Plans that lead to excessive discounting
  2. Plans that cause a misallocation of the reps time across your product line
  3. Too high or too low a quota
  4. Too high or too low a commission percentage
  5. Plans that have no flexible commission on loss leaders.

Let’s look at some examples and solutions.

Products with different gross margins

Widgets and Thrum-mats have similar list prices, but the cost of making Widgets is much lower than the cost of making Thrum-mats. A revenue-based plan leads to a lost opportunity because the reps have no incentive to focus on high margin Widgets.

Solution: Pay commissions as a percentage of gross margin (= revenue – cost of goods) instead of a percentage of revenue. An approximate cost of goods is fine, perhaps expressed as an amount per unit sold. Increase the commission percentage so that the overall amount of commissions is about the same. The reps will spend more effort trying to sell Widgets than Thrum-mats, aligning their interest with those of the company.

Products with different warranty costs

Now imagine Widgets and Thrum-mats have similar list prices and gross margins, but Widgets incurs much lower warranty costs than Thrum-mats. The problem is similar to the example above, except that now the cost difference is in operating expense instead of cost of goods.

Solution: Pay commissions as a percentage of revenue (or gross margin) less an operating expense charge for warranty maintenance that is smaller for Widgets than for Thrum-mats. Again, the goal is alignment of interests.

Loss leaders

In this scenarios, Widgets and Thrum-mats have similar list prices and costs, but Widgets are highly differentiated and Thrum-mats more of a commodity. Thus, Thrum-mats are often thrown in as a loss leader in bundled deals. Sales reps and managers who are assigned to Thrum-mats feel cheated out of a potential list price sale when this happens.

Solution: Pay part of the commissions on Thrum-mats as a flat amount per unit sold.

Impact of commission changes

You want to to change your commission plan with the goal of increasing the sales of high margin Widgets relative to low margin Thrum-mats. How do you measure success?

Solution: Compare planned results for sales and commissions with actual results. You might have one spreadsheet to compute planned sales and commissions, and a second that computes actual results in the same format so it is easy to compare them.

You can set up sales commission plans like these very easily with the right custom spreadsheet. You can experiment, track the outcome, and adjust. For example, we’ve just released one that does all this and more. Click on the link for a free trail of our custom sales commission spreadsheet.

Posted in Marketing & Sales | Tagged , , , , , | 2 Comments