Microapplications for Accountants

crismaldy

Chapter2.pdf

Home >Business & Finance homework help >Accounting homework help >Microapplications for Accountants

LEARNING OBJECTIVES

Certain elements of the accounting cycle are completed in the same

manner each period. Among these are the financial statements

which, for a given business, will have a consistent appearance from

one period to another. In this chapter, you will examine three of

the four financial statements: the income statement, the statement

of owner’s equity, and the balance sheet. Because these statements

tend to have the same appearance from one period to the next,

creating an Excel file as a base can significantly increase efficiency.

You will review various Excel features regarding row and column

manipulation, text alignment/control, and worksheet management.

Doing so will allow you to create a uniform appearance across

multiple versions of financial statements, as well as other

accounting cycle components.

2 Financial Statements ■ Create an income

statement

■ Create a statement of owner's equity

■ Create a balance sheet

■ Alter columns, rows, and text alignment

■ Add, remove, and adjust worksheets

EFA.indb 31 7/13/18 5:50 PM

32 Excel for Accounting Chapter 2: Financial Statements

City Music World City Music World is a retail store that sells musical equipment and offers lessons for beginning and experienced musicians. Now that the company is using Excel to increase its efficiency, it must establish templates for its financial statements that can be used every period (City Music World creates monthly financial statements). You are respon- sible for creating the current period’s financial statements such that they can be easily updated for subsequent periods.

In this chapter, you will create financial statements in Microsoft Excel. You will examine techniques for adjusting columns and rows, altering the appearance of text, and altering the appearance of individual cells. You will finish with an examination of worksheet management.

Many Excel features can be used to improve the appearance of an income statement.

P R O J E C T

EFA.indb 32 7/13/18 5:50 PM

Accounting Refresher: Financial Statements 33

Financial Statements Four financial statements are completed near the end of the accounting cycle to summa- rize activity for the period and indicate the company’s current financial position. The order in which the financial statements are completed is as follows:

• Income statement

• Statement of owner’s equity

• Balance sheet

• Statement of cash flows

We will examine the first three financial statements here.

Income Statement

The income statement displays revenues and expenses for the period. The final line within the income statement calculates revenues minus expenses and is referred to as either net income (if revenues exceed expenses) or net loss (if expenses exceed revenues).

At the top of the income statement, the three-line header displaying the company name on the top line and Income Statement on the second line does not simply list the final date of the period. Rather, it shows: for the month/year/period ended xx/xx/xxxx. This phrasing indicates that the income statement conveys activity for an entire period of time, which in this case is a month.

This income statement covers the period from 8/1/2016 through 8/31/2016.

Statement of Owner’s Equity

The statement of owner’s equity is a reconciliation that combines the beginning owner’s equity with account changes that occurred during the period in order to arrive at the ending owner’s equity. Items added to the beginning owner’s equity include net income and investments made by the owner. Items deducted from beginning owner’s equity include net loss and owner withdrawals.

TIP! The Withdrawals account is used by a sole-proprietorship. For a corporation, the equivalent account is Dividends.

As the statement of owner’s equity displays activity for an entire period of time (similar to the income statement), the third line of the header displays: for the month/year/period ended xx/xx/xxxx.

(continued)

EFA.indb 33 7/13/18 5:50 PM

34 Excel for Accounting Chapter 2: Financial Statements

Balance Sheet

The balance sheet displays the balances within all Asset, Liability, and Owner’s Equity accounts. It shows that the accounting equation is in balance as of the end of the period. The balance sheet is a snapshot of the financial status of a company.

The balance sheet displays account balances as of the final day of the period; that is, it does not convey information regarding the activity during the period (it shows only the ending balances). Unlike the previous two financial statements, the third line of the balance sheet header shows the final date of the period.

Modifying Columns and Rows You can insert and delete columns, rows, and cells in your worksheets. This can be particularly bene- ficial when completing financial statements. For example, if you determine that an account has been omitted, a row can be added in the proper location. The ability to add and delete rows ensures that a template can be created for each financial statement and then modified for different accounts when updating from one period to the next.

You can insert or delete columns and rows by first selecting the desired columns or rows in one of several ways:

• Clicking a single column or row heading

• Dragging to select adjacent headings

• Holding [Ctrl] while clicking nonadjacent headings

TIP! You can also right-click a column or row header to initiate the insertion or deletion.

Once you have selected the desired rows or columns, you can either insert columns or rows beside those that are highlighted (to the left of selected columns or above selected rows) or delete the high- lighted columns or rows.

Home→Cells→Insert Cells €→Insert Sheet Columns or Insert Sheet Rows

Home→Cells→Delete Cells €→Delete Sheet Columns or Delete Sheet Rows

EFA.indb 34 7/13/18 5:50 PM

Hiding Columns and Rows 35

Modifying Cells When deleting a cell or a range, you must indicate how to shift the surrounding cells to either make room for the addition or fill the space from the deletion. Doing so can impact the cells both below and to the right of the insertion or deletion. Therefore, take care when altering a worksheet in this manner.

There are four shift cells options available when you insert cells.

Home→Cells→Insert Cells €→Insert Cells | [Ctrl]+[Shift]+[=] Home→Cells→Delete Cells €→Delete Cells | [Ctrl]+[-]

Hiding Columns and Rows To hide columns or rows, you must first highlight them in the same manner used to insert/ delete columns or rows. And, you can hide columns or rows by right-clicking those that are highlighted. Alternatively, when using the Ribbon or keyboard shortcuts, you can highlight any cells within the columns or rows you wish to hide.

Hidden rows and columns are not visible and do not print, but they do remain part of the work- sheet. You can use the Unhide command to make hidden rows and columns visible once again.

Column C and row 2 are hidden.

The Hide command is useful when financial statements are used for managerial purposes. For example, commentary regarding a company’s performance can be entered below the income statement so that management can review these performance indicators. This commentary can then be hidden from view when sharing the income statement with parties to whom it does not apply.

EFA.indb 35 7/13/18 5:50 PM

36 Excel for Accounting Chapter 2: Financial Statements

To Unhide a hidden column or row, you first highlight the headers of the columns or rows surrounding the hidden ones. For example, to unhide column E, first highlight the columns D and F headers.

NOTE! If column A (or row 1) is hidden, you can unhide it by first highlighting the column B (or row 2) header and the Select All button and then choosing Unhide.

Home→Cells→Format→Hide & Unhide→Hide Rows | [Ctrl]+[9] Home→Cells→Format→Hide & Unhide→Unhide Rows | [Ctrl]+[Shift]+[»] Home→Cells→Format→Hide & Unhide→Hide Columns | [Ctrl]+[0] Home→Cells→Format→Hide & Unhide→Unhide Columns | [Ctrl]+[Shift]+[¶]

Develop Your Skills EA2-D1

In this exercise, you will complete City Music World’s income statement for the month ending December 31, 2016. Month-end revenue and expense account balances are as follows: Sales Revenue equals $42,000, Rent Expense equals $12,000, Insurance Expense equals $4,900, Telephone Expense equals $3,100, and Miscellaneous Expense equals $2,600.

1. Start Excel. Open a Blank Workbook and save the file in your Chapter 02 folder as: EA2-D1-FinancialStatements-[YourName]

2. Enter this data:

cell A1 City Music World

cell A2 Income Statement

cell A3 For the Month Ended December 31, 2016

3. Select cell A5, type Sales Revenue and tap [Tab] three times, and then type 42,000 and tap [Enter].

When multiple revenue accounts exist, the header Revenues is entered above the account names. Here we have only one revenue account, so no header is necessary.

Enter Expenses

4. Type Expenses: in cell A6.

5. Right-click the row 6 header and choose Insert.

It is customary to include a blank line between revenues and expenses.

EFA.indb 36 7/13/18 5:50 PM

Hiding Columns and Rows 37

6. Enter this data:

Cell B8 Rent Expense

Cell B9 Insurance Expense

Cell B10 Telephone Expense

Cell B11 Miscellaneous Expense

Cell C8 12,000

Cell C9 4,900

Cell C10 3,100

Cell C11 2,600

The amounts are entered in column C so that when they are later totaled in cell D12, the total will appear directly below total revenues (in cell D5). The result is that only total revenues, total expenses, and ultimately net income are displayed in column D.

7. Select cell A12, type Total Expenses and tap [Tab] three times, and then type 22,600 and tap [Enter].

This total represents the sum of the four figures in the range C8:C11.

Complete the Income Statement

8. Select cell A13, type Net Income and tap [Tab] three times, and then type 19,400 and tap [Enter].

Net Income is entered here because total revenues exceed total expenses. The amount is determined by subtracting total expenses of $22,600 from total revenues of $42,000. (When total expenses are higher than total revenues, Net Loss is used.)

9. Highlight the range C5:D13, choose Home→Number→Accounting, and then click Decrease Decimal twice.

Every dollar amount within the income statement now appears with a dollar sign and zero decimal places. Alternatively, financial statements can also be displayed such that the first and last figures include a dollar sign, while all other figures exclude them.

10. Select the columns B–D headers and then double-click the border between the columns B–C headers.

Because multiple columns were highlighted here, AutoFit was applied to all of them simultaneously when you double-clicked the border.

11. Right-click the column A header, choose Column Width, and then type 4 and tap [Enter].

12. In cell A16, type this text: Review Miscellaneous Expenses – Potential Savings?

13. Select cell A16 and choose Home→Cells→ Format→Hide & Unhide→Hide Rows.

Although you have included the note in cell A16 as a reminder for yourself, you don’t want it displayed when you distribute the income statement to others. Therefore, you have now hidden row 16.

EFA.indb 37 7/13/18 5:50 PM

38 Excel for Accounting Chapter 2: Financial Statements

14. Save your file.

Unless otherwise directed, keep your file open at the end of each exercise.

Managing Worksheets As you work with more complex workbooks, workbook management will increase in impor- tance. You can organize a workbook by inserting, deleting, and rearranging worksheets. You also can rename worksheet tabs and apply colors to them. These options can be accessed via the Ribbon, by right-clicking, and by using keyboard controls.

1. Navigation buttons allow you to scroll left and right between worksheet tabs.

2. The New Sheet button inserts a new sheet to the right of the active sheet tab.

3. The active worksheet is displayed prominently.

WARNING! You cannot undo the Delete Worksheet command. If you issue the command by mistake, you can close the workbook without saving and then reopen it to recover the lost worksheet.

When completing period-end financial statements, it is beneficial to maintain all statements in a single Excel workbook, each on a separate worksheet. Alternatively, multiple versions of the same financial statement (representing different periods) can be maintained within a single workbook.

Home→Cells→Insert Cells €→Insert Sheet | [Shift]+[F11] Home→Cells→Delete Cells €→Delete Sheet

Home→Cells→Format €→Rename Sheet

Home→Cells→Format €→Move or Copy Sheet

Home→Cells→Format €→Tab Color

31 2

EFA.indb 38 7/13/18 5:50 PM

Changing Vertical Alignment and Rotating Text 39

Changing Vertical Alignment and Rotating Text

Vertical alignment positions cell contents between the top and bottom of the cell. Vertical alignment options include Top, Bottom, Center, and Justify. The default alignment is bottom. The Justify option (which can be selected only via the Alignment dialog box launcher) evenly distributes unused space between lines in a multiple-line entry so text fills the cell from the top edge to the bottom edge. Within financial statements, vertical alignment is primarily used to ensure that headers and account names stand out.

Home→Alignment→Top Align

Home→Alignment→Middle Align

Home→Alignment→Bottom Align

Rotating Text The Orientation feature has several rotation options. When you change the orientation, Excel increases the row height to accommodate the rotated text. While rotating text can make titles more aesthetically pleasing, make sure the rotation doesn’t increase row height so worksheet data becomes difficult to view.

Although standard financial statements do not typically contain rotated text, the use of a rota- tion option can make separate commentary (that is positioned below the financial statement) stand out to the end user.

Home→Alignment→Orientation

Home→Alignment→Orientation→Format Cell Alignment

Develop Your Skills EA2-D2

In this exercise, you will modify the income statement and create the statement of owner’s equity for City Music World. Note that the beginning balance within the Harold Cameron, Capital account was $184,000, and the owner withdrew $14,000 during the month.

1. Save your file as: EA2-D2-FinancialStatements-[YourName]

2. Right-click the active worksheet tab, choose Rename, and then type Income Statement and tap [Enter].

3. Type Monthly Net Income is Acceptable in cell A17.

This note is placed below the income statement to direct the user to examine the net income. By next changing the orientation of this note, it will further stand out when the financial statement is viewed.

4. Select cell A17 and choose Home→Alignment→Orientation→Format Cell Alignment to open the Format Cells dialog box.

EFA.indb 39 7/13/18 5:50 PM

40 Excel for Accounting Chapter 2: Financial Statements

5. Follow these steps to change the text orientation:

A. Click the Alignment tab, if necessary.

B. Type 10 in the Degrees box.

C. Click OK.

Complete the Statement of Owner’s Equity

6. If a second worksheet tab is visible to the right of the Income Statement tab, select that second tab. If a second worksheet tab is not visible, click New Sheet.

7. Choose Home→Cells→Format→Rename Sheet and then type Statement of O.E. and tap [Enter].

The abbreviation O.E. is used for Owner’s Equity.

8. On the Income Statement tab, select and then right-click the range A1:A3 and choose Copy.

9. On the Statement of O.E. tab, click cell A1 and choose Home→Clipboard→Paste.

10. Type Statement of Owner’s Equity in cell A2.

The three-line header for the statement of owner’s equity is the same as the header for the income statement (with the exception of the second line). Therefore, it is efficient to copy and paste the income statement header here.

EFA.indb 40 7/13/18 5:50 PM

Using Alignment, Indent, and Text Control Options 41

11. Enter this data:

Cell A5 Harold Cameron, Capital – 12/1/2016

Cell C5 184,000

Cell A6 Net Income

Cell C6 19,400

Cell C7 203,400

The net income is taken from the income statement, which you completed first, so the $19,400 can now be inserted into the statement of owner’s equity. The $203,400 is a subtotal that represents the sum of the beginning capital balance and net income.

12. Enter this data:

Cell A8 Harold Cameron, Drawing

Cell C8 14,000

Cell A9 Harold Cameron, Capital – 12/31/2016

Cell C9 189,400

13. Highlight the range C5:C9, choose Home→Number→Accounting, and then click Decrease Decimal twice.

14. Highlight the headers for columns A–C and then double-click the border between the columns A–B headers.

15. In cell B1, choose Home→Cells→Format→Column Width and then type 2 and click OK.

16. Save your file.

Using Alignment, Indent, and Text Control Options

Excel allows you to alter how text is aligned within cells. In addition to the standard left, center, and right horizontal alignments, you can indent cell contents from either edge. Additionally, you can change a worksheet’s appearance by merging cells, wrapping lengthy text within a cell, and entering a line break within a cell.

Aligning Entries To align an entry within a cell is to shift that entry either to the left, center, or right of the cell. By default, text entries are left aligned and number entries are right aligned. Often the appearance of a financial statement can be greatly improved through the effective use of alignment options.

Home→Alignment→Align Left

Home→Alignment→Center

EFA.indb 41 7/13/18 5:50 PM

42 Excel for Accounting Chapter 2: Financial Statements

Home→Alignment→Align Right

Indenting Cell Entries Indenting a cell entry offsets the entry from either the left or right edge of the cell, depending on the manner in which it is aligned. If a cell entry is left aligned, it will indent from the left edge, and if it is right aligned, it will indent from the right edge.

Indenting can be used in a variety of instances, such as when entering a credit within a journal entry or listing multiple expenses within an income statement.

Home→Alignment→Decrease Indent

Home→Alignment→Increase Indent

Merging Cells Merging cells means to combine cells. You can merge cells both vertically and horizontally, and merged cells will behave as one large cell. The merged cell takes on the name of the top-left cell in the merged range. For example, if you merge cells A1:E1, the resulting merged cell will be named cell A1.

WARNING! If you merge two or more cells, each of which contains data, some data will be lost.

The Merge & Center command merges selected cells and sets center alignment to it. This technique can be used to center a heading across columns, but it can only be used on one row at a time. You can split the merged and centered cell by clicking the Merge & Center button again.

The Merge Across command merges the contents of multiple rows simultaneously. For example, if you use Merge & Center on the range A1:D2, the result would be one large merged cell over this range. However, if you use Merge Across on this same range, the result would be two merged cells (neither of which is centered) within the ranges A1:D1 and A2:D2.

Either Merge & Center or a combination of Merge Across and Center alignment should be used for the three-line header at the top of every financial statement.

Home→Alignment→Merge & Center

Home→Alignment→Merge & Center €→Merge Across

Home→Alignment→Merge & Center €→Unmerge Cells

Wrapping Text and Entering Line Breaks The Wrap Text option forces text to wrap within a cell, ensuring that no text is cut off. When entering headers at the top of a table (such as Owner’s Equity as part of the accounting equa- tion), this command can be particularly useful. An alternative to Wrap Text is to manually

EFA.indb 42 7/13/18 5:50 PM

Applying Borders and Fills 43

insert a line break. Doing so moves all text after the line break to the next line within the same cell.

With both techniques, row height increases to accommodate the additional lines of wrapped text. However, you may still need to adjust the column width and/or row height after either of these commands is applied.

A line break has been entered after Owner’s that forces Equity to a second line. The line break can be removed by clicking after the “s” in Owner’s and tapping [Delete].

Home→Alignment→Wrap Text

[Alt]+[Enter] to insert a line break

Applying Borders and Fills Borders are lines around the cell edges that both print and display in the worksheet. Fills are background shading and pattern effects that fill entire cells. While borders serve an essential purpose within each financial statement, the fill color option should be used sparingly.

Borders The Borders button lets you add borders to cell edges. When you click the Borders €, a list of options appears.

1. The Borders € displays the image of the last border applied.

2. These border options are applied one edge at a time to each cell in the selected range.

EFA.indb 43 7/13/18 5:50 PM

44 Excel for Accounting Chapter 2: Financial Statements

While borders can be used to distinguish elements within many accounting-related work- sheets, they are particularly important in financial statements. A single underline within a financial statement indicates that a calculation (typically addition or subtraction) is being performed on the above figures; a double underline indicates that a figure is the final one for an entire statement or a portion of the statement.

For example, when multiple expenses are listed within an income statement, a single underline is displayed below the final expense amount to indicate that all are being added to arrive at the total expense figure. Then a double underline below the net income or loss within the income statement indicates that this is the final figure.

Home→Font→Borders

Fill Colors The Fill Color button fills the background of selected cells with color. When you click the Fill Color €, a palette of colors appears. You can apply a color to all selected cells by choosing it from the palette and remove a color with the No Fill option.

1. Remove a color fill from cells

2. Access a palette of standard colors or create a custom color

3. Shows the most recently used color

TIP! Printing a test version of a worksheet allows you to see how your color choices will print, which is especially important for grayscale printers.

While fill color can help to ensure that, in particular, financial statement headers stand out, they can also be distracting within business reports. A good rule of thumb is to use fill color in only one or two locations within your worksheet and to use only muted colors unless there is a compelling reason to do otherwise.

Home→Font→Fill Color

EFA.indb 44 7/13/18 5:50 PM

Applying Borders and Fills 45

Develop Your Skills EA2-D3

In this exercise, you will modify the income statement and the statement of owner’s equity, and you’ll create a balance sheet. Month-end asset and liability account balances for City Music World are as follows: Cash equals $104,000, Accounts Receivable equals $31,000, Equipment equals $83,000, and Accounts Payable equals $28,600.

1. Save your file as: EA2-D3-FinancialStatements-[YourName]

2. On the Income Statement tab, highlight the range A1:D1 and choose Home→ Alignment→Merge & Center.

The top line within the three-line header has now been centered across columns A–D.

3. Merge and center the range A2:D2 and the range A3:D3.

4. Select the range A1:A3.

Notice that, as a result of the previously applied Merge & Center commands, the range A1:D3 is highlighted when you select the range A1:A3.

5. Choose Home→Font→Fill Color €→Blue, Accent 1, Lighter 80%.

The header now has a more distinct appearance from the rest of the statement. Note that the name of each fill color (such as Blue, Accent 1, Lighter 80%) appears as a ScreenTip when you hover the mouse pointer over the colors.

6. On the Statement of O.E. tab, highlight the range A1:C3 and choose Home→Alignment→Merge & Center €→Merge Across.

Each row within the three-line header (across columns A–C) has been merged.

EFA.indb 45 7/13/18 5:50 PM

46 Excel for Accounting Chapter 2: Financial Statements

7. Choose Home→Alignment→Center.

8. Select the range A1:A3 and choose Home→Font→Fill Color.

Note that the blue fill shade you chose earlier is applied to the range.

9. On the Income Statement tab, select cells C11 and D12 and then choose Home→ Font→Borders €→Bottom Border.

The border below cell C11 indicates that all expense figures are being added to determine total expenses. The border below cell D12 indicates that the total expenses are being subtracted from the sales revenue to arrive at the net income.

10. Select cell D13 and choose Home→Font→Borders €→Bottom Double Border.

The double border below cell D13 indicates that the net income figure is the final figure in the income statement.

11. On the Statement of O.E. tab, select cells C6 and C8 and then choose Home→Font→ Borders €→Bottom Border.

12. Select cell C9 and choose Home→Font→Borders €→Bottom Double Border.

Create the Balance Sheet and Begin Entering Data

13. If a third worksheet tab is visible to the right of the Statement of O.E. tab, select it; other- wise, click New Sheet.

14. Right-click the new worksheet tab, choose Rename, and then type Balance Sheet and tap [Enter].

15. On the Statement of O.E. tab, copy the range A1:A3.

16. Switch to the Balance Sheet tab, select cell A1, and press [Ctrl]+[V] to paste.

17. Type Balance Sheet in cell A2 and 12/31/2016 in cell A3, and then tap [Enter] twice.

This three-line header displays a single date on the third line, as the balance sheet is a snapshot that conveys the position of a business on a single date but not information regarding the business’ activity over a period of time.

EFA.indb 46 7/13/18 5:50 PM

Applying Borders and Fills 47

18. Enter this data:

Cell A5 Assets:

Cell A7 Cash

Cell C7 104,000

Cell A8 Accounts Receivable

Cell C8 31,000

Cell A9 Equipment

Cell C9 83,000

Cell A11 Total Assets

Cell C11 218,000

The balance sheet can be set up either with the assets positioned above or to the left of the liabilities and owner’s equity. You displayed them above.

Cell C11 contains the figure $218,000, which represents the sum of the three asset figures.

Complete the Balance Sheet

19. Enter this data:

Cell A13 Liabilities:

Cell A15 Accounts Payable

Cell C15 28,600

Cell A17 Owner’s Equity:

Cell A19 Harold Cameron, Capital

Cell C19 189,400

Cell A21 Total Liabilities & Owner’s Equity

Cell C21 218,000

20. Highlight the range C7:C21, choose Home→Number→Accounting Number Format, and then click Decrease Decimal twice.

21. Select cells A5, A13, and cell A17 and choose Home→Font→Bold.

22. Select cells C9 and C19 and choose Home→Font→Borders €→Bottom Border.

23. Select cells C11 and C21 and click the Bottom Double Border button.

24. Select cell A8 and choose Home→Alignment→Wrap Text.

You must increase the column width in order for Accounts Receivable to display properly. This will be done after the next few steps are completed.

25. Select cell A15 and then click within the Formula Bar to the left of the P in Payable.

26. Tap [Backspace] and then hold [Alt], tap [Enter], and tap [Enter] again to confirm the entry.

Tapping [Backspace] eliminated the space between Accounts and Payable. Holding [Alt] and tapping [Enter] applied a line break between the words, moving Payable to a new line within the same cell. The appearance of this entry will be corrected within the next few steps.

EFA.indb 47 7/13/18 5:50 PM

48 Excel for Accounting Chapter 2: Financial Statements

27. Select cell A21 and choose Home→Alignment→Wrap Text.

28. Wrap the text in cell A19, choose Home→Cells→Format→Column Width, and then type 22 and click OK.

Although text wrapping has been applied to cells A8 and A19, the entries within these cells now appear on one row, as the column is wide enough to allow this. However, the entry in cell A15, to which a line break was applied, has remained on two lines within the same cell.

29. Change the width of cell A19 to 15.

Now that the column width has been reduced, all three cells display the respective entries on multiple lines.

30. Double-click the border between the column C–D headers.

By double-clicking the right border of the column C header, you automatically decreased the width of the highlighted column to fit its entries.

31. Select cell B5 and change the column width to 2.

Because the highlight is in column B, the width of this column has changed.

32. On all three tabs, set the range A1:A3 in bold formatting.

33. On the Balance Sheet tab, highlight the range A7:A9 and choose Home→Alignment→ Increase Indent.

34. Select the headers for rows 6, 10, 14, 16, 18, and 20.

35. Choose Home→Cells→Format→Row Height, type 5, and tap [Enter].

36. Set the height of rows 19 and 21 to 32.

37. Save and close your file.

EFA.indb 48 7/13/18 5:50 PM

Applying Borders and Fills 49

What Are the Benefits of Using Excel to Create Financial Statements?

Similar to journal entries, financial statements can be automatically generated using computerized accounting systems. Although many companies use such systems, it’s not uncommon for small businesses to opt against them—often due to a desire to minimize costs and a lack of willingness to learn the computerized process. For these small businesses, Excel can increase efficiency and allow for effective comparisons to be made across accounting periods.

Creating templates in Excel for each of the four financial statements means you can copy and update the templates for each subsequent period, thus streamlining work. Through the use of multiple worksheet tabs, the financial statements can be main- tained within the same file, allowing easy comparison between different periods.

Although increased efficiency and the ability to compare data are significant benefits, the knowledge gained from manu- ally creating financial statements can be far more impactful. When using financial statements generated through accounting software, the user has not participated in their creation, whereas a careful review of every line item is inevitable when manually creating financial statements. As such, many owners find that they are better equipped to run their businesses when they use Excel to generate their monthly statements.

EFA.indb 49 7/13/18 5:50 PM

50 Excel for Accounting Chapter 2: Financial Statements

Self-Assessment Check your knowledge of this chapter’s key concepts and skills using the Self-Assessment here or in your eLab course.

1. When you delete cells, you must indicate how surrounding cells should shift. True False

2. A hidden row will appear when the worksheet is printed. True False

3. Neither row A nor column 1 can be hidden. True False

4. The deletion of a worksheet cannot be reversed with Undo. True False

5. All vertical alignment options are accessed directly on the Ribbon. True False

6. When a rotation option is applied, row height automatically adjusts to fit the rotated cell entry.

7. By default, text entries are right-aligned and number entries are left-aligned. True False

8. A cell entry can only be indented if it is left-aligned. True False

9. The Merge & Center command can be used to merge individual rows of data simultaneously. True False

10. The most recently selected border option is displayed on the Border button on the Ribbon. True False

11. When right-clicking to insert cells, which option does NOT appear? Entire Column Shift Cells Up Shift Cells Right Entire Row

12. Which element of a worksheet tab CANNOT be changed? Name Height Location Color

13. Which of these is NOT a horizontal alignment option? Middle Left Right Center

14. What does a single bottom border within a financial statement indicate? The above figure is the final figure within the statement. The above figure should be examined closely by management. A calculation is being performed on the figures above the border. The below figure is the final figure within the statement.

EFA.indb 50 7/13/18 5:50 PM

Self-Assessment 51Self-Assessment 51

15. How is a line break applied? [Ctrl]+[Enter] [Ctrl]+[Shift] [Alt]+[Enter] [Alt]+[Shift]

16. Which of these options is NOT available on the Merge & Center menu on the Ribbon? Merge & Center Center Merge Across Unmerge Cells

17. The Increase Indent button is displayed within which group on the Home tab? Clipboard Font Alignment Number

18. Which of these options does NOT appear on the Fill Color menu? Theme Colors Gradient Colors Standard Colors More Colors

19. When a merge command is applied, the new merged cell takes on the name of which former cell? The cell formerly located in the top left of the merged area The cell formerly located in the top right of the merged area The cell formerly located in the bottom left of the merged area The cell formerly located in the bottom right of the merged area

20. When the Increase Indent command is applied, the cell entry: is always indented from the right edge of the cell. is indented from the right or left edge of the cell, depending on the type of entry. is always indented from the left edge of the cell. None of these; the user chooses how to indent.

EFA.indb 51 7/13/18 5:50 PM

52 Excel for Accounting Chapter 2: Financial Statements

Reinforce Your Skills

EA2-R1 Create Financial Statements for Pro Painting Inc.

In this exercise, you will create a monthly income statement, statement of owner’s equity, and balance sheet in Excel for Pro Painting Inc. With the exception of the Giovanni Keith, Capital account (the balance for which is from 1/1/2016), the company had the following account balances as of 1/31/2016.

Accounts Receivable $600 Maintenance Expense $800

Auto Expense $950 Notes Payable $450

Cash $3,100 Rent Revenue $800

Delivery Expense $350 Service Revenue $3,600

Giovanni Keith, Capital – 1/1/2016 $5,050 Supplies $700

Giovanni Keith, Drawing $500 Tools $1,100

Insurance Expense $1,800

1. Open a Blank Workbook and save the file in your Chapter 02 folder as: EA2-R1-FinancialStatements-[YourName]

2. If necessary, use the New Sheet button to add worksheet tabs for a total of three.

3. Follow these steps to rename the tabs:

• Double-click the first worksheet tab and type: Income Statement • Right-click the second worksheet tab, choose Rename, and type: Statement of O.E.

• Select the third worksheet, choose Home→Cells→Format→Rename Sheet, and type: Balance Sheet

4. On the Income Statement tab, type the company name in cell A1, the statement name in cell A2, and the date in an appropriate format in cell A3.

5. Highlight the range A1:D3, choose Home→Alignment→Merge & Center €→ Merge Across, and then center the text.

6. On the Statement of O.E. tab, type the company name in cell A1, the statement name in cell A2, and the date in an appropriate format in cell A3.

7. Merge and center the contents in the ranges A1:C1, A2:C2, and A3:C3.

8. Repeat steps 6–7 on the Balance Sheet tab, taking care to use the correct statement name (cell A2) and date format (cell A3).

Enter Data for the Income Statement

9. On the Income Statement tab, type a revenue header in cell A5.

EFA.indb 52 7/13/18 5:50 PM

Reinforce Your Skills 53

10. Enter the data as described:

In this cell or range: Enter the data for:

Range B6:B7 Revenue account names (largest to smallest)

Range C6:C7 Revenue account balances

Cell A8 Total revenue description

Cell D8 Total revenue amount

Cell A10 Expenses header

Range B11:B14 Expense account names (largest to smallest)

Range C11:C14 Expense account balances

Cell A15 Total expenses description

Cell D15 Total expenses

Cell A16 Net income or loss entry

Cell D16 Net income or loss amount

11. Select cells C7, C14, and D15 and then choose Home→Font→Border €→ Bottom Border.

12. Apply a bottom double border to cell D16.

13. In cell A18, type Review maintenance expense for potential savings and then choose Home→Alignment→Orientation→Format Cell Alignment and change the number in the Degrees box to 12.

14. Right-click the row 18 header and choose Hide.

15. Highlight the range C6:D16, choose Home→Number→Accounting, and then click Decrease Decimal twice.

16. Right-click the column A header and change the column width to 6.

17. Highlight the headers for columns B–D and double-click the border between the columns B–C headers.

18. Highlight the range A1:A3, apply bold formatting, and then choose Home→Font Fill Color € and select Blue-Gray, Text 2, Lighter 80%.

Enter Data for the Statement of Owner’s Equity

19. On the Statement of O.E. tab, enter the data as described:

In this cell: Enter the data for the:

Cell A5 Beginning equity account name

Cell C5 Beginning equity amount

Cell A6 Name of the item to be added

Cell C6 Amount to be added

Cell C7 Subtotal of cells C5 and C6

Cell A8 Name of the item to be deducted

Cell C8 Amount to be deducted

Cell A9 Ending equity account name

Cell C9 Ending equity amount

EFA.indb 53 7/13/18 5:50 PM

54 Excel for Accounting Chapter 2: Financial Statements

20. Apply a bottom border to cells C6 and C8 and apply a bottom double border to cell C9.

21. Select cells A5 and A9 and choose Home→Alignment→Wrap Text.

22. Set the range C5:C9 in the Accounting number format with no decimal places.

23. Using the right-click method, set the width of column A to 23 and the width of column B to 2.

24. Double-click the border between the columns C–D headers.

25. Select the headers for rows 5 and 9, choose Home→Cells→Format→Row Height, and set the height to 30.

26. Highlight the range A1:A3, apply bold formatting, and then choose Home→Font→ Fill Color €→Blue-Gray, Text 2, Lighter 80%.

Enter Data for the Balance Sheet

27. On the Balance Sheet tab, enter the data as described:

In this cell or range: Enter the data for the:

Cell A5 Asset header

Range A6:A9 Asset account names (in the order Cash, Accounts Receivable, Supplies, Tools)

Cell A10 Total asset description

Cell A12 Liability header

Cell A13 Liability account name

Cell A15 Owner’s equity header

Cell A16 Equity account name

Cell A18 Total liability and owner’s equity description

28. Type the appropriate asset amount in cell C6. Continue by entering appropriate amounts in the range C7:C10 as well as in cells C13, C16, and C18.

29. Highlight the range A6:A9 and choose Home→Alignment→Increase Indent twice.

30. Apply a bottom border to cells C9 and C16 and apply a bottom double border to cells C10 and C18.

31. Select cell A7 and then click in the Formula Bar to the left of the R in Receivable, tap [Backspace], hold [Alt], tap [Enter], release [Alt], and tap [Enter].

32. Select cell A18 and choose Home→Alignment→Wrap Text.

33. Set the range C6:C18 in the Accounting number format with no decimal places.

34. Using the right-click method, set the width of column A to 21 and the width of column B to 2.

35. Double-click the border between the columns C–D headers.

36. Select the headers for rows 7 and 18, choose Home→Cells→Format→Row Height, and set the height to 30.

EFA.indb 54 7/13/18 5:50 PM

Reinforce Your Skills 55

37. Set the row height of cells A14 and A17 to 5.

38. Highlight the range A1:A3, apply bold formatting, and then choose Home→Font→ Fill Color €→Blue-Gray, Text 2, Lighter 80%.

39. Save and close your file.

EA2-R2 Create Financial Statements for Deep Freeze Co.

In this exercise, you will create a monthly income statement, statement of owner’s equity, and balance sheet in Excel for Deep Freeze Co. With the exception of the Alistair Rowe, Capital account (the balance for which is from 6/1/2016), the company had the following account balances as of 6/30/2016.

Accounts Payable $1,100 Rent Revenue $2,000

Alistair Rowe, Capital – 6/1/2016 $21,700 Sales Revenue $8,100

Alistair Rowe, Drawing $1,600 Shipping Expense $1,000

Building $9,500 Supplies $600

Cash $14,000 Telephone Expense $1,800

Insurance Expense $2,100 Utilities Expense $2,300

1. Open a Blank Workbook and save the file in your Chapter 02 folder as: EA2-R2-FinancialStatements-[YourName]

2. If necessary, use the New Sheet button to add worksheet tabs for a total of three.

3. Follow these steps to rename the tabs:

• Double-click the first worksheet tab and type: Income Statement • Right-click the second worksheet tab, choose Rename, and type: Statement of O.E.

• Select the third worksheet, choose Home→Cells→Format→Rename Sheet, and type: Balance Sheet

4. On the Income Statement tab, type the company name in cell A1, the statement name in cell A2, and the date in an appropriate format in cell A3.

5. Highlight the range A1:D3, choose Home→Alignment→Merge & Center €→ Merge Across, and then center the text.

6. On the Statement of O.E. tab, type the company name in cell A1, the statement name in cell A2, and the date in an appropriate format in cell A3.

7. Merge and center the contents in the ranges A1:C1, A2:C2, and A3:C3.

8. Copy the range A1:C3, click the Balance Sheet tab, select cell A1, and press [Ctrl]+[V].

9. Change the entry in cell A2 to the correct statement name and change the entry in cell A3 to an appropriate date format.

EFA.indb 55 7/13/18 5:50 PM

56 Excel for Accounting Chapter 2: Financial Statements

Enter Data for the Income Statement

10. On the Income Statement tab, type a revenue header in cell A5.

11. Enter the data as described:

In this cell or range: Enter the data for:

Range B6:B7 Revenue account names (largest to smallest)

Range C6:C7 Revenue account balances

Cell A8 Total revenue description

Cell D8 Total revenue amount

Cell A10 Expenses header

Range B11:B14 Expense account names (largest to smallest)

Range C11:C14 Expense account balances

Cell A15 Total expenses description

Cell D15 Total expenses

Cell A16 Net income or loss entry

Cell D16 Net income or loss amount

12. Select cells C7, C14, and D15 and then choose Home→Font→Borders €→ Bottom Border.

13. Apply a bottom double border to cell D16.

14. In cell A18, type Review rent revenue to maximize profit and then choose Home→Alignment→Orientation→Format Cell Alignment and change the number in the Degrees box to 8.

15. Highlight the range C6:D16, choose Home→Number→Accounting Number Format, and then click Decrease Decimal twice.

16. Right-click the column A header and change the column width to 6.

17. Highlight the headers for columns B–D and double-click the border between the columns B–C headers.

18. Select the range A1:A3 and choose Home→Font→Bold.

Enter Data for the Statement of Owner’s Equity

19. On the Statement of Owner’s Equity tab, enter the data as described:

In this cell: Enter the data for the:

Cell A5 Beginning equity account name

Cell C5 Beginning equity amount

Cell A6 Name of the item to be added

Cell C6 Amount to be added

Cell C7 Subtotal of cells C5 and C6

Cell A8 Name of the item to be deducted

Cell C8 Amount to be deducted

Cell A9 Ending equity account name

Cell C9 Ending equity amount

EFA.indb 56 7/13/18 5:50 PM

Reinforce Your Skills 57

20. Apply a bottom border to cells C6 and C8 and apply a bottom double border to cell C9.

21. Select cells A5 and A9 and choose Home→Alignment→Wrap Text.

22. Set the range C5:C9 in the Accounting number format and with no decimal places.

23. Using the right-click method, set the width of column A to 22 and the width of column B to 2.

24. Double-click the border between the columns C–D headers.

25. Select the headers for rows 5 and 9, choose Home→Cells→Format→Row Height, and set the height to 30.

26. Set the range A1:A3 in bold formatting.

Enter Data for the Balance Sheet

27. On the Balance Sheet tab, enter the data as described:

In this cell or range: Enter the data for the:

Cell A5 Asset header

Range A6:A8 Asset account names (in the order Cash, Supplies, Building)

Cell A9 Total asset description

Cell A11 Liability header

Cell A12 Liability account name

Cell A14 Owner’s equity header

Cell A15 Equity account name

Cell A17 Total liability and owner’s equity description

28. Type the appropriate asset amount in cell C6. Continue by entering appropriate amounts in the range C7:C9 as well as in cells C12, C15, and C17.

29. Highlight the range A6:A8 and choose Home→Alignment→Increase Indent twice.

30. Apply a bottom border to cells C8 and C15 and apply a bottom double border to cells C9 and C17.

31. Select cell A17 and choose Home→Alignment→Wrap Text.

32. Set the range C6:C17 in the Accounting number format with no decimal places.

33. Using the right-click method, set the width of column A to 20 and the width of column B to 2.

34. Double-click the border between the columns C–D headers.

35. Select the headers for row 17, choose Home→Cells→Format→Row Height, and set the row height to 30.

36. Set the row height of cells A13 and A16 to 5.

37. Set the range A1:A3 in bold formatting.

38. Save and close your file.

EFA.indb 57 7/13/18 5:50 PM

58 Excel for Accounting Chapter 2: Financial Statements

Apply Your Skills

EA2-A1 Create Financial Statements for Titan Industries

In this exercise, you will create a monthly income statement, statement of owner’s equity, and balance sheet in Excel for Titan Industries. With the exception of the William Buffalo, Capital account (the balance for which is from 11/1/2016), the company had the following account balances as of 11/30/2016.

Accounts Payable $3,500 Repair Expense $2,500

Accounts Receivable $7,000 Service Revenue $13,000

Building $13,000 Supplies $1,000

Cash $19,000 Supplies Expense $700

Equipment $10,000 Utilities Expense $1,800

Office Expense $6,200 William Buffalo, Capital – 11/1/2016 $40,200

Notes Payable $5,000 William Buffalo, Drawing $500

1. Open a Blank Workbook and save the file in your Chapter 02 folder as: EA2-A1-FinancialStatements-[YourName]

2. Set the workbook to contain a total of three tabs, one for each financial statement and arranged in the order they will be completed.

3. In the range A1:A3 on each tab:

• Enter the appropriate three-line header for each, with bold formatting and the Orange, Accent 2, Lighter 80% fill color.

• Merge and center the header rows across columns A–D (Income Statement) or columns A–C (Statement of O.E. and Balance Sheet).

Enter Data for the Income Statement

4. On the Income Statement tab, enter the data as described:

In this cell or range: Enter the data for the:

Cell A5 Revenue account name

Cell D5 Revenue account amount

Cell A7 Expenses header

Range B8:B11 Expense account names (largest to smallest)

Range C8:C11 Expense account amounts

Cell A12 Total expenses description

Cell D12 Total expenses

Cell A13 Net income or loss entry

Cell D13 Net income or loss amount

5. Apply the appropriate border to cells C11, D12, and D13.

EFA.indb 58 7/13/18 5:50 PM

Apply Your Skills 59

6. In cell A15, type a comment that indicates the largest expense and apply a 10% Orientation to the cell; hide row 15 so your comment is not visible.

7. Apply the Accounting number format with zero decimal places to all amounts in the statement and set appropriate column widths to columns A–D.

Enter Data for the Statement of Owner’s Equity

8. On the Statement of O.E. tab, enter the data as described:

In this cell: Enter the data for the:

Cell A5 Beginning equity account name

Cell C5 Beginning equity amount

Cell A6 Net income or loss title

Cell C6 Net income or loss amount

Cell C7 Subtotal of cells C5 and C6

Cell A8 Name of the appropriate account

Cell C8 Amount associated with cell A8

Cell A9 Ending equity account name

Cell C9 Ending equity amount

9. Apply the appropriate border to cells C6, C8, and C9 and wrap the text in cells A5 and A9 to display the account names.

10. Apply the Accounting number format with zero decimal places to all amounts in the statement, set appropriate column widths to columns A–C, and set appropriate row heights to any rows requiring adjustment.

Enter Data for the Balance Sheet

11. On the Balance Sheet tab, enter the data as described:

In this cell or range: Enter the data for the:

Cell A5 Asset header

Range A6:A10 Asset account names in order of liquidity (Cash, Accounts Receivable, Supplies, Equipment, Building)

Range C6:C10 Asset account amounts

Cell A11 Total assets name

Cell C11 Total assets amount

12. Apply the appropriate border to cells C10 and C11.

13. Insert a line break in the appropriate location in the account name in cell A7.

EFA.indb 59 7/13/18 5:50 PM

60 Excel for Accounting Chapter 2: Financial Statements

14. Enter the data as described:

In this cell or range: Enter the data for the:

Cell A13 Liabilities header

Range A14:A15 Liability account names (from short- to long-term)

Range C14:C15 Liability account amounts

Cell A16 Total liability name

Cell C16 Total liability amount

Cell A18 Owner’s equity header

Cell A19 Equity account name

Cell C19 Equity account amount

Cell A21 Total liabilities and owner’s equity name

Cell C21 Total liabilities and owner’s equity amount

Hint: In this instance, the order of liabilities from short- to long-term is also the order from smallest to largest.

15. Wrap the text in cells A19 and A21 to display the account name.

16. Apply the appropriate border to cells C15, C19, and C21.

17. Apply the Accounting number format with zero decimal places to all amounts.

18. Apply appropriate column widths to columns A–C, indent the account names in the range A6:A10 and the range A14:A15, and apply appropriate row heights as needed.

19. Save and close your file.

EA2-A2 Create Financial Statements for Metropolitan Corp.

In this exercise, you will create a monthly income statement, statement of owner’s equity, and balance sheet in Excel for Metropolitan Corp. With the exception of the Terry Mattingly, Capital account (the balance for which is from 5/1/2016), the company had the following account balances as of 5/31/2016.

Accounts Payable $27,000 Sales Revenue $23,000

Accounts Receivable $24,000 Service Revenue $4,000

Cash $57,000 Supplies Expense $6,000

Land $41,000 Tax Expense $13,000

Maintenance Expense $17,000 Telephone Expense $10,000

Notes Payable $9,000 Terry Mattingly, Capital – 5/1/2016 $118,000

Printing Expense $11,000 Terry Mattingly, Drawing $2,000

1. Open a Blank Workbook and save the file in your Chapter 02 folder as: EA2-A2-FinancialStatements-[YourName]

2. Set the workbook to contain a total of three tabs, one for each financial statement and arranged in the order they will be completed.

EFA.indb 60 7/13/18 5:50 PM

Apply Your Skills 61

3. In the range A1:A3 on each tab:

• Enter the appropriate three-line header with bold formatting and the Green, Accent 6, Lighter 60% fill color.

• Merge and center the header rows across columns A–D (Income Statement) or columns A–C (Statement of O.E. and Balance Sheet).

Enter Data Within the Income Statement

4. On the Income Statement tab, enter the data as described:

In this cell or range: Enter the data for the:

Cell A5 Header for revenues

Range B6:B7 Revenue account names (largest to smallest)

Range C6:C7 Revenue account amounts

Cell A8 Total revenue entry

Cell D8 Total revenue amount

Cell A10 Header for expenses

Range B11:B15 Expense account names (largest to smallest)

Range C11:C15 Expense account amounts

Cell A16 Total expenses entry

Cell A17 Net income or loss entry

Range D16:D17 Amounts associated with cells A16 and A17

5. Apply the appropriate border to cells C7, C15, D16, and D17.

6. In cell A19, type a comment that indicates the largest revenue item and apply a 15% Orientation to the cell.

7. Apply the Accounting number format with zero decimal places to all amounts within the statement and apply appropriate column widths to columns A–D.

Enter Data for the Statement of Owner’s Equity

8. On the Statement of O.E. tab, enter the data as described:

In this cell: Enter the data for the:

Cell A5 Beginning equity account name

Cell C5 Beginning equity amount

Cell A6 Net income or loss title

Cell C6 Net income or loss amount

Cell A7 Appropriate account name

Cell C7 Amount associated with cell A7

Cell A8 Ending equity account name

Cell C8 Ending equity amount

9. Apply the appropriate border to cells C7 and C8 and use line breaks to display the account names in cells A5 and A8.

EFA.indb 61 7/13/18 5:50 PM

62 Excel for Accounting Chapter 2: Financial Statements

10. Apply the Accounting number format with zero decimal places to all amounts.

11. Apply appropriate column widths to columns A–C and appropriate row heights to rows 5 and 8.

Enter Data Within the Balance Sheet

12. On the Balance Sheet tab, enter the data as described:

In this cell or range: Enter the data for the:

Cell A5 Asset header

Range A6:A8 Asset account names in order of liquidity (Cash, Accounts Receivable, Land)

Range C6:C8 Asset account amounts

Cell A9 Total assets name

Cell C9 Total assets amount

13. Apply the appropriate border to cells C8 and C9.

14. Wrap the text in cell A7.

15. Enter the data as described:

In this cell or range: Enter the data for the:

Cell A11 Liabilities header

Range A12:A13 Liability account names (from short- to long-term)

Range C12:C13 Liability account amounts

Cell A14 Total liability name

Cell C14 Total liability amount

Cell A16 Owner’s equity header

Cell A17 Equity account name

Cell C17 Equity account amount

Cell A19 Total liabilities and owner’s equity name

Cell C19 Total liabilities and owner’s equity amount

Hint: In this instance, the order of liabilities from short- to long-term is also the order from smallest to largest.

16. Indent the account names twice in the ranges A6:A8 and A22:A13.

17. Apply the appropriate border to cells C13, C17, and C19 and add a line break to the entry in cell A19.

18. Apply the Accounting number format with zero decimal places to all amounts.

19. Apply appropriate column widths to columns A–C and appropriate row heights as needed.

20. Save and close your file.

EFA.indb 62 7/13/18 5:50 PM

Extend Your Skills 63

Extend Your Skills

EA2-E1 Create Financial Statements for Frozen Fractals Corp.

In this exercise, you will create an income statement, statement of owner’s equity, and balance sheet in Excel for Frozen Fractals Corp. With the exception of the Jacqueline Elise, Capital account (the balance for which is from 12/1/2016), the company had the following account balances as of 12/31/2016.

Accounts Payable $6,000 Land $27,000

Accounts Receivable $4,100 Office Expense $4,000

Auto Expense $9,400 Rent Revenue $8,500

Cash $61,000 Sales Revenue $41,000

Furniture $8,000 Supplies Expense $2,500

Jacqueline Elise, Capital – 12/1/2016 $73,200 Telephone Expense $6,100

Jacqueline Elise, Drawing $3,000 Utilities Expense $3,600

When creating the financial statements, be certain to use the techniques from this chapter to present each in good form.

EA2-E2 Create Financial Statements for BeBo Industries

In this exercise, you will create an income statement, statement of owner’s equity, and balance sheet in Excel for BeBo Industries. With the exception of the Lily Ruth, Capital account (the balance for which is from 8/1/2016), the company had the following account balances as of 8/31/2016.

Accounts Payable $3,700 Security Expense $1,200

Accounts Receivable $3,400 Service Revenue $14,100

Cash $27,000 Supplies Expense $900

Equipment $9,100 Telephone Expense $1,500

Lily Ruth, Capital - 8/1/2016 $34,000 Utilities Expense $2,100

Lily Ruth, Drawing $1,400 Wages Expense $3,300

Maintenance Expense $1,900

When creating the financial statements, be certain to use the techniques from this chapter to present each in good form.

EFA.indb 63 7/13/18 5:50 PM

64 Excel for Accounting Chapter 2: Financial Statements

EA2-E3 Create Financial Statements for Weather Watchers Co.

In this exercise, you will create an income statement, statement of owner’s equity, and balance sheet in Excel for Weather Watchers Co. With the exception of the Jacob Wright, Capital account (the balance for which is from 3/1/2016), the company had the following account balances as of 3/31/2016.

Accounts Payable $29,000 Salaries Expense $51,000

Accounts Receivable $9,000 Sales Revenue $82,000

Building $41,000 Service Revenue $11,000

Cash $17,000 Truck Expense $8,000

Insurance Expense $14,000 Utilities Expense $13,000

Jacob Wright, Capital - 3/1/2016 $58,000 Wages Expense $22,000

Jacob Wright, Drawing $5,000

When creating the financial statements, be certain to use the techniques from this chapter to present each in good form.

EFA.indb 64 7/13/18 5:50 PM

Critical Thinking 65

Critical Thinking

EA2-C1 Discuss Using Hide/Unhide

Many individuals are likely to review a company’s financial statements. Among these stake- holders are executive managers, potential investors, loan officers, and prospective employees. When disseminating financial statements, it is not uncommon to include commentary or discussion points related to the figures. These comments are often intended for a limited audience. The Hide and Unhide commands can be used to ensure that comments are displayed within a worksheet only when desired.

Write a paragraph of at least five sentences in which you identify four stakeholders who are likely to review financial statements (use those referenced or others of your choosing) and why. Write a second paragraph of at least five sentences to discuss the type of commentary that would be appropriate to include for each stakeholder.

EA2-C2 Discuss Managing Workbooks in an Accounting Environment

Multiple worksheet tabs can be used to organize a company’s financial statements for an accounting period. This technique can be used in a variety of other ways as well. Giving thought to these other uses can yield significant benefits down the road in terms of enhancing efficiency and ensuring quality analyses.

Write a paragraph of at least six sentences in which you identify four accounting-related tasks (other than completing financial statements) for which the use of multiple worksheets would be appropriate. Discuss how multiple worksheets within a workbook can be used for each task.

EFA.indb 65 7/13/18 5:50 PM