Project 3 - Draft

HJN Company Handbook Microsoft Excel 2013

Table of Contents Introduction (will include page numbers once completed) Function Library Addition Subtraction Multiplication Division Straight-Line Depression (SLN) Interest Payment (PMT) Shortcuts Glossary (glossary will be included in second draft)

Welcome to the HJN Accounting Company! Here at HJN we strive to achieve customer satisfaction through outstanding work practices. We are a bookkeeping and payroll accounting company whom handle financial tasks such as assisting business owners stay financially organized, tax complaint, and allow them to prepare for further business growth. As a new employee you will often use Microsoft Excel to complete everyday functions. At our company we use the 2013 edition of this program, which may be found within your desktop applications. Do NOT use the online version of this program to complete your work, as it does not provide the same formatting. This handbook will provide as a guideline to complete tasks needing Microsoft Excel. Uses for Microsoft Excel 2013 Microsoft Excel will commonly be used Simple functions such as addition, division, multiplication and division will be used on an everyday basis for most employees. Those who are associated with asset management, cost of goods sold will be using Straight-Line Depression. While those in accounts receivable and accounts payable and watch cash flow will be using Interest Payments. Microsoft Excel is an electronic spreadsheet program that is used for storing, organizing, and manipulating data. This program presents tables which arrange cells in rows and columns that may be manipulated mathematically, using both basic and complex arithmetic operations and functions. *Insert screenshot showing cell, row, & column*

The Function Library The function library is where all functions that are pre-programmed in excel are located and stored. The function library can be found under the formulas tab, which is located in the main ribbon of Excel. Types of Functions In the function library there are multiple categories for functions. Listed below are the two categories our company uses the most. 1. Auto Sum – contains functions Sum, Average, count numbers, max, and min 2. Financial – contains numerous functions related to the financial field.

There are other categories located in the function library, however, this manual will focus on the categories listed above; interest payments (PMT) and straight-line depreciation (SLN) Basic Functions How to start basic functions With basic functions, typing an “ = “ into the desired cell will start the function process and tell excel that there is an equation to be entered. Addition 1. Start with = 2. Type in Identifying cell letter and numbers to be added ▪ Ex. Add cells A1 and B1, which both contain the number 1 and show the results in cell C1 ▪ Formula: =A1+B1 • This formula is entered in cell C1

▪ Cell C1 should show the number 2 • The AutoSum function can also be used in this scenario Subtraction 1. Start with = 2. Type in Identifying cell letter and numbers to be subtracted ▪ Ex. Subtract cells A1 and B1, which both contain the number 1 and show the results in cell C1 3. Formula: =A1-B1 • This formula is entered in cell C1 ▪ Cell C1 should show the number 0 Multiplication 1. Start with = 2. The star “ * “ symbol is used for multiplication 3. Type in Identifying cell letter and numbers to be multiplied ▪ Ex. Multiply cells A1 and B1, which both contain the number 2 and show the results in cell C1 4. Formula: =A1*B1 • This formula is entered in cell C1 ▪ Cell C1 should show the number 4

Division 1. Start with = 2. The backslash (/) is used for division 3. Type in identifying cell letter and number to be Divided ▪ Ex. Divide cells A1 and B1. Cell A1 contains the number 4 and cell B1 contains the number 2. Show the results in cell C1 4. Formula: =A1/B1 • This formula is entered in cell C1 ▪ Cell C1 should show the number 2

Straight-Line Depreciation Accountants assigned to asset management will be involved in the monthly, quarterly, and yearly adjustments to the books. One of the major steps in this process will be the depreciation of assets such as buildings and equipment. Our company uses Straight-Line Depreciation, a method which divides the cost of the asset evenly over its useful life. In this simple form of depreciation, depreciation values will be equal across every year until the asset reaches a value of zero. In our firm, depreciation of assets is calculated on a monthly basis for our own use, and these numbers are totaled up yearly for tax purposes. Depreciation allows the company to increase expenses, thus reducing our tax burden for the year. Using the Straight-Line Depreciation (SLN) Function 1. Select the cell where you wish to display the depreciation expense 2. Select the formulas tab at the top of the page 3. In the function library, select the “Financial” drop down arrow 4. Click on the SLN function in the drop-down list 5. In the “Cost” box, enter the amount paid for the asset 6. In the “Salvage” box, enter the salvage value of the asset * 7. In the “Life” box, enter the remaining useful life of the asset (in years). If calculating monthly depreciation, multiply the useful life by 12. 8. The depreciation expense should be calculated by excel and entered into the appropriate cell. * Salvage Value is a pre-determined dollar amount that the company expects to receive at the end of the useful life of an asset. Estimated gain at disposal.

SLN Shortcut For more advanced users of Excel, there is a shortcut to completing the SLN calculation. 1. Select the cell where you wish to display the depreciation expense 2. Enter an equal sign (=) followed by the letters, “SLN” 3. Select the SLN pop-up box that appears 4. Following the “SLN” enter the cost, salvage, and useful life in that order, separating each by a comma. 5. Click enter and the calculation should appear. Ex: An asset which costs 10,000 was purchased by HJN and estimated to have a useful life of 10 years. HJN expects the salvage cost to be zero at disposal. Calculate the year value of depreciation. Enter the following in cell A5: =SLN(10000,0,10)

Interest Payments Accountants working in accounts receivable and accounts payable will be responsible for calculating the amount of money flowing in and out of the firm. Included in these calculations is the amount of interest our firm owes for loans we are in the process of paying off. Conversely, since our firm issues small loans, accounts receivable will be responsible for calculating the interest we should receive from clients for money we have lent out. Interest will be payable in six to twelve-month increments, depending on the terms of the loan. Additionally, interest expenses and revenues must be calculated monthly to properly follow the standards of revenue recognition set forth by U.S GAAP. Using the Interest Payment (IPMT) Function: 1. Select the cell where you wish to display the depreciation expense 2. Select the formulas tab at the top of the page 3. In the function library, select the “Financial” drop down arrow 4. Click on the IPMT function in the drop-down list 5. In the “Rate” box, enter the interest rate of the loan as a percentage divided by 12 a. Ex: 10%/12 (months) 6. In the “Per” box, enter the number “1” 7. In the “Nper” box, enter the number of months left in the loan 8. In the “Pv” box, enter the amount of money received through the loan as a negative number 9. Leave the “Fv” box blank

10. To calculate the semi-annual interest payment, multiply the monthly payment by 6. For the annual interest payment, multiply the monthly payment by 12

Action Shortcut Go to Formula Tab Alt + M Expand or collapse the formula bar Ctrl + Shift + U Cancel an entry in the cell or formula bar Esc Complete an entry in the formula bar and select Enter the cell below Select all text in the formula bar from the cursor Ctrl + Shift + End position to the end. Display the menu or message for an error Alt + Shift + F10 checking button Use flash fill to automatically recognize patterns Ctrl + E in adjacent columns and fill the current column Insert a function Shift + F3 Copy the value from the cell above the active cell Ctrl + Shift + Straight quotation mark (“) into the cell or the formula bar