3 Principles of Good Spreadsheet Design

I have run across countless spreadsheet articles that walk you through the steps for working with data, creating formulas, and making it all look good, etc.  But, rarely do I run across articles that talk about the bigger picture of how to organize an entire workbook so that it is easy to use, modify, and analyze. Without this bigger picture in mind, it is so easy to create spreadsheets that are overly complex, convoluted, and ultimately produce data that is inaccurate.  I recently worked with two organizations who were struggling with poorly designed spreadsheets that supported critical processes but clearly were not working for them. One was a food manufacturing company needing to track the many ingredients that were used in each of their products. The other was a transit company that needed to track ridership and vehicle usage for multiple routes.

Using my three principles of good spreadsheet design, I transformed their complex, convoluted, and error prone spreadsheets into organized and maintainable tools that are efficient for data entry and produce accurate reports.

Miles of Data to Track

The transit company is a great example of a spreadsheet designed with good intentions but that eventually spun out of control.  The screenshot below illustrates how it combined data entry, multiple sets of data, and analysis into a single sheet.  While this worked initially, data entry was slow and as routes changed and spreadsheet updates accumulated, errors piled up. Finally, the spreadsheet could not accurately produce the reports that were required by their funding sources. With all of the reporting functions intermingled with the data entry, each time they had to make a change, formulas had to be updated individually across all the rows or columns.  As you can imagine, this was time consuming and impractical.  Inevitably, some formulas were missed and the ridership totals and vehicle usage totals became inaccurate.

The original sheet with the data entry and analysis combined

Principle 1: Separate Data Entry From Reporting/Analysis

Like most businesses, the transit company above used spreadsheets for two main purposes: data entry and reporting/analysis.  Both are equally important but have conflicting requirements.  When entering data, we want to be able to add new data points quickly, using the fewest mouse movements and keyboard clicks possible.  The most efficient approach is to organize all of the data we are entering into adjacent rows and columns.  On the other hand, when doing analysis we want to see the summaries of our data (totals, averages, etc.) all together so that we can interpret meaning and see trends.  How to solve this conflict?  Use separate tabs for the data entry and reporting/analysis parts of your spreadsheet.

In the case of the transit company, I rebuilt their spreadsheet to separate the data entry and analysis functions so that each could be managed separately.  The revised sheet captures all the ridership and vehicle usage data on their own tabs, with formulas that are reused, while the reporting is done on its own separate tabs.  Below is an example of the separated sheets.

Revised sheet for data entry only

Revised reporting sheet, separate from data entry

Principle 2: Group Sets of Data into Separate Tables

Just like it's helpful to keep data and summaries separate, it is helpful to keep different sets of data separated.  To improve the efficiency of the transit company spreadsheet, I created one table with the ridership information (see the Data Entry screenshot previously for how this looks) and a separate table to track the use of their vehicles.  By separating this data it was much easier to enter each efficiently and then analyze ridership separately from vehicle usage.

They also wanted to be able to summarize their data by many different types of dates.  For example, they wanted to be able to compare ridership on different days of the week, different months, and different quarters.  I was able to accomplish this by adding a date dimension table, which contains details (e.g. day of the week, name of the month, etc.) about every date for a period of time. This table contains many years worth of dates so that it never needs to be updated.  By defining a relationship between the ridership data and this date table, it becomes easy to view the data using different versions of the date without needing to add all of the date data to the ridership data table.  Here's an example of the date dimension table and report table that uses the day of the week instead of the date.

An example date dimension table

Reporting by day of the week using the date dimension table

Principle 3: Let Excel do the Formula Work For You

While there are endless guides for writing better formulas, the best formula is one that you don't have to write.  Excel Tables and Pivot Tables allow you to analyze and calculate data while minimizing the number of formulas you need to write and manage.  All of the reporting screenshots above are examples of Pivot Tables in use.

With Excel Tables you can turn a data range into a table and then, using one formula, calculate entire columns using the data in the table.  If the formula needs to change later on, you can do it in one row and it will automatically update all the other rows.  The transit company's vehicle usage data included starting and ending mileage for each vehicle. By turning this into a table, I was able to set up a formula that was used in a column to automatically calculate the total mileage driven for every entry.  In the future, if the formula needs to be changed, they will only need to change it in one row and it will automatically update for all other rows.

Pivot Tables allow you to quickly and easily summarize and categorize your data without having to use any formulas.  In our transit company example, they are now able to summarize their ridership data by stop, date, month, or bus as they choose by updating the pivot table.  All without writing a single formula.  

The bottom line is, Excel provides a powerful platform for gathering and analyzing your data, but, like any powerful platform it must be set up carefully to make the most of its capabilities.  Contact us today to learn more about how we can help you build powerful and efficient spreadsheets for your organization.

Apple's Best Product (Hint: It's not a phone or a watch)

Also, unpopular ideas aren't always bad, great ideas can be, psychological ownership, and social media lessons

• Did you hear?  Apple released new iPhones!  While they may argue that they are Apple's best products ever, Michael Grothaus disagrees.  He argues that Apple's best product is now privacy.  In a world where most technology companies make their money by selling your data to advertisers (looking at you Google and Facebook), "Apple makes its hundreds of billions every year by selling physical products that have a high markup."  The result?  Apple can take steps to protect your privacy that other internet giants simply can't!

• Want to build a loyal customer base? When customers have a sense of psychological ownership for your product, they become loyal fans who repeatedly buy and promote what you are selling.  They feel so invested in your product that it becomes an extension of themselves.  Here are three strategies that you can use to cultivate customers' psychological ownership of your product: 1) involve your customers in designing your product, 2) customize your product for them, 3) create opportunities for your customers to know every facet of your product so that they develop a sense of having a special, unique relationship with it.  Be careful though.  Once your customers have psychological ownership, changes you make in your product can backfire. Read this article for some great examples of companies who developed psychological ownership and those who had it come back to bite them.

• Have you ever believed something that was counter to what you most people believed?  You just knew it in your gut and it turned out you were right?  Airbnb, Rent the Runway and Foursquare all began as ideas that no one believed would work.  According to contrarian investor Peter Thiel, the most powerful business innovation question you can ask yourself or someone else for that matter is, "What important truth do very few people agree with you on?"  Learn how the answer to this question can drive business innovation in this short article by Scott Belsky, CPO at Adobe.

• On the other hand, "A good idea before it's time can be a deadly trap sucking pioneers resources and wasting time fertilizing the ground for settlers to come in later to flourish."  Here are stories about seven internet related start-ups that had amazing ideas and failed. While the pioneer businesses went down, the original ideas persisted and now underlie many of the platforms we use on a daily basis.  Since then, many of these seven founders have gone on to leadership in successful ventures.

• Are you an entrepreneur or someone with entrepreneurial aspirations?  Successful entrepreneurs have some key strengths in common which most people can achieve through intentional practice. From Dhaval Patel, here are some important ones to consider in your life as an entrepreneur.  Can you make lemons out of lemonade? Successful entrepreneurs find opportunity in obstacles.  Do you strive continually to improve?  Successful entrepreneurs are always thinking of ways to improve, in how they work personally or how their business works.  Do you find yourself immobilized by fear of uncertainty?  Successful entrepreneurs know how to pursue their goals in the face of uncertainty and ambiguity.  Do you practice generosity? Successful entrepreneurs develop good business karma by helping others succeed.

• Organic reach is ever harder to achieve on social media.  Facebook, Instagram, LinkedIn, Twitter, Pintrest, and so on all increasingly require companies to pay to reach their users.  How can you get the most organic reach possible?  Take a lesson from an industry that is prohibited from advertising on most social media platforms: the cannabis industry.  Making use of influencers, video, brand ambassadors, and multiple social media platforms can all boost your organic reach, getting you more exposure for less money.

Don't Write a Business Plan, Make a Canvas Instead!

Got a business idea?  First choose your business model, then write a business plan using the Business Model Canvas. For some business model examples, read my earlier post, which included the multi-sided platform, the long-tail, and the ever-popular freemium models. Now, to create a business plan, I recommend the Business Model Canvas as a compact, single page planning tool that's quick, agile, and comprehensive. In contrast, traditional business plans take many hours to produce and yield long, involved documents that are really difficult to update. The Business Model Canvas is straightforward, fast to create, and easy to update as you learn more about your market, your customers, and how you are going to serve them. 

The method is founded on continuous learning (as in Lean Startup Methodology) with particular emphasis on your value model (are you selling something that people want to buy) and your growth model (can you get enough people to buy it). Once the canvas is created, you can envision the business as a whole and then adjust the plan as you learn more.  In the early days of starting your new business, being able to rapidly react to new information is absolutely vital to finding a final business model that you will be able to scale. The business model canvas is a key enabler of that learning cycle.

With your plan in hand, you'll have productive, meaningful conversations with your business mentors, advisers, or investors to hone your plan and drive your business forward.

What is the Business Model Canvas?

The Business Model Canvas was originally presented by Alexander Osterwalder and Yves Pigneur in their book Business Model Generation. The canvas breaks your business plan into nine building blocks:

Customer Segments

Who are you serving?

Value Propositions

What customer needs are you satisfying?

Channels

How do you communicate with and deliver your value to your customers?

Customer Relationships

How do you interact with your customers?

Revenue Streams

Which customer segments pay you? How do they pay you?

Key Resources

What things (physical, intellectual, etc.) do you rely on to serve your customers?

Key Activities

What do you need to do to serve your customers?

Key Partnerships

Who must you work closely with to serve your customers?

Cost Structure

What are the main expenses involved in serving your customers?

These 9 building blocks are arranged on a one-page canvas.  The template below provides a bit more detail about each building block and I'll dive deeper into each one in future blog posts (so stay tuned!).

View fullsize

How to get Started

Getting started with the business model canvas need not be intimidating! Here are some tips:

• Read the Business Model Generation book I linked above. It gives a wonderful explanation of canvas as well as examples of how the canvas works for a wide variety of business models.

• Get a template for the business model canvas

  • Print out the picture above

  • Sign up for a free account at www.strategyzer.com (publishers of the Business Model Generation book) and download their template

  • Google "business model canvas template" to find a variety of templates

• Do it with a team

  • If you are all in the same location, print out a blank canvas, stick it on the wall, and fill it in using sticky notes generated by the team.  It's simple, easy to set up, and keeps everyone involved and engaged by moving sticky notes around.  

  • If you are not all in the same space, you can use a digital tool to create the canvas using your screen-sharing tool of choice.  I found that the flowchart tool draw.io had an excellent template for the business model canvas.  The picture above is based on their template, which you can find on their website.

No matter what template you use, the business model canvas is a powerful tool for turning your idea into a business.  Contact us today to learn more about how we can help you prepare your ideas and your business for growth.

This Post will Blow your Doors Off!

Learn how to craft exciting announcements, get your side hustle off the ground, and beat your inner procrastinator.

Create Announcements Your Customers Will Read

What's important to your customers and what will get them to pay attention? It's your product and what it can do for them. Yet many product announcements or press releases lead off with the author's excitement. Customers really don't care about that. This article from Inc.com has some basic tips that will help you craft announcements that will get your customers excited and engaged. 

Getting Started Is the Hardest Part 

If you've ever been on a bicycle, you know that getting started is the hardest part.  Once you push down on the pedal and then get a few circles behind you, you build speed and momentum and it's way easier to keep going than to stop. It's the same thing with getting started on an important task. Getting started is the hardest part.  This article proposes an easy trick for getting started - the 5 minute rule. Find out more about it.

How To Start A Business While You're Employed

Many people dream about starting their own business to supplement their employment income, pursue an outside passion, and/or to eventually become their own boss.  Knowing where to start, building your skill set, staying motivated, and having time to focus will make your dream into reality.  Want to find out how to overcome barriers that could get in your way?  Read this article from the Harvard Business Review. 

E-Mail: Using It Well Is More Complicated Than You Might Think

When my Mom first entered the work world 40 years ago, she tells me that there were still secretaries who typed letters that staff wrote by hand on legal pads with review by supervisors.  Writing a letter involved etiquette, content, and grammar.  Getting it out was a relatively slow process. Today we write e-mails and it's fast.  However, the same considerations and more apply to writing e-mail, because it's so fast and easy and often sent without anyone else's review.  Without enough attention, the results can be embarrassing, if not disastrous for relationships and business.  Here are 20 tips to hone your e-mail skills.

Doesn't Everyone Know That You Need to Be Careful With Social Media?

Based on this article, the answer is clearly no.  This cautionary tale demonstrates why the public nature of social media must be remembered by everyone. Don't write anything that could backfire if the wrong person read it. Remember the rules for e-mail from the article above when using social media.

Have an idea for a business but don't know where to start?  Trying to grow your business but feel like you’re running in circles?  We can help you move toward the business of your dreams!  Get in touch today!

Choose the Right Funding for your Startup

No matter what kind of business you own, ideally, your products and customers should provide all the funding that it needs.  But, reaching that can be tricky and often takes time. Choosing the right way to fund your business as your customer base and revenue grows is critical.  There are a number of different ways to get the funding you need it your company grows.  Here are some of the considerations to keep in mind when choosing your funding sources.

Bootstrapping

Since the goal is to have your customers provide all the funding for your business, why not try that from the beginning.  Nationally, as funding markets are "correcting for years of overly exuberant startup funding," or in states like Vermont where startup capital has always been difficult to find, bootstrapping your company can be an attractive option.

With bootstrapping, you create a profitable product from the beginning and then reinvest the early revenue to improve the product, expand your customer base, and grow your profits.  The process strongly aligns with the principles of the Lean Startup methodology, which emphasizes continuous validated learning.  Bootstrapping requires a strong internal drive, a willingness to build sweat equity, and a lifestyle that gives you the flexibility roll with the inevitable ups and downs of income.

It also requires that you develop and sell a good product quickly. When your only source of cash is revenue from the products you sell, you must have a product that people want to buy from the start. With outside funding, poor product performance can be masked.  When funding runs out for these products, they fail in the marketplace.

Without external investors, you continue to own and control your entire company and you can grow and learn at your pace without answering to anyone else.

Crowdfunding has become a popular mechanism for bootstrapping a new product.  Platforms like Kickstarter and IndieGoGo allow businesses to offer the public early access to a product, typically for a reduced price.  This allows you to test the demand for a product before it is produced and to collect the funds needed to make a product before you deliver it.  Crowdfunding allows you to make larger jumps in scale than would otherwise be possible if you had to make the product before you sold it.

External funding

Not every business is suited for bootstrapping.  Businesses that are very capital intensive will likely need outside funding since it will take a lot of money before they are able to generate revenue.  Similarly, businesses that are looking to quickly grow to massive scale (hundreds of thousands of customers and up) can benefit from outside investment to power the rapid development and scale up needed.

Additionally, not every business owner is well suited for bootstrapping.  If you benefit from having a strong outside source of motivation and direction, bringing in an outside investor may increase your chances of growing your business successfully.

If bootstrapping isn't the right fit for you or your company, you will need external funding.  Choosing the right type is extremely important.  Should you take a loan from a bank?  Should the business take an investment in exchange for equity?  Read below to learn more about options for external financing.

Equity Financing

Equity financing is the umbrella term for investments in which the investor takes an ownership stake in the company in exchange for providing funding.  There are many different types of investors who do equity investments and each is suited for particular types and/or stages of a business's growth.

Angel Investors

Angel investors are typically "wealthy individuals who like to invest their personal funds in startups."  They tend to be less formal than venture capitalists, and while they typically take an ownership stake some look for a percentage return on their investment instead.  Their informality and generally smaller investment size makes them well suited for early investment rounds before other investors would be interested.

Angel investors are also typically people that you already have a trusting relationship with, which makes networking and relationship building vital to securing this type of funding.  Additionally, since angel investors often have entrepreneurial backgrounds, they may be able to provide expertise and access to networks beyond their financial investment.  However, as with any investor, it is important that you can work well with an angel investor long term.

Family and friends may also invest in companies in a similar manner.  Depending on their background, they might act like an angel investor or they may invest based on their faith in you rather than on a deep understanding of your business.  Either way, they are often one of the earliest sources of funding that a company is able to access.

Venture Capitalists

Venture capitalists (VCs) are organizations of investors.  Run by professional investors, they use funds raised from partners "partners such as pension funds, endowments, and wealthy individuals."  In general, VCs are interested in later stage companies that have already shown potential for long-term growth.  VCs also typically look for more control in the company than angel investors do.  Their investments will often require seats on the company's board and sometimes even the ability to replace you as CEO  the company.

Even more so than angel investors, VCs bring business expertise and access to networks to help companies grow.  This, coupled with their ability to make large investments, makes them a powerful partner for companies that are looking to quickly grow to massive scale.  In exchange, they look for much greater control over the company.  This increased control makes it vital for you, as a business owner, to carefully choose which VCs you take investments from.

Strategic Investors

Strategic investors are similar to VCs, but are typically corporations making investments instead of funds.  For example, when Coca Cola invests in an up and coming beverage company or Budweiser invests in a craft brewer, these are strategic investments.  The level of funding can vary greatly depending on the stage of the company.  These company's investment styles are similar to VCs and many of the same benefits and drawbacks still apply.  They often have deep knowledge and experience in the market and can provide expertise that may be impossible to find anywhere else.  In exchange, they are also likely to require high levels of control over the company.

Debt Financing

Finally, debt financing is an option for some companies that do not want to give up ownership or control of the company.  Typically, debt financing comes in the form of loans from banks, though sometimes angel investors will provide funding with a fixed return.  The benefit of debt financing is that you, as the business owner, retain control over your company.  However, banks are also often less likely to lend to businesses with significant risks.  Thus, if your business has high capital requirements but relies on relatively proven technologies you are more likely to be able to secure debt financing.  Keep in mind that debt financing usually gets paid back before any return is given to equity holders.  So, a business with a lot of debt will be less attractive to VCs and strategic partners.

Regardless of how you want to fund your company, you will need a plan for turning your ideas into a business.  Contact us today to learn more about how we can help you prepare your ideas and your business for growth.