Help for Business Plan Financials, v3.1
7/27/2017
Planning Shop Help for Business Plan Financials
2
Table of Contents INTRODUCTION …………………………………………………………………………………………………………………………….. 3
About Excel ……………………………………………………………………………………………………………………… 3 Have Questions? ………………………………………………………………………………………………………………. 3
GETTING STARTED ……………………………………………………………………………………………………………………….. 4 Excel 2013 or Excel 2016 for Windows ………………………………………………………………………………… 4 Excel 2011 and 2016 for Mac ……………………………………………………………………………………………… 5 Other Versions of Excel ……………………………………………………………………………………………………… 8
BACKING UP THE WORKSHEETS ……………………………………………………………………………………………………….. 9 NAVIGATING THE WORKSHEETS ……………………………………………………………………………………………………… 10
Excel 2013 & 2016 for Windows and Excel 2016 for Mac ……………………………………………………… 10 Excel 2011 for Mac ………………………………………………………………………………………………………….. 10 Where to Start ………………………………………………………………………………………………………………… 10
ENTERING DATA …………………………………………………………………………………………………………………………. 11 To enter data into cells: ……………………………………………………………………………………………………. 11 To save your worksheets: …………………………………………………………………………………………………. 11
CHANGING DATA ………………………………………………………………………………………………………………………… 12 To change a number in a cell: …………………………………………………………………………………………… 12
HELP BALLOONS …………………………………………………………………………………………………………………………. 13 ABOUT OUR SUPPORT FOR MAC VS. WINDOWS …………………………………………………………………………………. 14
Limitations of Excel 2011 for Mac ………………………………………………………………………………………. 14 CUSTOMIZING THE WORKSHEETS …………………………………………………………………………………………………… 15
To Unprotect Worksheets: ………………………………………………………………………………………………… 15 FREQUENTLY ASKED QUESTIONS (FAQ) ………………………………………………………………………………………….. 16
Planning Shop Help for Business Plan Financials
3
Help for Business Plan Financials, v3.1
Introduction Welcome to PlanningShop’s Business Plan Financials product! These Excel-based worksheets will help you create clear, thoughtful financial projections and statements for your business plan. We’ll take you step-by-step through much of your financial planning, ultimately providing you with printer-ready financial statements and charts. For a thorough look at the entire business planning process, we recommend our companion book, Successful Business Plan: Secrets & Strategies by Rhonda Abrams. It is available from our website, www.PlanningShop.com.
From a potential investor’s point of view (whether it’s a bank, a venture capitalist, or a friend or relative), accurate, realistic numbers are the most important part of a business plan. It is imperative that you carefully consider every number you put in your plan—and how each number will affect other numbers!
This is where you’ll find the real benefit of creating your financials in a spreadsheet. At any time in the process, you can skip forward or backward, change a number here, adjust a number there, and see how it affects your final statements.
As you get your business up and running, you can return to your worksheets and modify your numbers to reflect the realities of your business and better project future growth.
About Excel The Business Plan Financials are a set of pre-programmed templates designed to work with your copy of Microsoft Excel, a popular “spreadsheet” program. Excel is a deep and complex application, chock full of menus, buttons, tools, functions, and features. Fortunately, you won’t have to worry about most of these—in designing Business Plan Financials, we’ve harnessed a lot of Excel’s power for you, laying out the financial statements, pre-programming the calculations, and designing the charts—so you can focus on the important stuff: your business and your numbers.
If, however, you find that you’d like to dig into Excel’s power yourself, Excel comes with an extensive help system under the “Help” menu on Excel’s main menu bar.
Have Questions? If you have any questions about the product, email us at [email protected]. In your email, please include the following:
1. Your operating system (examples: Microsoft Windows 10, Windows 8, Mac OS X 10.13, etc.)
2. Your Excel version (examples: Excel 2013 or 2016 for Windows; or Excel 2011 or Excel 2016 for Mac)
3. The version of the Business Plan Financials workbook you are using. You can find this by clicking on the PlanningShop tab of the Excel ribbon, selecting the “Business Plan Financials Help” drop- down menu, and selecting the “About” menu item.
Planning Shop Help for Business Plan Financials
4
Getting Started When opening the Business Plan Financial worksheets in Excel on Windows or in Excel 2016 on Mac, you need to make sure Excel Macros are enabled. You’ll see a warning message if they are not.
Excel 2013 or Excel 2016 for Windows A Security Warning will appear in the top 1/3 of the Excel window:
Click on the “Enable Content” button. Your workbook should now be ready to use.
If you do not see the above “Security Warning”, but instead find yourself on the “Warning” worksheet, do the following to enable Macros:
1. In the upper left corner of the Excel Ribbon, click the “File” tab
2. Click “Options” in the lower left corner
Planning Shop Help for Business Plan Financials
5
3. Select “Trust Center” from the lower left, and then click on the “Trust Center Settings….” In the lower right corner.
4. In the Trust Center window, click on “Macro Settings” on the left side, and then under “Macro
Settings”, select the radio button for “Disable all macros except digitally signed macros”.
5. Click OK twice. 6. Your workbook should now be ready to use with Macros enabled, and you should be on the
“Welcome” worksheet.
Excel 2011 and 2016 for Mac When opening the workbook in Excel on Mac, a pop-up window will appear.
Planning Shop Help for Business Plan Financials
6
Here is how it looks in Excel 2011:
Here is how it looks in Excel 2016:
In either case, select “Enable Macros”.
Your workbook should now be ready for use.
If you do not see the above “Security Warning”, but instead find yourself on the “Warning” worksheet, do the following to enable Macros:
1. In the upper left corner of the Excel menu, select “Excel” and then “Preferences” from the drop- down menu.
Planning Shop Help for Business Plan Financials
7
2. On the Excel Preferences pop-up window, click on the “Security & Privacy” lock icon, within the “Security & Privacy” section.
3. Under “Macro Security”, you’ll probably find that “Disable all macros without notification” was checked. Instead, change it to either “Disable all macros with notification” or “Enable all macros”.
Planning Shop Help for Business Plan Financials
8
Other Versions of Excel Version 3.0 of Business Financial Worksheets only works with Excel 2013 or 2016 for Windows, and Excel 2011 or 2016for Mac. If you have other versions of Excel, contact PlanningShop to inquire about the availability of suitable versions of the Business Financial Worksheets.
Planning Shop Help for Business Plan Financials
9
Backing up the Worksheets Before you do anything else, make a backup of the unused Worksheets. That way, if at any point you decide that you want to start over with a fresh set of Worksheets, you can use the backup.
1. Open Business Plan Financials in Excel as you normally would 2. Click on Excel’s File menu and select “Save as…” 3. Type in a new name for the Worksheets (e.g., “MyFinancialProjections.xlsm”). NOTE: make sure
the file extension is .xlsm, not just .xls. 4. Click the Save button
You are now working in the copy of the Worksheets called “MyFinancialProjections.xlsm” and the original file is safely preserved on your computer. From now on, use this new file for creating your projections. If at any time you need to open a fresh set of Worksheets, you can open the original, unused copy.
Note that in an emergency you can find an “original condition” file, “ORIG_PlanningShop_BizPlanFin (3.1) ”, in the directory where you installed Business Plan Financials. In your download, we provided two copies of the original worksheet, specifically to help with these types of situations.
Planning Shop Help for Business Plan Financials
10
Navigating the Worksheets When you launch your copy of Business Plan Financials, a new toolbar may appear in Excel’s menu area above the worksheets. This toolbar helps you navigate through the various charts and worksheets included with the product.
Excel 2013 & 2016 for Windows and Excel 2016 for Mac The Planning Shop Ribbon/toolbar isn’t displayed when you first start up Excel. You need to click on the “PlanningShop” tab along the top edge of the Excel Ribbon to make it visible:
(NOTE: the exact items and appearance vary depending on the exact version of Excel, but the PlanningShop tab is always to the far right of all the other tabs).
From the PlanningShop Ribbon, you can navigate through the worksheets (much like turning pages in a book), click on the green arrows. You can also jump directly to any worksheet by selecting it from the Worksheets menu or to a chart by selecting it from the Charts menu.
Excel 2011 for Mac Unfortunately, we were unable to provide a navigation toolbar/ribbon that works with Excel on the Mac. This is because Microsoft does not allow third-party developers like the Planning Shop to add custom tabs to the Excel 2011 Ribbon on Mac.
So, to navigate the worksheets you’ll have to use the standard Excel worksheet tabs. You’ll find a tab for each worksheet along the bottom edge of your Excel window – click on the tab to jump to a particular worksheet. Note that there are 23 worksheets in total, and not all tabs will be visible at once.
Where to Start Start with the Setup and Assumptions worksheet where you’ll enter some basic assumptions about your business.
Remember: You can ALWAYS go back and change any information you enter in a worksheet. Nothing is set in stone. If, for example, you’re working on the Professional Services worksheet and you realize that you didn’t budget for enough staff in the Marketing Budget worksheet, you can go back (using the blue arrow buttons or the Worksheets menu), make the adjustment, and return to the Professional Services worksheet.
Planning Shop Help for Business Plan Financials
11
Entering Data Each worksheet is composed of “cells” containing either black text or blue text. You need only to enter data into the blue cells—the black cells are automatically calculated for you.
To enter data into cells: 1. Click ONCE on the cell to highlight it 2. Type in your data. Any existing data in the cell will be replaced with the new data 3. Press the Enter or Tab button on your keyboard to confirm the entry and move to the next cell
In many cases you will find that when you enter a number in a cell, the number automatically appears in all the subsequent cells on that line. This is done merely as a convenience for you—you can always overwrite those auto-copied numbers as necessary.
If there are lines or cells that don’t apply to your business, just leave them set to zero.
It’s important to frequently save your worksheets to protect them from data loss.
To save your worksheets: Simple. Go to Excel’s File menu and select Save.
Planning Shop Help for Business Plan Financials
12
Changing Data Once you have entered data into the worksheets, you can always go back and make changes. This is one of the advantages of developing your financial projections electronically.
In fact, this technique can be particularly useful for “testing” potential variations or alternate scenarios in your company’s financial performance. For instance, you may want to see what happens if sales are lower than you anticipate. Go to the Sales Projections worksheet and change the numbers. Then see how this change affects your Income Statement, Cash Flow, and Balance Sheet.
To change a number in a cell: 1. Use the Worksheets menu or the blue arrow buttons to navigate to the worksheet you want to
change 2. Activate the cell you want to edit by clicking on it once 3. Type in the new number and press the Enter or Tab key on your keyboard 4. To save your changes, select Save from Excel’s File menu
Planning Shop Help for Business Plan Financials
13
Help Balloons
As you work, keep an eye out for red triangles in the corner of some cells. These triangles indicate the presence of a “help balloon” which will popup when you roll over the triangle, presenting you with additional information and tips about the section you are working on.
Planning Shop Help for Business Plan Financials
14
About Our Support for Mac vs. Windows Business Plan Financials are supported on the following platforms:
1. Microsoft Windows 10 with Microsoft Excel 2016 2. Microsoft Windows 8 and higher with Microsoft Excel 2013 3. Mac OS X with Microsoft Excel 2016 for Mac 4. Mac OS X Lion (10.7) with Microsoft Excel 2011 for Mac
No matter which platform you use, you use the same Excel file.
If you have a version that is not supported, contact the PlanningShop ([email protected]) to inquire about the availability of other versions of Business Plan Financials that run on these platforms.
Limitations of Excel 2011 for Mac All versions of Business Plan Financials will produce high quality, detailed financial information for your business plan. However, there are some slight limitations when working with the Business Plan Financials workbook in Excel 2011 for Mac. These limitations don’t affect the data in your business plan, but they do affect the user-friendliness of the Business Plan Financials workbook.
Microsoft does not support customization of the Excel 2011 toolbar on Mac, so there is no PlanningShop Toolbar.Instead, you will need to navigate between worksheets and charts using the Excel worksheet tabs (along the bottom edge of the workbook).
Planning Shop Help for Business Plan Financials
15
Customizing the Worksheets
Business Plan Financials are designed to make the process of creating financial projections as easy as possible, even for those who have never used Excel. Therefore, certain restrictions, limitations, and protections have been designed into the product.
If, however, you have substantial experience with Excel and would like to alter the worksheets to meet your needs, you can do so by “unprotecting” worksheets.
WARNING: Working in an unprotected worksheet can potentially damage the product’s functionality and integrity. Do not unprotect worksheets unless you have experience with and knowledge of Excel and Excel formulas! The PlanningShop will not provide technical support, replacements, or refunds for Electronic Financial Worksheet products that have been unprotected. Please review the License Agreement for further information.
To Unprotect Worksheets:
1. Navigate to the worksheet which you wish to unprotect 2. Select the “Review” tab from the menu along the top of the Ribbon, and then click the “Unprotect
Sheet” (no password is required). 3. For Excel 2011 on Mac, select the “Review” tab from the Ribbon, go to the “Protection” group, and
then click on “Sheet.”
Once the worksheet is unprotected, you’ll have free rein to alter the worksheet’s appearance, formulas, and functionality. For more information on using Excel, see the Excel Help menu.
If you would rather not make the changes yourself, PlanningShop can provide customizations for you. Please contact us for rate information at [email protected].
Planning Shop Help for Business Plan Financials
16
Frequently Asked Questions (FAQ)
Q. How do I figure out what information to put in all these worksheets?
A. These worksheets are designed to work hand-in-hand with The Successful Business Plan: Secrets & Strategies, by Rhonda Abrams. Next to the title text of each worksheet you’ll find a help balloon (roll over the red triangle to make the balloon appear) which will tell you where in the book you’ll find information about how to use the worksheet.
Q. Is there a certain order to the worksheets that I must follow when entering my data?
A. Not at all. You can enter information into the worksheets in any order you wish. We’ve placed them in an order that we feel will make the most sense for you as you develop your financials, but this is merely a convenience, not a rule.
Q. The Sales Projections worksheet provides space for only ten “product lines.” I have more products than that. What should I do?
A. Although you want carefully researched, well-developed financials in your business plan, it’s important that your plan not overwhelm readers (including yourself!) with too much data. Look at all the products or services you plan to sell, and see if you can find ways to strategically consolidate them into a few major product lines that will be most useful for financial analysis.
For example, if you are opening a small restaurant, you might group your products as “wine & beer,” “lunch menu,” “dinner menu,” and “take-out.” For many restaurants, liquor sales are a crucial revenue stream and would justify their own product line. Many smaller ethnic restaurants have differently- priced menus for lunch and dinner, and would need to project sales accordingly. Finally, for some restaurants, take-out sales can exceed eat-in sales. Classifying your product lines this way would allow for much stronger projections and analytical opportunities than, say, listing dozens of menu items.
Q. There are “#NAME?” signs showing up in some of my worksheet cells instead of numbers. What do I do?
A. If you didn’t (or forgot to) enable macros, the “#NAME?” signs appear in cells on the Income Statement, Cash Flow, Balance Sheet, Key Ratio, and Break Even worksheets.
Refer to the “Getting Started” section of this document to learn how to enable macros.
Q. There are “#####” showing up in some of my worksheet cells instead of numbers. What do I do?
A. The “###” signs appear in a cell when the number that should be there is too long to fit within the cell. We have set the cells to comfortably hold seven digits (enough for most of our customers, but not so large that the worksheets won’t print on standard size paper). If you have ambitious plans and require more digits, here’s what to do:
1. Unprotect the worksheet on which the “####” appears. Go to Excel’s Review tab on the ribbon, and select “Unprotect Sheet.”
2. Activate the column headers. Go to the Tools menu and select the “Options…” item. Click on the “View” tab. Put a check mark next to the “Row & column headers” item. Click “Okay.”
3. Now, to widen a column, place your mouse pointer at the top of the worksheets, within the column headers row (the row with the “A,” “B,” “C,” etc.). Move your mouse between two columns, click and drag to the right. You’ll increase the size of the column.
Planning Shop Help for Business Plan Financials
17
4. Repeat this process for any other columns on the worksheet you wish to expand.
5. Re-protect the worksheet (Excel Ribbon à Review à Protect Sheet). Do not enter a password.
Q. There are “#NUM!” errors showing up in some of my worksheet cells instead of numbers. What do I do?
A. This indicates that a calculation error is occurring somewhere in the worksheets. The most likely cause is that on the Capital Expenditures or Capital Investments worksheets you have entered a date that falls prior to the start date of your company, as entered on the “Setup & Assumptions” worksheet. If this is the case, you should see errors on the CapEx or CapInvest worksheet, and the Setup worksheet.
If you go back and change the dates on transactions on the CapEx or CapInvest worksheet to fall after your company’s startup date, the #NUM! errors should disappear.
In other cases, the error can occur if data of the wrong type has been entered into a cell. For instance, if you have entered alphabetic letters into a cell that is designed to accept only digits, a #NUM! error will occur in all the other cells that depend on that number for calculations. To fix this error, check your entries to make sure that you have entered valid data.
Q: I’m getting “#VALUE!” errors in some of my worksheet cells instead of numbers. What do I do?
A. There are many ways the #VALUE! error can occur. You might have accidentally changed a cell’s formula to something that is not valid Excel syntax. Alternatively, you might have entered text into a cell where a number is expected, and that cell is later referred to by formulas in other cells.
We recommend that you first make sure you entered numeric values in all cells that expect numeric values. Also, make sure you haven’t entered ranges of values like “30-40” in any cells. If that does not work, we recommend that you quickly see if you can find any obvious cells where you might have inadvertently changed the formula to something that mixes formulas and operators (+ – / *), such as the incorrect =SUM(A1 + B1 + C1) instead of the corrected =SUM(A1, B1,C1).
If neither of the above approaches works, then we recommend you start over from a blank set of Business Plan Financials. Find the “ORIG_PlanningShop_BizPlanFin(3.1)” file in the directory where you installed Business Plan Financials, copy it, and then start working on your financials in the copied workbook (do not edit the original workbook).
Q: I’m getting some “read-only” errors that are similar to the below screenshot.
A. This kind of “read-only” error occurs when you try to edit worksheet cells that are not meant to be edited, or if you try to add new cells, columns, workbooks, or worksheets.
We strongly recommend against making these kinds of changes to your workbook unless you are very experienced with Excel. To learn how to do so without triggering the read-only error, please refer to the “Customizing Worksheets” section this PDF.
Planning Shop Help for Business Plan Financials
18
Q. My Worksheets seem slow—every time I enter a number, it takes a moment for the program to respond. How can I make it go faster?
A. The Worksheets are set up to “recalculate” after a change is made to any cell. This means that as soon as you make a change, all the other dependent numbers on that worksheet and all other worksheets are updated to reflect the change you just made. Because every number you enter will affect other numbers elsewhere in the program, this recalculation can take a moment or two, depending on your computer’s processor speed.
But there is a solution! You can turn off this “auto-recalculation” feature, and you’ll find that entering data is nice and fast. The downside? All the other numbers in the program won’t automatically update when you make a change—if you want to see how your changes have affected everything else, you’ll need to tell Excel to recalculate by pressing the F9 button on your keyboard.
In Excel 2013 & 2016 for Windows and Excel 2016 for Mac:
1. Select the Formulas tab from the Excel ribbon. 2. Click on the Calculation Options button 3. Select “Manual”
In Excel 2011 for Mac:
1. Select the Formulas tab from the Excel ribbon. 2. Click on the “Settings” button underneath the Calculations group on the ribbon. 3. Select “Manual”
Remember to press F9 on your keyboard to update your worksheets after you’ve entered your data.
Q. I’d like to make changes to the structure or layout of the worksheets. How can I do that?
A. You can certainly make changes if you’d like, but keep in mind that any changes you make could potentially affect the integrity of the built-in calculations. For more about customizing the product, see “Customizing the Worksheets.”
Q. I don’t need all the rows that are available on some of the worksheets, and I’d like to delete them so my financials look nice when I print. How can I remove rows without compromising the calculations the program performs for me?
A. Removing rows from any of the worksheets can, indeed, cause problems on other worksheets, because data is passed back and forth “behind the scenes.” You never know if one of the rows you are deleting will affect data calculations elsewhere. However, one trick you can use is to “hide” rows that you don’t need. Hidden rows are still available to the program, but are not visible to users or on printouts.
Here’s how to hide a row:
1. Unprotect the worksheet on which the row you want to hide appears. Go to the Excel Ribbon, and select the Review tab. Then, on Windows, click the “Unprotect Sheet” button. On Mac, click the “Sheet” Button in the “Protection” group.
2. Make sure that you have zeroed out any numbers that appear in the rows you are hiding— remember, the program will still draw on data in hidden rows.
3. Select any cell in the row you want to hide. If you want to hide a stack of rows, draw your cursor down a column, selecting multiple rows.
4. On Windows, right click on your selected rows to pop-up the context menu. Select “hide” from the menu. The rows will disappear.
Planning Shop Help for Business Plan Financials
19
On Mac, Ctrl+click on your selected rows to bring up the pop-up menu. Select “Hide”. The rows will disappear.
To unhide rows, do the following:
1. On the worksheet where you want to unhide rows, use your mouse to select both the visible row above and the visible row below where the hidden rows live.
2. On Windows, select the range of cells that include the row before and row after the ones you want to unhide. Right click, and on the pop-up menu select “Unhide”. On Mac, Ctrl+click on your selected row, then select “Unhide” from the pop-up menu.
Q: I noticed that some of the formulas in the workbook refer to worksheets named “Config,” “DepnSchedule,” and “LoanInvestSchedules”, but I don’t see those worksheets anywhere in the workbook. What’s going on?
“Config,” “DepnSchedule,” and “LoanInvestSchedules” are the names of hidden, protected worksheets inside the Business Plan Financials workbook. They are very important worksheets, and if you delete them or make any changes to them of any kind, most of the formulas in the entire workbook will break.
It is absolutely essential that you never edit or delete these worksheets. That is why we protected them and hid them.
Q: When I print out a worksheet, the page breaks are not where I’d like them to be. How do I fix it?
A: If a worksheet is big enough, when printed it will create multiple pages. If you don’t like the way the worksheet is being split into pages, use Excel’s Page Break Preview. In Page Break Preview, Excel zooms out so you can see the entire (big) worksheet. You can then drag and drop the blue lines to specify where the pages should break.
On Windows, to use Page Break Preview, click the View tab on the Excel Ribbon, and then select the Page Break Preview button.
On Mac, go to the Layout tab of the Excel Ribbon, and select the Page Layout menu item.
Q: How can I get my worksheets into a Word document or PowerPoint presentation?
A: Go to the worksheet you want to include in your document or presentation, select the worksheet cells and hit Ctrl-C to copy. Then, switch to your Word or Powerpoint document and hit Ctrl-V to paste.
Q: How can I create a PDF file from my Business Plan Financials worksheets, so that it will look nice when printed?
A: You need a third-party program that can convert Excel to PDF to do this. We recommend (but do not support) Adobe Acrobat or PrimoPDF. Refer to the documentation for those programs.
Q. I’d like to make changes to the worksheets, but I’m not proficient in Excel. What should I do?
A. PlanningShop can customize the worksheets to meet your precise needs. Contact us for information about our customization rates at [email protected].
Planning Shop Help for Business Plan Financials
20
Q. I’ve got a question that’s not addressed here. How can I get help?
A. Email us at [email protected]. Be sure to give us your name and full contact information, and to be as specific as possible about the problem or question you have. In your email, please include the following:
1. Your operating system 2. Your Excel version (examples: Excel 2007 for Windows, Excel 2010 for Windows, Excel 2011 for
Mac) 3. The version of the Business Plan Financials Workbook you are using. On Windows, you can find
this by clicking on the PlanningShop ribbon tab, selecting the “Business Plan Financials Help” and then the “About Business Plan Financials” menu item. On Mac, select the “About” worksheet.
