Division of Business and Information Management,
Bristol Community College
The purpose of this assignment is to experiment with the functionality of Microsoft Excel, its labels, dates, named references, and formulas.
If you decided to open a new business, you would have to prepare and maintain a number of financial documents. For example, you could use these documents to obtain a loan from a bank. If you are starting a new business, all your financial data are pro forma statements. That is, financial statement analysis which are based solely on future projections.
We will use Excel Workbook to work on financial documents in the order outlined in this assignment. However, before we lay down the required steps (see Home Work Instructions at the end), lets introduce some business guidelines related to the documents.
First of all, the sample financial statements presented in this introduction are all pro forma statements. They show only projections for the future profitability of your business, and not the history of an existing business.
Ultimately, in a course of a series of upcoming home work assignments, we must cover the following pro forma statements:
The Pro Forma Cash Flow Statement is the financial document that projects what your business means in terms of dollars. A cash flow statement is the same as a budget. It is a pro forma (or projected) statement used for internal planning and estimates how much money will flow into and out of a business during a designated period of time, usually the coming tax year. Your profit at the end of the year will depend on the proper balance between cash inflow and outflow.
The Cash Flow Statement identifies when cash is expected to be received and when it must be spent to pay bills and debts. It also allows your business managers to identify where the necessary cash will come from.
The Cash Flow Statement statement deals only with actual cash transactions and not with depreciation and amortization of assets, which makes it easier for us to deal with in the future homework assignment(s).
Time Period. The Cash Flow Statement should be prepared on a monthly basis for the next tax year (or more) of your business.
Because the cash flow statement deals with cash inflow and cash outflow, the first step in planning can be best accomplished by preparing two of the following worksheets.
This worksheet describes the cash flowing out of your business. It identifies categories of expenses and obligations and the projected amount of cash needed in each category. In real life you would use the information from your individual budgets (inventory purchases, direct expenses, administrative expenses, owner draws, etc.) to enter the required data.
The following list explains the categories of items that would typically appear on the Cash To Be Paid Out Worksheet:
Start-Up Costs. These are the costs incurred by you to get your business underway. They are generally one-time expenses and are capitalized for tax purposes.
Inventory Purchases. Cash to be spent during the period on items intended for resale. If you purchase manufactured products, this includes the cash outlay for those purchases. If you are the manufacturer, include labor and materials on units to be produced.
Variable Expenses (Selling or Direct Expenses). These are the costs of all expenses that will relate directly to your product or service (other than manufacturing costs or purchase price of inventory.)
Fixed Expenses (Administrative or Indirect Expenses). Include all expected costs of office overhead. If certain office-related bills must be paid, include them in this category.
Assets (Long-Term Purchases). These are the capital assets that will be depreciated over a period of years (land, buildings, vehicles, equipment). Determine how you intend to pay for them and include all cash to be paid out in the current period. Note: Land is the only asset that does not depreciate and will be listed at cost.
Liabilities. What are the payments you expect to have to make to retire any debts or loans? Do you have any Accounts Payable as you begin the new year? You will need to determine the amount of cash outlay that needs to be paid in the current year. If you have a car loan for $20,000 and you pay $500 per month for 12 months, you will have a cash outlay of $6,000 for the coming year.
Business Name: Easy Packaging, Inc.
Time Period Covered: Jan 1 - Dec 31, 20XX
|
|
||
| 1. Start-Up Costs: | 1,450 | |
| Business License | 30 | |
| Corporation Filing | 500 | |
| Legal Fees | 920 | |
|
|
||
| 2. Inventory Purchases | 32,000 | |
| Cash out for goods intended for resale | ||
|
|
||
| 3. Variable Expenses (Selling) | ||
| Advertising/Marketing | 8,000 | |
| Freight | 2,500 | |
| Fulfillment of Orders | 800 | |
| Packaging Costs | 1,400 | |
| Sales Salaries/Commissions | 14,000 | |
| Travel | 1,550 | |
| Miscellaneous Variable Expense | 300 | |
| Total Selling Expenses | 28,550 | |
|
|
||
| 4. Fixed (Administrative) Expenses | ||
| Financial Administration | 1,800 | |
| Insurance | 900 | |
| Licenses/Permits | 100 | |
| Office Salaries | 16,300 | |
| Rent Expenses | 8,600 | |
| Utilities | 2,400 | |
| Miscellaneous Fixed Expense | 400 | |
| Total Operating Expenses | 30,500 | |
|
|
||
| 5. Assets (Long-Term Purchases) | 6,000 | |
| Cash to be paid out in current period | ||
|
|
||
| 6. Liabilities | 9,900 | |
|
Cash outlay for retiring debts, loans,
and/or accounts payable |
||
|
|
||
| 7. Owner Equity | 24,000 | |
| Cash to be withdrawn by owner | ||
|
|
||
| Total Cash To Be Paid Out | $ 131,000 | |
|
|
||
This next financial document is the worksheet showing the cash flowing into your business. It helps u to estimate how much cash will be available, and from what sources. To complete this worksheet in real life, you would have to look at cash on hand, projected revenues, assets that can be liquidated, possible lenders or investors and owner equity to be contributed. This worksheet forces you to take a look at any existing possibilities for increasing available cash.
The following list explains the categories of items that typically appear on the Sources of Cash Worksheet:
Cash On Hand. Money that you have on hand in your bank accounts and other resources. Be sure to include petty cash and any income that you have not yet deposited.
Sales (Revenues). This includes projected revenues from the sale of your products and/or services. If payment is not expected during the time period covered by this worksheet, do not include that portion of your sales. Include here all advance deposits that you require your customers to pay on expected sales or services.
Miscellaneous Income. The money on loans or saving account deposits that will yield interest income during the period in question.
Sale of Long-Term Assets. If you are expecting to sell any of your fixed assets such as land, buildings, vehicles, machinery, equipment, etc., be sure to include only the cash you will receive during the current period.
Liabilities. This figure represents the amount you will be able to borrow from lending institutions such as banks and finance companies. Be reasonable about how much you think you can borrow. If you have no collateral, have little data in your business plan, or if you have a poor financial history, you will find it difficult, if not impossible, to find a lender. In real life this source of cash requires lots of pre-planning!
Equity. Sources of equity come from owner investments, contributed capital, sale of stock, or venture capital. Do you anticipate the availability of personal funds? Does your business have the potential for growth that might interest a venture capitalist? Be sure to be realistic in this area. Remember, that you cannot sell stock (or equity) to a nonexistent investor.
Business Name: Easy Packaging, Inc.
Time Period Covered: Jan 1 - Dec 31, 20XX
|
|
||
| 1. Cash On Hand | $ 20,000 | |
|
|
||
| 2. Sales (Revenues) | ||
| Sales | 90,000 | |
| Service Income | 22,000 | |
| Deposits on Sales or Services | 0 | |
| Collections on Accounts Receivable | 3,000 | |
|
|
||
| 3. Miscellaneous Income | ||
| Interest Income | 1,000 | |
| Payments to be Received on Loans | 0 | |
|
|
||
| 4. Sale of Long-Term Assets | 0 | |
|
|
||
| 5. Liabilities | ||
| Loan Funds (Banks, Lending Instruments, etc.) | 40,000 | |
|
|
||
| 6. Equity | ||
| Owner Investments (Sole Prop. or Partnership) | 10,000 | |
| Contributed Capital (Corporation) | 0 | |
| Sale of Stock (Corporation) | 0 | |
| Venture Capital | 35,000 | |
| A. Without product sales | $ 131,000 | |
|
|
||
| Total Cash Available | ||
| B. With product sales | $ 221,000 | |
|
|
||
Your task is to create and save an Excel document with the two worksheets described above, namely the Cash To Be Paid Out, and the Sources Of Cash.
Use Microsoft Excel to complete this assignment. Start Excel and open a blank document.
While working with Excel, save your document often. Give it a descriptive name, for example, "ProFormaStatements.xls".
By default, a new blank document includes three worksheets named Sheet1, Sheet2, and Sheet3. There is a worksheet tab at the bottom of the screen, where you can click to navigate to specific worksheet. An Excel document with more than one worksheet is called a workbook.
It is a good practice to give your worksheets descriptive names. Rename the worksheets named Sheet1 and Sheet2. Double click on the tab of the worksheet, or right-click on a worksheet tab and select Rename. Type the new name and press Enter. Name your worksheets
TIP: For easier recognition, you can also color-code your worksheet by clicking on the worksheet tab, then clicking Format menu, Sheet, Tab Color.
Add appropriate headings in both of your worksheets. The headings should specify the name of the financial document, the name of the company, and the time period. Excel uses a default font of Arial 10 points. However, you can change the typeface, size, style, and color, or add Bold, Underline, or Italics styles as you wish.
Please use appropriate date formats when entering dates. Cick Format menu, Cells, and the dialog box will open. Click Numbers tab and specify appropriate format options to properly display the dates.
Enter all worksheet items and the corresponding dollar amounts. You don't have to enter all items in exact form and order presented in the worksheet samples above. You may enter the information that you like by simply following the guidelines for the categories of worksheet items.
Enter formulas to calculate the SUM of appropriate areas of your worksheets.
IMPORTANT: You must use range names in all of the formulas that you create. To give names to single cells or contiguous group of cells in your worksheets, select the cells that you wish to name, than click Insert menu, Name, Define.
All formulas in Excel begin with the equal sign (=formula). When using range names, yor formulas should have format such as =SUM(StartUpCostsItems), where StartUpCostsItems is the name given to the range of cells highlighted in the following fragment of the Cash To Be Paid Out Worksheet:
|
|
||
| 1. Start-Up Costs: | 1,450 | |
| Business License | 30 | |
| Corporation Filing | 500 | |
| Legal Fees | 920 | |
|
|
||
When you are done, save your document, close it, and exit Excel.
Submit your workbook document via e-mail attachment sent to:
Igor Kholodov Igor.Kholodov@bristolcc.edu