Home /OpenOffice.org 3 Course Package /Lesson Five - Creating a Basic Spreadsheet for Calculating Household Expenses Using Calc /Lesson Six - Creating a Balance Sheet Using Calc

Lesson Six - Creating a Balance Sheet Using Calc

Undefined

Lesson Objectives

In this lesson, you will learn the following:

(All the lessons here comply with the National Occupational Standards of E-Skills)

  1. How to create a balance sheet and why they are important for monitoring the financial status of an individual or business.

  1. How to resize cells using the mouse pointer.

  1. How to add worksheets to a spreadsheet document.

  1. How to add values among worksheets.

  1. How to use the SUM function to add values among multiple cells throughout a worksheet.

Overview
In the last lesson, we learned how to use Calc to create a basic spreadsheet for calculating monthly household expenses. Using Calc can help keep track of where money is being spent each month and where money could be saved or expenditures eliminated. However, Calc has the ability to create even more complex spreadsheet documents, including calculating financial data for business plans, creating business reports and more.
In this lesson, you will become acquainted with features within Calc to create a balance sheet. Upon completion of this lesson, you will have learned to fundamentals of creating more complex formulas. This includes creating formulas manually to calculate values listed throughout a worksheet, and even among multiple worksheets. Ending the lesson will include instruction regarding how to understand and analyze the data contained within a balance sheet.
Getting Started
Before we do so, we need to open the Lesson Six file that is available for use with this book. To open the file, follow these steps:

  1. 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. 

  1. 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 06 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_06_start.ods to open the file.

Entering Text and Numbers Within Cells
The first thing we need to for this lesson is to enter the remaining text and values needed to complete the personal balance sheet. If you are unfamiliar with selecting cells within a Calc spreadsheet document, you will need to refer to the section in Lesson 6 titled “Entering Text and Numbers Within Cells”. You may also refer to the sections ‘Selecting a Cell” and “Entering Text and Numbers” located within Appendix B.
To enter the remaining text and numbers needed to complete the personal balance sheet in this lesson, follow these steps:

  1. With the lesson file lesson_06_start.ods open, select the personal balance sheet by clicking on the worksheet tab labeled Personal located above the status bar.

  1. Select cell B8. Once the cell has been selected, type Furniture for the type of current asset that will be entered into the cell. Then press the ENTER or RETURN key on your keyboard.


  1. When you pressed the ENTER or RETURN key, you noticed that Furniture was entered into cell B8 and Calc automatically proceeded to select B9 in the next row. With B9 automatically selected for you, type Jewelry and press the ENTER or RETURN key. Then in cells B15, B16, B24, B31 and B32, type Rental Property, Automobiles, Credit Card Balances, Rental Property Loan and Automobile Loan Balance respectively.


  1. Now that we have created the column that describes each type of asset or liability contained within the personal balance sheet, we now need to provide the worksheet some data to calculate. Enter the following amounts within the respective cells associated with the type of asset or liability: 300.00 (cell D6), 5000.00 (cell D7), 2500.00 (cell D8), 750.00 (cell D9), 550.00 (cell D10), 211000.00 (cell D14), 125000.00 (cell D15), 12000.00 (cell D16), 10000.00 (cell D17), 4200.00 (cell D24), 5000.00 (cell D25), 1500.00 (cell D26), 205000.00 (cell D30), 118000.00 (cell D31), 15000.00 (cell D32) and 15000.00 (cell D33).


Formatting Cells
As we have been entering the dollar amounts for the assets and liabilities, you have noticed that the values are not formatted properly. For instance, all of the figures represented in this personal balance sheet are dollar amounts. When we typically write a dollar amount on paper, we write it with a dollar sign in front of the value, and cents are represented two decimal places behind the dollar amount. Currently, however, the amounts represented do not contain the cents amount. Nor do any of the values within the spreadsheet have the dollar sign placed in front of them.
In the following steps, we will format the cells so that the values contained within are represented in U.S. dollar amounts. We will also format some of the cells so that they contain lines that signify where a subtotal is present for a specific asset or liability. This is often done to help in identifying the subtotals within a balance sheet. To perform these formatting changes, follow these steps:

  1. Using your left mouse button, click on column label D located at the top of the worksheet. Doing so will select the entire column, but will leave all remaining columns within the worksheet unselected.


  1. Click on the Format menu and select Cells from the menu options that appear.

  1. When the Format Cells window appears, click on the Numbers tab located at the top of the window.

  1. In the Category selection area, select Currency.

  1. Once a category has been selected, select a format type located within the Format selection area. By default, Calc will choose -$1,234.00. The minus (-) sign represented in front of the dollar amount represents a negative amount, as is a standard format spreadsheet applications use if a dollar amounts happens to be negative. This format type is acceptable, and should be selected if it isn’t already.


  1. Click the OK button to complete the operation.


  1. Next, we are going to format some of the cells so that they contain lines that signify where a subtotal is present for a specific asset or liability. To do so, select cell D10.


  1. Click on the Format menu and select Cells from the menu options that appear.

  1. When the Format Cells window appears, click on the Borders tab located at the top of the window.

  1. In the Line Arrangement selection area, select the border arrangement by clicking on the second default arrangements provided. Customize the border arrangement by double-clicking with your left mouse button on the top, left and right borders until they are deleted using the User-Defined configuration area provided.

  1. In the Line selection area, select the 1.00pt border thickness using the Styles selections and choose the Black border color using the Color popup menu.


  1. In the Spacing To Contents area, select the appropriate border spacing for each side of the selected cells (optional).

  1. Once all formatting selections have been made, click the OK button to complete the operation.

  1. Repeat steps #7 through #13 for cells D17, D26 and D33. After doing so, cell formatting for the personal balance sheet will be complete.


Resizing Cells Using the Mouse Pointer
If you look carefully at cells D14, D15, D30 and D31, you will notice that the dollar value has disappeared and has been replaced by pound signs (###). This is because the cell isn’t wide enough to display all of the characters of the value that is currently there.
In Lesson 5, you may recall that we utilized the Format menu to increase the width of the columns so that text and numeric values would display properly. Not only can you utilize the Format menu to adjust the width of columns and the height of rows, but you can also use your mouse pointer within the column and row label area within a worksheet to make adjustments as well. To do so, follow these steps:

  1. Place your mouse pointer at the top of the worksheet within the column label area along the border between two columns. In this example, place your mouse pointer within the column label area where column D and E border each other.


  1. When you place your mouse pointer where column D and E border each other, your mouse pointer transforms into a line with two arrows pointing left and right. When it does, hold down the left mouse button and drag it to the right to increase the width of the column. If the width of the column had needed to be decreased, you would have dragged the mouse to the left. When the column has been adjusted to the desired width, release the left mouse button.


  1. To perform a similar task to adjust the height of a row, place the mouse pointer in the row area on the left side of the worksheet and perform a similar operation by dragging the mouse up or down.

Creating Formulas Using the SUM Function
Now that our personal balance sheet has been formatted and values provided for the various asset and liability entries, we are now ready to use the SUM function to enable the worksheet to automatically calculate the values for us. To do so, follow these steps:

  1. Using your mouse, select cell D11.

  1. 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 D6 through D10, which is the cell range we wish to calculate the total current assets. 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 cell D11.




  1. Select cell D18. Then click the SUM function button located in the Function Bar. When doing so, cells D14 through D17 should be selected, which is the cell range we wish to select to calculate the total long-term assets. Press the ENTER or RETURN key and Calc adds the values within the cell range selected. The results are then produced within cell D18.


  1. Next, we need to calculate the total assets that are presented within the personal balance sheet. We do so by adding together the total current assets and total long term assets. Because the cells we need to add are not adjacent to one another within the worksheet, we will need to create the formula manually to obtain the correct result. To do so, select cell D20. Then proceed with Step #5.

  1. After selecting cell D20, type =sum 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 cell D11. When doing so, the cell is entered into the formula. Then type the plus “+” sign, and then use the left mouse button to click cell D18 to enter the cell into the formula. 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:

=sum(D11+D18)

Press the ENTER or RETURN key. The values contained within cells D11 and D18 will be added, and the result will be produced within cell D20.

  1. Select cell D27. Then click the SUM function button located in the Function Bar. When doing so, cells D24 through D26 should be selected, which is the cell range we wish to select to calculate the total current liabilities. Press the ENTER or RETURN key and Calc adds the values within the cell range selected. The results are then produced within cell D27.


  1. Select cell D34. Then click the SUM function button located in the Function Bar. When doing so, cells D30 through D33 should be selected, which is the cell range we wish to select to calculate the total long-term liabilities. Press the ENTER or RETURN key and Calc adds the values within the cell range selected. The results are then produced within cell D34.


  1. Next, we need to calculate the total equity, often referred to as an individuals net worth within a personal balance sheet. We do so by subtracting the total assets from the total of current and long-term liabilities. Again, because the cells we need to add are not adjacent to one another within the worksheet, we will need to create the formula manually to obtain the correct result. To do so, select cell D36. Then proceed with Step #9.

  1. After selecting cell D36, type = followed by a beginning parenthesis. Because we will be subtracting some values within the formula, we cannot use the SUM function and obtain the correct value. 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 cell D20. When doing so, the cell is entered into the formula. Then type the minus “-” sign, followed by another beginning parenthesis. Use the left mouse button to click cell D27 to enter the cell into the formula. Type the plus “+” sign, then use the left mouse button to click cell D34. End the formula by typing two ending parentheses. To type the ending parentheses, hold down the SHIFT key and press the zero (0) key twice within the main keyboard area (not within the numeric keypad area). When completed, the formula should read:

=(D20-(D27+D34))

Press the ENTER or RETURN key. The values contained within the formula will be calculated, and the result will be produced within cell D36. Like when calculating the result using standard arithmetic, Calc adds the values D27 and D34 first, then subtracts the total value from the value contained within cell D20.

  1. Finally, we need to calculate the total liabilities and equity. We do so by adding together the total current liabilities, total long-term liabilities and equity (net worth). To do so, select cell D38. Then proceed with Step #11.

  1. After selecting cell D38, type =sum 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 cell D27. When doing so, the cell is entered into the formula. Then type the plus “+” sign, and then use the left mouse button to click cell D34 to enter the cell into the formula, followed by typing another plus “+” sign. Use the left mouse button to click cell D36 to enter the cell into the formula. 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:

=sum(D27+D34+D36)

Press the ENTER or RETURN key. The values contained within cells D27, D34 and D36 will be added, and the result will be produced within cell D38.

Adding Values Among Worksheets
Not only can you calculate values within an individual worksheet, but you can also calculate values contained within multiple worksheets as well. In this simple example, we will calculate the total amount of cash present in both the personal balance sheet and the opening business balance sheet. To do so, follow these steps:

  1. Select the personal balance sheet by clicking on the worksheet tab labeled Personal located above the status bar.

  1. Select cell B42 and type Total Personal and Business Cash. Then press the ENTER or RETURN key on your keyboard.

  1. Next, select cell D42. Then type =sum 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 cell D6. When doing so, the cell is entered into the formula. Then type the plus “+” sign.

  1. Select the opening business balance sheet by clicking on the worksheet tab labeled Business located above the status bar. Within the Business worksheet, use your left mouse button to click cell D6. When doing so, the cell is entered into the formula. 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:

=sum(D6+Business.D6)

Press the ENTER or RETURN key. The values contained within cell D6 of the Personal worksheet and cell D6 of the Business worksheet will be added, and the result will be produced within cell D42 of the Personal worksheet.

Adding Worksheets to a Spreadsheet Document
When opening this Calc spreadsheet lesson file, you will notice that two worksheets are present. These worksheets are labeled Personal and Business. However, additional worksheets can be created within this or any Calc spreadsheet document. To create a new worksheet within a Calc spreadsheet document, follow these steps:

  1. Click the Insert menu and select Sheet from the menu options that appear. 

  1. In the Position selection area, choose whether the new worksheet should be positioned before or after the current worksheet by selecting the appropriate radio button.

  1. In the Sheet selection area, select the number of worksheets to be added by using the selection field provided. If only one new worksheet is to be created, type the name the new worksheet should be labeled as using the Name text field provided. In this example, select to create one (1) new worksheet and enter your first name as the worksheet name.

  1. Click OK to complete the operation. The new worksheet should appear within the worksheet list located just above the Status Bar at the bottom of the spreadsheet window.

Exporting a Spreadsheet as a Portable Document Format (PDF) File
Like the Writer word processing application bundled with OpenOffice.org, Calc has the ability to export spreadsheet documents as a Portable Document Format (PDF) file. As you recall, saving a document as a PDF file is a convenient way to share read-only documents to other users that have a PDF reader application installed on their computer. However, OpenOffice.org cannot edit a document that has been saved as a PDF file. Therefore, if you wish to save a document for editing at a later date, save the document in its Native OpenDocument file format.
To save a document as a read-only PDF file, follow these steps:

1. Click on the File menu and choose Export As PDF from the menu options that appear. 

2. When the Export As PDF window appears, OpenOffice.org will provide you with some additional options for you to select. Among these options are the page range and image compression quality. If you choose Lossless Compression, the file will be exported in the highest quality possible but the file size will be larger. Sending large file sizes via email, for example, will result in longer receiving and download times. You also have the option to select JPEG compression to decrease the file size and, therefore, reduce the amount of time it takes to upload and download a file. OpenOffice.org allows you to use the popup menu provided to select the compression quality on a scale from 1% to 100%, with 1% being the lowest quality. After you have selected your additional export options, click the EXPORT button. 

3. A window will appear and prompt you to choose a location to save your document. Choose the location you want to save a document to in the Save In popup field.

4. In the field File Name, type the name you would like to save the file as.

5. In the File Format popup menu, make sure Portable Document Format (PDF) is selected. Then click the button EXPORT to complete the operation.

Understanding the Data Contained Within the Personal Balance Sheet
The personal balance sheet is complete. However, you may now be asking yourself, “Why would I need to create a balance sheet for myself?” or “What is the value of maintaining a personal balance sheet?” To answer these questions, let me first explain what a balance sheet is and why it is formatted the way it is. Then we will examine the personal balance sheet completed in this lesson to understand what the document is telling us. By doing so, this will hopefully develop an appreciation of how a personal balance sheet can help in analyzing the present financial status of an individual or family and, therefore, help in determining strategies to improve the financial status in the future.
Simply put, a personal balance sheet is a way for yourself or lenders to determine what your personal net worth is. Net worth, or can be referred to as equity, is calculated by adding up the value of all of the property a person or family owns and subtracting all of the money owed to lenders or individuals. Property that an individual or family owns are classified as assets, and are listed within the top half of a balance sheet. Money owed to lenders or individuals is called a liability. Liabilities, or the amount owed to multiple lenders or individuals, are listed within the bottom half of a balance sheet.
Assets and liabilities can be divided into subcategories. If you look at the personal balance sheet completed for this lesson, the assets are divided into two subcategories: current assets and long-term assets. Current assets refer to property that can be sold and converted to cash quickly, usually within one (1) year or less. Cash is the most basic asset an individual can have, and is often listed first within the assets category. Sometimes cash is referred to as a liquid asset, because it is an asset that doesn’t have to be converted to money. Long-term assets refer to property that, while could be sold to convert to money, cannot be done so as quickly or easily. Real estate and automobiles are the most common types of long-term assets an individual may own. While technically a house or automobile could be sold and converted to money within a (1) year period, it is traditionally listed as a long-term asset because many individuals finance the purchases of such assets for more than a year.
Like the assets listed within the personal balance sheet, the liabilities are also divided into two subcategories: current liabilities and long-term liabilities. Current liabilities represent money that is owed to lenders or individuals which is due for payment-in-full within one (1) year. Again, you will note that some types of debt, such as credit card payments, may not be due for payment-in-full within one (1) year because payment plans may extend beyond that time. However, most lenders regard it as a current liability because such a liability needs to be paid as quickly as possible to reduce the amount of money being paid in interest to finance the debt. Long-term liabilities represent money that is owed to lenders and individuals to be paid off in installments over a term greater than one (1) year. For example, a home mortgage is often paid off over a thirty-year (30) period, or an automobile for a four (4) year period or longer. While a lender may not require an individual to divide the assets and liabilities into subcategories within a personal balance sheet submitted with a loan application, it may help an individual to do so to identify which liabilities need to be established as a priority for paying off, or which assets could be sold quickly to turn to cash.
The document is called a balance sheet because the amount of assets an individual or family has listed in the top half of the document must balance with the total calculated for the liabilities and equity in the bottom half of the document. If you look at the completed personal balance sheet for this lesson, you will note that the Total Assets and the Total Liabilities and Equity balance to $367,100.00. If the Total Assets and Total Liabilities and Equity do not balance when preparing a personal balance sheet, each section should be analyzed to determine where the calculation error is. Preparing a personal balance sheet with the Calc spreadsheet application, however, can help you significantly reduce errors. By creating formulas in their proper location within the spreadsheet, Calc can keep track of the calculations for you.
For example, select cell D31 within the completed personal balance sheet and change the liability value for the rental property to $122,000.00. Watch what happens to the Equity (Net Worth) value and the Total Liabilities and Equity value. The Total Liabilities and Equity remains at $367,100.00, as it should because the Total Assets remain at the same figure. The formulas we had created within Calc for this exercise was designed to do this. However, because this individual’s liabilities increased and their assets remained unchanged, their net worth had to decrease. In fact, after changing the amount owed on the rental property, this individual now has a negative net worth of $600.00. This means that this individual now owes more money than they have in assets. The house and rental property are all worth more (see the value listed in the assets section) than they owe to the lenders for the respective property (as listed in the liabilities section). This individual could have a nice home, drive a decent car and have a large screen television in the living room. Yet, if they had to sell everything they own, they still wouldn’t have enough money to pay off their debts in full.
How can this happen? For young people, it is not uncommon for them to have a negative net worth because they have yet to accumulate enough assets, or they have not accumulated enough equity in the assets they own for them to be worth more than they owe. Moreover, some assets can be more of a liability than they are worth. Automobiles are a prime example of this. In terms of money spent, automobiles are one of the most significant purchases an individual can make. Yet, they depreciate in value worse than any other asset an individual can own. It is not uncommon to purchase a car and have it lose its value up to 30% as soon as it is driven off the car lot. Examining the personal balance sheet completed for this lesson, you will note that the asset value listed for this individual’s automobile is $12,000.00 (cell D16), yet this individual still owes the auto finance company $15000.00 (cell D32) for the automobile.
Other liabilities that can negatively affect an individual’s net worth are credit card loans and consumer loans. Consumer debt refers to those owed for purchases such as furniture, appliances, and electronics where credit is obtained through in-store financing, check cashing businesses or consumer credit lenders. Debt incurred through such loans are often for purchasing items that are considered consumable, which have little or no value to a lender. Electronics, such as computers or televisions for example, are usually not accepted as assets of value by lenders on a personal balance sheet because they lose their value very quickly after purchase. Therefore, as collateral for a loan, it is worthless to a lender because they could not sell off such property at an auction and obtain nearly the amount of money that it cost when it was new. Yet, when an individual purchases these items new on credit, the money that was loaned for purchasing such items still has to be repaid. Moreover, credit card loans and consumer loans usually carry with it higher interest rates, which can further negatively affect an individual’s finances.
So why would you need to prepare and maintain a personal balance sheet? Let’s use purchasing a home as an example. When you apply for a mortgage loan at a bank to obtain financing to buy a new house, the bank is likely to ask that you present to them a copy of your personal balance sheet along with the application. Just as we did with the personal balance sheet completed for this lesson, examining a personal balance sheet is to assist the banker in determining how much equity (and what kind) you have accumulated to offer as collateral for the loan. Collateral is money, or assets that can be sold and converted to money, that the bank can seize in the event an individual defaults on repaying a loan. The greater an individual’s net worth, the less of a credit risk they are to lenders. Increased equity means that, in the event that an individual defaults on a loan, there is an increased chance of being able to sell the assets and recoup the money owed on a loan. Moreover, greater equity means that an individual has a chance to turn that equity into money to keep their payments to lenders current in the event of loss of income, such as due to an illness, an injury at work or loss of employment. Other reasons for a personal balance sheet to be presented to a lender include purchasing commercial or rental property and seeking a loan to start a small business.
Working With Business Balance Sheets
A business balance sheet is very similar to a personal balance sheet. It’s purpose is the same, except that assets and liabilities declared on a business balance sheet are often different than that declared on an individual’s balance sheet. If you look at the business balance sheet provided in the lesson file, you will note that a number of assets and liabilities listed are exclusively related to a business’ operations. These include inventory, accounts receivables, accounts payables and more. However, the method of calculating assets, liabilities and equity for a business is the same as for an individual. If you wish, you can utilize the business balance sheet provided in the lesson file to further practice using Calc for formatting worksheets and performing calculations.
Additional Resources
OpenOffice.org: Description of Functions
http://documentation.openoffice.org/manuals/oooauthors2/0314CG-DescriptionOfFunctions.pdf
OpenOffice.org: Template for Creating Balance Sheets
http://documentation.openoffice.org/Samples_Templates/User/template/balancesheet.stc
U.S. Small Business Administration: Balance Sheets
http://www.sba.gov/managing/financing/balsheet.html
James Hartsfield – N.C. Cooperative Extension Service: Understanding Financial Risk
http://www.ces.ncsu.edu/sampson/pubs/farm_man/UndstandFinRisk.html
Review Questions

  1. What menu would you select to add a new worksheet within a Calc spreadsheet document?

  1. When subtracting values among cells, does the SUM function need to be utilized? Why?

  1. What type of image compression would need to be selected to export a PDF file in the highest quality possible?

  1. (True or False) Cells can be resized by utilizing either the mouse pointer within the column and row label area or by selecting the View menu.

  1. (True or False) Like the Writer word processing application bundled with OpenOffice.org, Calc has the ability to export spreadsheet documents as a Portable Document Format (PDF) file.