Follow these 3 Tricks For a Treat of a Spreadsheet!

In honor of Halloween, here are 3 tricks you can use to get the most out of your spreadsheets. No dark magic here, just good advice!

1. Use Pivot Tables

We've talked a bit about pivot tables before, but I cannot emphasize enough how powerful they are for data analysis.  However, they also have a reputation for being understandable only by very advanced Excel users.  This is not true!  They are powerful, and it is true that there are many features. Even better, you can get started easily and learn as you go!  Check out some of the guides below to get started.  Is your data not set up to easily flow into a pivot table?  Drop us a line and we can help!

2. Index-Match

Looking up data from one part of your spreadsheet to use somewhere else is common.  Many people make use of VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup), which are easy, single-formula options for looking up values.  However, there are some limitations. For example, in VLOOKUP, your lookup value must be to the left of the value you want to use.   A more powerful and flexible approach combines the INDEX and MATCH functions. To help you choose when to use which function, check out this excellent explanation from Exceljet.  Ready to get into even more advanced lookups?  Check out all of Excel's lookup functions!

3. Data Tables Instead of Ranges

Excel's data tables are a powerful way to organize large sets of data while ensuring that your formulas remain consistent throughout.  I talked a bit about them in a previous post (here), but they are worth taking another look at!  One of the biggest advantages of data tables is their built-in use of named ranges and structured references. Referencing columns and tables by name allows you to easily read the formula and better understand what it is doing and what data it is using.  Here's an example from an inventory tracking sheet that I've worked on.  Without knowing anything else about the spreadsheet you can probably get a reasonable idea of what the formula is calculating just by the names used!

Table Formulas.PNG

Want to learn more about tables?  Check out Microsoft's guide here.

Excel provides so many ways to help you manage your spreadsheet data!  I hope you find these tips helpful.  Contact us today to learn more about how we can help you build powerful and efficient spreadsheets for your organization.

Improving Access to Capital Via Crowdfunding

Also, what kind of CEO do you want to be, get the right insurance, and don't settle for bad customers

  •  Equity crowdfunding platforms are a groundbreaking alternative to traditional venture capital. Platforms, such as Republic started by Kendrick Nyugen, are leveling the playing field for women and minorities whose access to venture capital has been hampered by a lack of strong connections with the right people, among other factors. Equity crowdfunding provides a powerful work around to this challenge by giving "equal access to anyone who wants to invest in vetted startup organizations".  In fact, recent changes in SEC rules have empowered "members of the public to invest in early-stage companies without being an accredited investor, while also including certain limitations to protect these new investors."  It's an exciting time for start-up businesses and everyday investors.

  • Do you have the right insurance? Is it enough?  Does reading about it put you to sleep?  Your personal insurance is not enough! Here's a summary of the risks that small businesses face and the types of insurance you should consider.  Find out what coverage you should have and get it before disaster strikes!

  • Star CEOs like Elon Musk of Tesla are attractive visions.  But, they do not come without their risks.  Musk recently cost Tesla $20 million in fines to the SEC.  Papa John's CEO John Schnatter' comments about NFL players kneeling have caused the company's share price to drop and endangered sales.  At the same time, rockstar CEOs can bring much needed hype and attention to growing companies.  The trick is balancing the risks and the benefits.  For entrepreneurs, considering the role they wish to play as CEO and how they will structure their company to help them achieve that is an important part of scaling up.  This Harvard Business Review article gives some suggestions for creating a board that has the power to balance out a star CEO and help keep a company on an even keel, regardless of the CEO's qualities.  As you envision your business's growth, ask yourself: "What sort of CEO do I want to be"?

  • Building something that customers want is the cornerstone of a successful business.  But, figuring out what they want can be difficult.  These entrepreneurs talk about how they figured out what their customers wanted.  The common theme?  Talk to your customers!  Tell them about your ideas and get their feedback.  You need to find something that resonates with customers and gets them excited to buy your product.

  • "Don’t settle for clients who pay late, ask for discounts and deals, waste your time and don’t do the work."  Early on, most entrepreneurs will take any customer they can get.  However, you will quickly come to realize that there are some customers who are simply not worth the trouble.  Instead of suffering through the difficult clients, tune your business to attract the best clients.  By building a first rate online presence, providing value throughout your offerings (even the free ones!), and pricing appropriately you can attract the best customers and forget about the difficult customers!

Ready to begin your startup adventure? Let us help you get your idea off the ground! Check out our Start Up services today!

The Case for Running Lean

When the lean startup movement kicked off around 2009, it was all the rage.  However, lately, I have been seeing articles (like this one from Entrepreneur) questioning the value of running lean and advising against using the lean startup methodology.  While many of the arguments have merit, I think the main problem is in how people implement the lean startup methodology rather than issues with the methodology itself.  Let's unpack some of those arguments and explore what they mean for entrepreneurs and how they can use lean startup methods successfully.

Testing Your Vision 

Author Nishta Tripathi mentions the co-founder of Like a Little, Prasanna Sankar, who used lean principles to start up but ultimate shut down after four years.  Over the course of those years, Sankar applied the lean approach by continually switching "ideas until a small group of users showed interest."  Essentially, he was chasing success without any particular vision for what that success looks like.  In reflecting on his experiences, Sankar concluded that you need deep knowledge and commitment to your idea (e.g. an overarching vision) and to your market in order to persevere through the tough times and build a successful business.  For him, the lean startup approach lacked this focus on vision, knowledge, and perseverance.

I agree with Sankar's contention that a deep commitment to what you are doing is essential and I also believe it is compatible with lean startup methods.  While switching strategies to find the one that resonates with customers is central to lean startup methods, those changes must be constrained within an overarching vision. If you have a broad vision for your product and it is aligned with your passion and your domain knowledge, you can use the lean startup methods to test various strategies within the scope of that vision in order to optimize the resulting product.

Pivoting vs. Persevering

Another criticism of the lean startup methodology is that it can be prone to false negatives, causing entrepreneurs to abandon ideas when they really need to persevere through the down times.  However, at its core, lean startup methodologies are about learning and putting yourself in the best position to make the best decisions in the face of extreme uncertainty.

Tripathi asks when facing a hurdle, "How do you know whether its a problem worth solving if you pivot away every time you stumble?"  To me, when a product is stumbling, the key is to understand why.  One of the core tenets of the lean startup methodology is getting out and talking to customers.  So, a company's metrics should tell them where they are struggling. Then discussions with customers or potential customers should help them understand why.  It is only with that understanding that an entrepreneur can make an informed decision between pivoting and persevering.

Using the Right Test at the Right Time

Another sticking point is A/B testing.  Tripathi notes that A/B testing "doesn’t produce significant results unless your traffic is meaningful."  I certainly agree. A/B testing can be a powerful tool, particularly as a product scales up.  However, when a startup is just starting, I find that the process outlined in Ash Maurya's book Running Lean is more effective.  Maurya's process relies on customer interviews to verify that the problems you aim to solve are actually problems worth solving, that your solution is resonating with your target customers, and that your message is convincing people to buy.

During this initial startup time, there is an extremely heavy emphasis on talking to potential customers, listening to them, and learning from them.  All qualitative feedback.  It is not until very late in the process, when you begin testing product-market fit, that you start collecting any sizable quantitative metrics.  Additionally, Maurya specifically separates the initial startup (from idea to proven product-market fit) from the scale up that follows.  During scale-up, optimization is the name of the game.  This is when A/B testing and other methods that rely on much higher levels of traffic become effective and useful.  So, once again, success with the lean startup methodologies relies on applying its principles appropriately based on the stage of your company's growth.

What about Physical Products?

Physical products present a unique challenge to the lean startup methods.  With software, rapid iteration and changes are fairly inexpensive.  Iterating physical products can be much more difficult given the increased expense and time required to produce them.  Additionally, many larger physical products are significantly more capital intensive to manufacture.  That said, advances in rapid prototyping, 3D printing, and on-demand production are reducing these difficulties.

In addition, while the challenges of rapidly iterating a physical product are clearly present, there is more to the product than just the physical piece being sold.  As Maurya notes, "Your price is part of your product."  So too is your marketing message and how you support and interact with your customers.  These are all candidates for iteration and testing following the principles of lean startup methods.  You should still be talking with your customers about their problems, measuring how customers are interacting with your website, and how your value proposition is resonating.

Tripathi uses Tesla as an example of a company that could not use lean startup methods, but I disagree.  Certainly, the pace of iteration is slower than for a software startup, but there is clear evidence of lean startup principles at play.  Elon Musk had a vision for an electric car company that would change the nature of cars.  However, he did not launch an electric car for the masses from the start.  Instead, Tesla developed the Roadster, an expensive electric sports car aimed squarely at people who had extra money for a car that was a toy.  These were early adopters, and through them, Tesla was able to develop and refine its technology and marketing before expanding to a premium luxury sedan, a premium luxury SUV, and only just recently a nearly mass-market sedan.  Tesla also makes use of constant improvements to the software in its vehicles, allowing it to continually refine its product as it gets feedback from its customers.  These are all examples of lean startup principles applied to a very capital intensive business.

The bottom line is, lean startup methods are a means to an end: starting a successful business.  Part of the challenge for you, the entrepreneur, is to apply those principles in a way that make sense for your business and your product.  This is not always an easy process.  We can help.  Contact us today to learn more about how we can help you turn your idea into a successful business.

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.