October 2012, I posted a mild complaint about the ability to build a calculator in Excel and then publish online via ExcelWebApp. The Excel piece was easy. The WebApp piece was disappointing but was infinitely better than Google’s version. Yesterday a friend asked me to look at a spreadsheet she’d developed. This had me revisit ExcelWebApp and uncover pleasant surprises!
In my friend’s spreadsheet, she had a grid showing various scenarios for breaking even on investment in a start-up business. It looked at
- Number of client visits
- Cost per visit
- Investment amount
For her one-time need, she was fine. But this grabbed my attention as I wondered what it’d be like to pimp this thing out and embed the result in a web page via ExcelWebApp.
Click to use the
Break-Even
Calculator
So, let’s explore this for 3 primary reasons:
- There are lots of tutorials on individual Excel functions or isolated challenges. But there isn’t much available for thinking through the creation of an integrated product. (Musicians know that sometimes the best way to learn a song is to get away from your instrument and discuss what it’s going to make the music sound good.)
- A behind-the-scenes peek into the thought process that makes app development expensive.
- A behind-the-scenes peek into the layers of development that take simple pieces and weave them into a single entity.
IMPORTANT! The how-to does not follow. Detailed how-tos are all over the internet.
This blogpost digs into the thought process.
Let’s start!
APP DEVELOPMENT: CONCEPTION
Objective: A calculator that can show multiple break-even scenarios when the only known variable is the initial investment. The variables we want to measure are:
- The price for visits
- How long it will take to break even
- How many visits will it take to break even
Problem: There are so many variables. How can we easily and quickly adjust multiple variables? Let’s say:
- $31,000 at $70/visit. Can she break even within 500 visits? How long would 500 visits take?
- $31,000 at $80/visit?
- $31,000 at $70/visit within 600 visits?
You can imagine, moving numbers around like that on paper can get maddening. And a spreadsheet that isn’t set up properly can be only a little less maddening.
APP DEVELOPMENT: THE SOLUTION
At this stage we separate core functionality from the critical pieces that are needed to send something out into the world; i.e., validation. For one’s own individual purposes, core functionality is fine. Responsible development, however, includes very meticulous thinking about validation: into the controls, interface, scalability (if necessary) and quality.
APP DEVELOPMENT: CORE FUNCTIONALITY
In developing this Break-Even Calculator, I noticed that there was one explicit variable missing: visits/month. This would be plugged into calculations determining the length of time to recover the initial investment. (Kids, this is why you learn your Algebra. This wonderful world is full of word problems and we have to recognize the variables to define problems and create solutions.)
Next. We needed to revise all formulas so that there are no hard-coded numbers.
Instead of
=(50*100)-31,000
we use
=(E3*G2)-B2
tying the variables (E3, G2, B2) to cells that can be any number and changed instantly
And here’s the layout (click on the images to see them full size):
- The cells in gray are our 6 variables, including Average # of Visits Per Month
- Lowest number of visits in a range we want to investigate
- Highest number in the range of visits
- Avg # of Visits/Mo
- Initial Investment/Loan
- Lowest visit cost to consider
- Highest visit cost to consider
- We have room for 5 $/Visit scenarios, represented by the columns
- There are 20 steps that are dynamically calculated between a range that we set; and that’s represented by the rows in the grid
- Negative values are represented by the red cells
- Positive values are in the blue cells
- In the section below the grid, if there’s a break-even, for each scenario the result is shown by number of visits, number of months and number of years
In the image above: at $87.50, we’re in the blue at 358 visits. At 30 visits per month, the investment of $31,000 will be recovered in 11.9 months. Below, we changed the Lowest Price/Visit to $35 and there is no recovery of the investment after 800 visits.
Next, we look at $50 at the lowest cost and bump the monthly average up to 45 visits. At $87.50 we’re in the blue after 8 months.
That’s all core functionality. What if we sent this out into the world now? It’d be worse than a grown man in a clip-on bowtie: all kinds of wrong!
APP DEVELOPMENT: VALIDATION
Now we have to think of everything that can possibly break the app or produce nonsensical results. And that has to be balanced with the cost of not having anything until the app is perfect. “Good enough” is constantly being measured against every new idea about how an app can be better. So, let’s list the validation features that provide reasonable prevention against breakage or nonsense.
List of Validation Features
Highs and Lows can’t be reversed; i.e., we can’t input a low cost of $50 and a high cost of $20
Red and blue cells need to be on the right values
The dynamic and numerical ranges of the Visits and $/Visit should be smooth and in order
Cells with formulas have to be protected against being typed over
Cells that ask for a number should only accept a number
None of our 6 variables should accept a negative number
The # of Visits can only be whole numbers, there are no 0.75 visits
The app should not accept data in cells that aren’t part of the app
The math has to be correct
The math can be 100% correct but release without the layers of validation would turn the app into a ticking time-bomb. Last night I came across a calculator that accepted an input of 9000 hours per day. CRAZY! BAD! HELLA-WRONG! The developer got the math right and stopped.
ExcelWebApp & SUMMARY
In and of itself, the Break Even Calculator isn’t so spectacular. I share this because it’s one of those things where the conception and details are more difficult than the execution. So, rather than turn this into a how-to tutorial, the focus was on the thought process.
When the ExcelWebApp was first released, it was exciting to embed an Excel spreadsheet on a web page, and share documents with people who use older versions of Excel. Via ExcelWebApp, users of Excel 2003 and Excel for Mac can experience Excel’s slicers and timelines. But the early ExcelWebApp didn’t have the validation features that make Excel powerful. In developing the Break Even Calculator, it was exciting to discover how much validation is included now, and real-time co-authoring was added in mid-November.
ExcelWebApp is still far from perfect but it’s worth exploring.
Remember! As you develop spreadsheets and apps, really think beyond the math and what reasonably and responsibly should be part of a dignified app.
Click to use the
Break Even
Calculator
Additional reading: 5 Reasons for Building Apps In Excel
brain & lightbulb image by smarnad via freedigitalphotos.net
