Answering Big What-Ifs: The Interactive Pro Forma

September 11, 2017 ENT650 – Adv. Entrepreneurial Finance, Entrepreneurship, Finance Comments (14) 291

ENT 650 - Week 3

Startup entrepreneurs face a daunting challenge in forecasting cash flow, profitability, and return on investment. It can be so overwhelming that many just ignore the numbers and jump right into developing a product or service. Certainly, a refined product or service is a critical early step in launching the entrepreneurial venture; however, understanding the financial aspects of the business, including the inputs to the product or service development is paramount to forecasting cash flow and business profitability, is equally important.

In business, particularly in the start-up phase, “pro forma” financial statements provide a way for the entrepreneur and potential investors to see the financial forecast of a business given a set of assumptions. The pro forma financials will include a set of assumptions on which the numbers were based, a cash flow statement, an income statement, and a balance sheet statement. Together, these statements provide an approximation of business performance considering the assumptions. Most investors place little value on the numbers a start-up provides because the core assumptions may be inaccurate (Rogers, 2014).  And if the assumptions are inaccurate, the financial performance will change. One of the best ways to understand how assumptions change the financial performance of a business is to build an interactive pro forma.

The interactive pro forma will allow an entrepreneur to quickly change assumptions and easily see the possible financial impact on the business as those assumption flow through the other financial statements. Once set up, it allows an entrepreneur to play a “What-if” game with assumption inputs and watch how those changes affect the business performance. The interactive pro forma is also a great tool to use when seeking outside investment. If the assumptions are challenged by an investor, they can be modified in real-time, and those modifications flow out to the other statements. This provides an instant view of the new business financials once new assumptions are in place.

Spreadsheet tools make building an interactive pro forma easy, although it does take some knowledge of the tools and a few hours to set up the models. Most of the time spent should be given to considering the assumptions. Assumptions are the most important part of the model and where entrepreneurs should put the bulk of their time.

Here’s one way to build an interactive pro forma:

  • In a spreadsheet program, create a new workbook with four tabs. Label each tab as follows: Assumptions, Cash Flow Statement, Income Statement, and Balance Sheet.
  • On the Assumptions Tab, list all of your assumptions about the business. Depending on the business one might choose product pricing, sales figures, monthly costs of operation, depreciation, taxes, insurance, etc. Make sure everything that could affect the business costs or revenue structure is included.
  • On the Cash Flow Statement tab, include the beginning cash balances, your sources of cash, and the uses of cash for each month. Use the assumptions to drive the revenue (sources) and expenses (expenses) on this tab. Note that the cash that’s left over at the end of each month becomes the Beginning cash balance for the following month. The Cash Flow Statement shows how the business will use the money it receives each month.
  • The Income Statement tab pulls data from the Cash Flow Statement tab but formats it differently. The Income Statement in a pro forma shows whether the business is profitable for a period by looking at the revenue and expenses based on the original assumptions (and cash flow).
  • The Balance Sheet pulls data from the Cash Flow Statement and the Income Statement to provide a look at the assets, liabilities, and equity of a company at a point of time. It helps the entrepreneur better understand what the company owes (liabilities), what it owns (assets), and the equity held by shareholders.

The above points are over-simplified. The idea is to give an overview of the process.  Each entrepreneur should develop his or her own pro forma to understand the specifics of his or her business better.

This said I have provided an example interactive pro forma workbook for an online publishing business for readers to download and explore. Some may have the skills and abilities to create a similar model, but others may need the help of a financial professional.

Using an interactive pro forma will help an entrepreneur get a better handle on his or her business by allowing the exploration of What-If scenarios what might impact the business. It is much better to explore these scenarios in advance of launch and develop contingency plans than it is to encounter the problems in “real time” without possible solutions waiting.

 

 

A few notes on this model:

  • This is an example for use as is. It is not supported in any way. It is not intended to be a tool to use without customization based on the specifics of an entrepreneurial venture.
  • This interactive pro forma is only an example to give the reader an idea of how such a tool can be developed. It is not based on a real business. I compiled this model for a graduate class, but I have developed similar models for entrepreneurial ventures. Each business venture is different, and so is the pro forma that is prepared for that venture.
  • On the Pricing Assumptions tab, all of the “Bold Blue” text areas can be changed to demonstrate how the interactivity might work. No other data can be changed.
  • The best way to explore this model is to look at the other statements before changing anything, then change one thing and see what effect it has on the model.
  • In this workbook, I have included an additional tab to for calculations of revenue and expenses based on the pricing assumptions. This worked easier for me to build the calculations, but others might do it differently.
  • Formulas can be seen in each of the cells (mouse over it), but only the values in the “Bold Blue” text area can be changed.

 

Reference

Rogers, S. (2014). Entrepreneurial Finance: Finance and Business Strategies for the Serious Entrepreneur (3rd ed.). New York: McGraw-Hill Education.

___

Featured Image Source: Getty Images/blackred

David Harkins is a serial entrepreneur, which is a more professional way of saying he is still trying to figure out what he wants to be when he grows up.
When not working for himself, he has had a fulfilling career in marketing, advising both large and small companies including several in the Fortune 500 and many of America’s largest nonprofit organizations. In his spare time, he consults, speaks, writes, hikes, explores, and creates art. Although, not necessarily in that order. Connect with him on social media below:

14 Responses to :
Answering Big What-Ifs: The Interactive Pro Forma

  1. cole says:

    David,

    I agree with “Using an interactive pro forma will help an entrepreneur get a better handle on his or her business “.
    As I continue to master my own- I am learning things- becoming more specific and detailed oriented. You are so correct.

    1. Thanks, Cole.

      I’m glad you’re learning something here to help you master your own finances as a budding entrepreneur.

  2. David,

    Thanks for such a great introduction to Pro Forma and the “What if” game of any business. Your steps were easy enough for us to understand how to set up the excel workbook. I also like the idea that new business owners can download a copy from you to get started.

    This was not easy at all for me, I struggle with this part of my business. Again, when I started my business, I had no real idea of what I was doing. BUT, boy do I now!! This ME program has opened my eyes for sure!

    Thanks for these great articles!

    Christina

    1. Thanks, Christina.

      I am sorry you struggled so much with this assignment. I hope you found the example useful and helpful in understanding how to structure your own pro forma.

      -David

  3. mnjimison says:

    David,

    Pro-forma assumptions are very helpful to determine projections, I also read in Entrepreneurial Finance that in the interactive pro-forma testing what-ifs you should come up with a pro-forma for best case scenario, worst case scenario and most realistic scenario and compare and contrast the three different spreadsheets to determine how to manage and prepare for each scenario. Thank you for the sample spreadsheet it is very helpful.

    Thanks,

    Mackensie

    1. My pleasure, Mackensie.

      I am glad you found the example useful. I have heard from several investors that they like these types of pro forma’s, too, because they can apply their own assumptions and see what happens.

  4. tcomer1 says:

    David,
    I think it’s neat that you provided a thorough example of ProForma for your readers. Preparing ProForma Financials can be a daunting task when creating the layout and coming up with the assumptions. Assumptions require a lot of research in an industry and industry’s customer. I agree with Rogers that typically the entrepreneurs will overstate their returns to show their company in a favorable light. However, it is not helpful to entrepreneur when they have to justify their projections and don’t have a track record or an industry analysis to back them up. Assumptions are just that, assumptions. One may assume their business will do well because they consider themselves to have a competitor advantage but assumptions also need to take in consideration of the unforeseeable.

    1. Thanks, Toshia.

      I think people really need to see something to get a grasp of how it’s done. I am hopeful by providing this example, my readers can gain some insights. You are correct in that some entrepreneurs do overstate their projections and returns for a favorable pro forma. But that’s not helpful to the entrepreneur. There no value in letting hope be the overarching the strategy for a business venture.

  5. Hi David,
    The Pro Forma I did has been one of the most difficult assignments for me. My experience is lacking with all these financial documents. I see the value and the necessity of the forms and after filling mine in I’m concerned about viability. I think I need to find a mentor to help me work with my numbers and see where I can make headway. Like Brad, I love all the color in your example so much easier to read. I also did not split my Proforma this time.
    Cece

    1. Thanks, Cece.

      Financial statements can be challenging, but as we’re learning they are essential tools in the entrepreneurial toolbox. Hang in there. The pro forma is doing what its supposed to be doing if you’re concerned about your venture’s viability. It helps you to think more deeply about what you might need to do to increase income or reduce expense to improve the possibility of success. It’s better to find this out on paper, first. Right? I’m glad you like the color. I think it makes it easier to read, too.

  6. I like the layout. The previous example split year one from 2 and was a hot harder to maintain. This is simpler. And the coloring really helps too. It would be great to add something that would calculate ratios. Graphs too would be neat. Perhaps a web version. I think I’m seeing a valuable product in its own. Thanks for sharing

    1. Thanks, Brad.

      I didn’t like that previous layout either. It didn’t make sense to me. And I think color helps, too. I took your suggestion and created the ratio calculators. You can find links to the various ratios and a calculation spreadsheet here: http://www.mrharkins.com/use-financial-ratios/

      -David

  7. Hello David,
    I see you say pro forma will help an entrepreneur get a better handle on his or her business. I agree with that but I think the hardest thing I have done in this class is the Pro Forma on a service type of business. I do not truly think it reflects an real or true 2 year figures because it is all in getting the clients to sell their home etc…
    If it was just renting homes I can figure that. Or selling a product might make more scene. But to guess when it is sales in a 100% commission large ticket item is not so simple.
    Mary

    1. I would agree it is hard to do a pro forma for service business. It is all in the assumptions. And the profitability comes from understanding the hours you have to put in to get the job done. For real estate, I think you can take the average number hours it takes to get a buyer in a house (or property sold), and then take an average of the commission for those transactions. With those percentages, you should be able to determine an average number of hours it takes to generate a certain amount of income. Then, I think you have to back out how many leads it takes to get a client. From there, you can look at averages again to determine how much time (and money) it might take to generate the leads you need. I’m a broker associated with Keller Williams, and they offer some excellent training around this that can be helpful to developing pro forma. If I can help further, please let me know.

Leave a Reply

Answering Big What-Ifs: The Interactive Pro Forma

by David Harkins time to read: 4 min
%d bloggers like this: