gabrielgurley.com
Open-Licensed Instructional Guides for Learners
Lesson: Creating a Cash Flow Statement Using
OpenOffice.org 3 Calc
Lesson Objectives
In this lesson, you will learn the following:
Overview
In the last two lessons, we have used Calc to create a personal worksheet for tracking personal monthly household expenses and a personal balance sheet. In this lesson, we will learn how to create a cash flow statement, often used by businesses to keep track of incoming revenues and outgoing expenditures at a given point in time. Upon completion of this lesson, you will have learned how to format text with font styles within a Calc worksheet, how to create formulas for multiplying data contained within the document and how to interpret the data within the completed cash flow statement.
Getting Started
Before we do so, we need to open the Lesson Seven file that is available for use with this book. To open the file, follow these steps:
Applying Font Styles to Text Contained Within a Spreadsheet Document
Like the Writer word processing application, Calc provides a wide array of formatting options for text contained within a spreadsheet document. In fact, many of the menu and toolbar options that are available within Writer are the same within Calc as well.
In this lesson, we need to apply the Bold font style to some of the text within the cash flow statement. To do so, follow these steps:
You can also format font styles by clicking on the Format menu located at the top of the Calc application window and selecting the Cells menu option.
Creating Formulas to Add, Subtract and Multiply Values
We are now ready to begin to perform our calculations for the cash flow statement. Much of the information you need to perform the calculations has already been provided, while some of the basic calculations have already been performed. For each month, however, we need to perform additional calculations for three items: Total Cash Disbursements, Income Taxes and Net Change In Cash.
First, we are going to perform the calculations for the Total Cash Disbursements for each month. Total cash disbursements are calculated by simply adding together the cash disbursement values together to receive a total. To do so, follow these steps:
Next, we are going to perform the calculations for the Income Taxes that are to be paid for each month. The income taxes are calculated by multiplying the Total Operating Profit (Before Taxes) by the appropriate income tax rate. In this example, the income tax rate is 35% for federal, state and local taxes. Be aware that the Total Operating Profit is usually found on an income statement (also referred to as a Profit And Loss Statement or P&L Statement) and not on a cash flow statement. However, it has been listed on the worksheet to provide the necessary information to calculate the income tax payable for each month. To calculate the Income Taxes for each month, follow these steps:
Finally, we are going to perform the calculations for the Net Change In Cash for each month. The net change in cash is calculated by subtracting the Total Cash Disbursements After Taxes from the Total Cash Receipts for the respective month. To calculate the Net Change In Cash for each month, follow these steps:
Analyzing the Final Data Contained Within the Cash Flow Statement
The cash flow statement is now complete. The cash flow statement is a very important tool for businesses, as it provides information regarding the actual cash position in any given period of time. This financial tool may not be crucial for a business that operates on a cash and carry basis, because it is easy to determine how much cash is available by looking at its bank statement or the amount of cash secured in the business’ safe. However, for start-up businesses and businesses that sell on credit, a cash flow statement is crucial in determining how much money is needed and available to start or operate the business.
Like the completed cash flow statement in this lesson demonstrates, a business can be making a profit and still lack the cash needed to pay its monthly bills. If you look at cell N33, you will note that the Ending Cash Position for the year increased to $14695.28, up from a Starting Cash Position of $6,000.00. This could mean that the business actually made money in its first year, which is unusual for many start-up businesses.
However, if you carefully analyze the cash flow statement, it reveals a very serious problem for months eight (8) and nine (9). If you look at the Ending Cash Position for those months (cells I33 and J33, respectively), they show a negative cash position. This means that if this business were actually operating during those months, they would not have enough money to pay their immediate bills. This could result in the business having to close its doors if additional funds were not available.
If this were a preliminary cash flow statement used for planning a start-up business, the business owner could resolve this potential problem by adding, at minimum, an additional $583.55 to the initial Starting Cash Position in month one (1) to cover the shortfalls for months eight (8) and nine (9). Or, if the business owner was confident that their sales forecast prior to these months would be higher than, then the increased sales could cover for the shortfall.
Moreover, the business owner could decide to open the business in month eight (8) or nine (9) with a starting cash position of $6,000.00. Looking at the completed cash flow statement for this lesson, you will note that the Net Change In Cash for months two (2) through eight (8) were all negative. This means that the business was using more cash to pay its bills than it was receiving in sales. By opening in months eight (8) or nine (9), it would allow the business to open its doors just prior to the big shopping season and increase its cash position significantly before entering the slower sales period of months two (2) through eight (8). This scenario could stabilize the financial position of the business greatly until it could establish itself and make sales and market share gains in the second and third year.
As you have seen, there is rarely one answer to the questions or problems that arise when analyzing a business’ financial position. However, by developing the fundamental financial statements with a spreadsheet application such as Calc, a business’ management team can identify the potential issues more clearly. By doing so, it can assist in solving problems before they arise.
Lesson Objectives
In this lesson, you will learn the following:
- How to create a cash flow statement and how to interpret the data contained within.
- How to add font styles to text contained within a spreadsheet document.
- How to create formulas to add, subtract and multiply values within a worksheet.
Overview
In the last two lessons, we have used Calc to create a personal worksheet for tracking personal monthly household expenses and a personal balance sheet. In this lesson, we will learn how to create a cash flow statement, often used by businesses to keep track of incoming revenues and outgoing expenditures at a given point in time. Upon completion of this lesson, you will have learned how to format text with font styles within a Calc worksheet, how to create formulas for multiplying data contained within the document and how to interpret the data within the completed cash flow statement.
Getting Started
Before we do so, we need to open the Lesson Seven file that is available for use with this book. To open the file, follow these steps:
- If you have not already downloaded the lesson files for this course, click on the Lesson Files topic located in the course lessons area and select to download the .zip file to your computer's desktop.
- When the file appears on your computer’s desktop, double-click the file icon to unzip its contents and access the files associated with each lesson. Double-click the Lesson Files folder icon that contains the available lesson files, locate the Lesson 07 folder and double-click on it to access the file for this lesson. When you have opened the folder, double-click the file named lesson_07_start.ods to open the file.
Applying Font Styles to Text Contained Within a Spreadsheet Document
Like the Writer word processing application, Calc provides a wide array of formatting options for text contained within a spreadsheet document. In fact, many of the menu and toolbar options that are available within Writer are the same within Calc as well.
In this lesson, we need to apply the Bold font style to some of the text within the cash flow statement. To do so, follow these steps:
- Using your left mouse button, click on row label 3 located on the left side of the worksheet. Doing so will select the entire row, but will leave all remaining rows within the worksheet unselected.
- To apply the Bold font style to the selected text, you can simply click the BOLD button located within the Formatting toolbar. If the Formatting toolbar is not visible, you can make it so by clicking on the View menu, select the Toolbars menu option and then select Formatting from the submenu options that appear.
- Repeat Steps #1 and #2 for row 4 within the worksheet. Applying the necessary font styles for this worksheet will then be complete.
You can also format font styles by clicking on the Format menu located at the top of the Calc application window and selecting the Cells menu option.
Creating Formulas to Add, Subtract and Multiply Values
We are now ready to begin to perform our calculations for the cash flow statement. Much of the information you need to perform the calculations has already been provided, while some of the basic calculations have already been performed. For each month, however, we need to perform additional calculations for three items: Total Cash Disbursements, Income Taxes and Net Change In Cash.
First, we are going to perform the calculations for the Total Cash Disbursements for each month. Total cash disbursements are calculated by simply adding together the cash disbursement values together to receive a total. To do so, follow these steps:
- Using your mouse, select cell C27, which is the where the Total Cash Disbursements for Month Two (2) should be presented.
- Click the SUM function button located in the Function Bar. When you click the button, Calc automatically suggests a cell range. In this step, Calc automatically selects cell range C17 through C26, which are the cash disbursement amounts we wish to add for Month Two (2). If Calc had incorrectly chose the cell range you wish to add, you could simply hold down your left mouse button and drag over the desired cell range to make the appropriate selection. Once the desired cell range has been selected, press the ENTER or RETURN key and Calc adds the values within the cell range selected. The results are then produced within the cell originally selected.
- Repeat Step #1 by selecting, one at a time, each of the following cells: D27, E27, F27, G27, H27, I27, J27, K27, L27 and M27. After selecting an individual cell, follow the instructions in Step #2 to obtain the results for the appropriate month. When you have completed adding the cell ranges specified above, the spreadsheet should look like the illustration below.
Next, we are going to perform the calculations for the Income Taxes that are to be paid for each month. The income taxes are calculated by multiplying the Total Operating Profit (Before Taxes) by the appropriate income tax rate. In this example, the income tax rate is 35% for federal, state and local taxes. Be aware that the Total Operating Profit is usually found on an income statement (also referred to as a Profit And Loss Statement or P&L Statement) and not on a cash flow statement. However, it has been listed on the worksheet to provide the necessary information to calculate the income tax payable for each month. To calculate the Income Taxes for each month, follow these steps:
- Using your mouse, select cell C28, which is the where the Income Taxes for Month Two (2) should be presented.
- After selecting cell C28, type the equal sign (=) followed by a beginning parenthesis. To type a beginning parenthesis, hold down the SHIFT key and press nine (9) within the main keyboard area (not within the numeric keypad area). After doing so, use your left mouse button to click the cell that contains the value for Total Operating Profit Before Taxes for the respective month. For Month Two (2), this value is displayed within cell C37. When doing so, the cell is entered into the formula. Then type the asterisk “*” sign, which represents the multiplication sign within spreadsheet applications. Then type 0.35 to tell Calc to multiply the Total Operating Profit Before Taxes for Month Two (2) by 35%. End the formula by typing an ending parenthesis. To type an ending parenthesis, hold down the SHIFT key and press zero (0) within the main keyboard area (not within the numeric keypad area). When completed, the formula should read:
=(C37*0.35)
Press
the ENTER or RETURN key. The values will be
added, and the amount of income tax to be paid
for Month Two (2) will be calculated, with the
result displayed within cell
C28.
- Repeat Step #4 by selecting, one at a time, each of the following cells: D28, E28, F28, G28, H28, I28, J28, K28, L28 and M28. After selecting an individual cell, follow the instructions in Step #5 to obtain the results for the appropriate month. When you have completed adding the cell ranges specified above, the spreadsheet should look like the illustration below.
Finally, we are going to perform the calculations for the Net Change In Cash for each month. The net change in cash is calculated by subtracting the Total Cash Disbursements After Taxes from the Total Cash Receipts for the respective month. To calculate the Net Change In Cash for each month, follow these steps:
- Using your mouse, select cell C31, which is the where the Net Change In Cash for Month Two (2) should be presented.
- After selecting cell C31, type the equal sign (=) followed by a beginning parenthesis. To type a beginning parenthesis, hold down the SHIFT key and press nine (9) within the main keyboard area (not within the numeric keypad area). After doing so, use your left mouse button to click the cell that contains the value for Total Cash Receipts for the respective month. For Month Two (2), this value is displayed within cell C14. When doing so, the cell is entered into the formula. Then type the minus “-” sign, and then use the left mouse button to click cell that contains the value for Total Cash Disbursements After Taxes for the respective month. For Month Two (2), the value is displayed within cell C29. End the formula by typing an ending parenthesis. To type an ending parenthesis, hold down the SHIFT key and press zero (0) within the main keyboard area (not within the numeric keypad area). When completed, the formula should read:
=(C14-C29)
Press
the ENTER or RETURN key. The value contained
within cells C29 will be subtracted from the
value contained within cell C14, and the result
will be produced within cell
C31.
- Repeat Step #7 by selecting, one at a time, each of the following cells: D31, E31, F31, G31, H31, I31, J31, K31, L31 and M31. After selecting an individual cell, follow the instructions in Step #8 to obtain the results for the appropriate month. When you have completed adding the cell ranges specified above, the spreadsheet should look like the illustration below.
Analyzing the Final Data Contained Within the Cash Flow Statement
The cash flow statement is now complete. The cash flow statement is a very important tool for businesses, as it provides information regarding the actual cash position in any given period of time. This financial tool may not be crucial for a business that operates on a cash and carry basis, because it is easy to determine how much cash is available by looking at its bank statement or the amount of cash secured in the business’ safe. However, for start-up businesses and businesses that sell on credit, a cash flow statement is crucial in determining how much money is needed and available to start or operate the business.
Like the completed cash flow statement in this lesson demonstrates, a business can be making a profit and still lack the cash needed to pay its monthly bills. If you look at cell N33, you will note that the Ending Cash Position for the year increased to $14695.28, up from a Starting Cash Position of $6,000.00. This could mean that the business actually made money in its first year, which is unusual for many start-up businesses.
However, if you carefully analyze the cash flow statement, it reveals a very serious problem for months eight (8) and nine (9). If you look at the Ending Cash Position for those months (cells I33 and J33, respectively), they show a negative cash position. This means that if this business were actually operating during those months, they would not have enough money to pay their immediate bills. This could result in the business having to close its doors if additional funds were not available.
If this were a preliminary cash flow statement used for planning a start-up business, the business owner could resolve this potential problem by adding, at minimum, an additional $583.55 to the initial Starting Cash Position in month one (1) to cover the shortfalls for months eight (8) and nine (9). Or, if the business owner was confident that their sales forecast prior to these months would be higher than, then the increased sales could cover for the shortfall.
Moreover, the business owner could decide to open the business in month eight (8) or nine (9) with a starting cash position of $6,000.00. Looking at the completed cash flow statement for this lesson, you will note that the Net Change In Cash for months two (2) through eight (8) were all negative. This means that the business was using more cash to pay its bills than it was receiving in sales. By opening in months eight (8) or nine (9), it would allow the business to open its doors just prior to the big shopping season and increase its cash position significantly before entering the slower sales period of months two (2) through eight (8). This scenario could stabilize the financial position of the business greatly until it could establish itself and make sales and market share gains in the second and third year.
As you have seen, there is rarely one answer to the questions or problems that arise when analyzing a business’ financial position. However, by developing the fundamental financial statements with a spreadsheet application such as Calc, a business’ management team can identify the potential issues more clearly. By doing so, it can assist in solving problems before they arise.