Bridging the Online vs Brick & Mortar Gap

/

Also, get the most from your marketing budget, parents make excellent entrepreneurs, and understand your customers' problem stories

 

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

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 sheet for data entry only

Revised reporting sheet, separate from data entry

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

An example date dimension table

Reporting by day of the week using the 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!).

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!