NON-ALCOHOLOIC BEVERAGE COMPANY

Business Plan Financials Worksheet Guidance

Below is guidance for data entry into cells of each of the separate Excel worksheets of the overall Business Plan Financials. This is the base pro-forma financial statements for your business. Please note: Many of the cells in the Business Plan Financials (BPF) are already prepopulated with suggested/default values. Use the default numbers unless otherwise indicated.

Only cells with BLUE text can be changed. The cells in black will be filled in automatically, based on your setup assumptions, or will be carried over from numbers you entered earlier on other worksheets. Work in order – as every cell you complete will carry over to all other applicable Excel worksheets (for example, if you enter a figure in the Staff Budget, that figure will carry over to your Income Statement and Cash Flow Statement).

You are encouraged to work through the guide as early as possible. (Review the Course Guide for specific due dates on specified worksheets; as assignments are due throughout the course.)

Worksheet #1 – Set Up and Assumptions

Line 9: the month the academic quarter starts

Line 12: enter the year the academic quarter starts

Line 15: Two (students can choose the number of product lines to include up to 10 lines)

Line 18: they will sell 55% on credit

Line 21: 30 days (standard default; however, can be adjusted as needed)

Line 43: $16,500 (estimate of net sales after six month of weekend sales)

Line 49: 1.00% (Dodd-Frank Act of 2011 allows businesses to receive interests on business accounts, per the agreement of the financial institution [range 0 to 1.50%])

Worksheet #2 – Sales Projections

Each student must determine this for their business—speculate on how your company will grow and how much you will sell. Reflect this in/from your Operations Plan

Worksheet #3 – Inventory

Line 7: Ingredients

Line 8: Bottles/caps

Line 9: Labels

Line 10: Cartons

Repeat these four lines for each product line.

Worksheet #4 – Capital Expenditures

(Note: This applies to purchased equipment, not leased equipment.)

Facilities section: Students are not purchasing facilities (facility is already provided)

Equipment section: Capital Purchases should be additional expenses that each student might decide to purchase. (Note – after the first year of business)

Current vans and other equipment should be captured in Line 23 as part of “Other Assets” on the Balance Sheet, Worksheet 11.

Computer Hardware/Software Section: Capital Purchases should be additional expenses that each student might decide to purchase.

Current computer hardware/software should be captured as part of “Other Assets” on the balance sheet, along with equipment mentioned above.”The Financials”
This week’s discussion is worth 90 points and it is part of your Course Project.  
Before you get started on this exercise, you will need the textbook (Chapter 16 – The Financials), the Business Plan Financials Guide and Excel document, and the NAB Company Portfolio. (Attached)

Link for ebook. Please use every page referenced to use  
https://strayer.vitalsource.com/#/books/9781933895475/epubcfi/6/66[;vnd.vst.idref=p33_ch16]!/4/2/4/2@0:0
This Assignment has two sections: The Excel document and the written portion of the financials (sources and use of funds, plan assumption, and  break-even analysis) as described below.
Section I:  The Business Plan financials (Excel document)

Using your NAB Company Portfolio and the first year of your business plan for the company, you will complete all the worksheets in the Excel document in order, so that you can complete the Income Statement, Cash Flow Projections, and Balance Sheet sections from the Business Plan Financials spreadsheet.  

Notes:  

The setup and marketing worksheet should already be completed from Assignment 2.
Use the instructions provided in the Business Plan Financials Guide to help you complete this document, as well as the NAB Portfolio (pages 4-8).
To submit: Attach the completed Excel worksheet to the discussion thread (click on the Add Content button under the Post button to access the attachment option when you are ready to submit, attach your document and then click submit)  

Section II: Financial section of the business plan (compose as a Word document and paste in the discussion window)  (2 pages)
You will have 3 headings as outlined below:
1.Sources and use of funds:  Outline the funds you have currently (see portfolio pages 4 and 8) and the ones you intend to raise (you need to raise funds per the portfolio page 4).  Explain how you plan to use the funds (a clear plan for how you will use the money).
Before addressing this section please review:

Sources and Use of Funds section on page 312 of your textbook-Sample plan on page 325 for an idea on how to address this section.

2. Plan assumptions: The Financial plan must be based on decisions and facts. Investors want to know if this plan is realistic. In this section you will outline your plan assumptions.

Before addressing this section please review:  

The setup and assumptions sheet in your Excel document.  
Sample plan in your textbook page 326
Assumption sheet in your textbook page 313.  
Article about the 5 key assumptions of your business plan. https://www.thebalancesmb.com/question-key-assumptions-in-your-business-plan-1200853

3. Break-even analysis:  The break-even point is the point at which you make enough money in revenue to pay your expenses, but no profit (or loss). In this section, clarify what the break-even number is for year one (average). You will extract this information from the Break-even tab in the Excel document.
Before addressing this section please review:

Page 314 in the textbook, which covers the break-even analysis.
The BrkEvn (Break-Even) worksheet in your Excel document. You would have calculated this there.

Notes:  

You will use the Excel document in Assignment 3 and 4.  
You will use the written portion of your Financials in Assignment 4.