Excel Formulas for Finance Statements by fvw14912

VIEWS: 0 PAGES: 64

Excel Formulas for Finance Statements document sample

More Info
									          Excel 2007
Formulas and Database Functions
Topics
 What’s New in 2007
 Formatting Spreadsheets
 Using Multiple Worksheets
 Formulas
 Functions: PMT, FV, Goal Seek & If/Then Statements
 Database Functions
Excel 2003 Vs 2007

     What’s New?
The Quick Access Toolbar, MS Office Button,
Mini Toolbar & Ribbon

 The MS Office Button, Quick Access Toolbar
 and the Ribbon replace the menu, Standard and
 Formatting Toolbars in Excel 2003.
Quick Access Toolbar
Located next to the MS Office
Button, the Quick Access
Toolbar offers one-click access
to the most widely used office
functions.
By default, there are 3 buttons
Save, New, and Open.
Click on the arrow next to the
toolbar, to open the customize
menu
Click the checkbox next to
each feature to add and more
options to the toolbar
This is a New Feature
The MS Office Button replaces the File menu
The MS Office Button
                       The MS Office Button is a
                       New Feature of Excel 2007.
                       This button is the access
                       point to
                          Create New Excel
                          Workbooks
                          Open
                          Save
                          Print
                          Close
                       This Button replaces the File
                       menu
The MS Office Button
                       The MS Office Button also
                       houses
                          Recently opened
                          presentations
                          Convert converts Excel files
                          into the 2007 Format
                          Prepare to finalize
                          presentations for
                          distribution
                          Send which distributes
                          presentations through
                          facsimile or email
                          Publish to distribute a
                          presentation to a server or
                          shared workspace
                          Excel Options (previously
                          located under the Tools
                          menu)
The Mini Toolbar
                   The Mini Toolbar is a
                   menu of frequently used
                   formatting tools that
                   appears when text is
                   highlighted in a Excel
                   Workbook. New Feature
 Menus and Toolbars - 2003
menu Toolbar




Formula Bar    Standard Toolbar    Formatting Toolbar

   In Excel 2003, different functions within Excel are
   accessed through the Menu Bar, Standard Toolbar,
   the Formatting Toolbar, and the Formula Bar
 The Ribbon


Tabs: 8 tabs representing                 Groups: Sections containing
common related activities                 Related items or tasks
                                         Formula Bar: Shows Excel
                                         Formulas
Commands: Buttons, boxes or
menus relating to specific functions within Excel

   Office 2007 is arranged differently. All menus are
   located within tabs on a menu bar called the ribbon
Key Tips




Key Tips are shortcuts to tab and menu options on the
Ribbon.
Push the alt key to show the Key Tips, then push the
corresponding letter on the keyboard to activate the Key
Tip. New Feature
The Formula tab


The Formula Library: This is a quick reference to all of the
different mathematical functions and operations that can be
calculated using Excel.
The Defined Names group: This feature, used in conjunction
with the database and filtering features of Excel, creates cell
names for use in database filtering.
The Formula Auditing group: This section features options to
evaluate and edit complex formulas so that users may locate
errors within formulas.
The Calculation group: This function determines when formulas
are calculated, either automatically (by default) or manually.
The Data tab (Excel)


The Get External Data group: This option replaces the Import
External Data function.
The Connections group: Updates and refreshes data imported from
external sources.
The Sort and Filter group: Replace the Sort and Filter options on the
Tools menu.
The Data Tools group: These tools assist to revise and develop
databases.
The Outline group: Displays by consolidating similar or grouped
items. Three options: group, Ungroup, & Subtotal.
Worksheet Terms

Cell: The intersection of a row and a column.
Cell Address: The combination of letters and
number that identifies a cell (A1, B3 etc).
Cell Notation: Method of writing cell formulas,
cell addresses, not cell contents are used when
writing mathematical formulas for cells.
Formatting a Spreadsheet
 Getting Cells Ready for Data
Formatting Cells
                   Click on “Sheet2”
                   Select cells A1-J7
                   On the Home tab,
                   locate the Font
                   group
                   Click the arrow to
                   open the Formatting
                   menu
Formatting Cell Font Style
                         Click on Font tab.
                         Change Font, Font Style,
                         & Size to Arial 12 Bold.
                         Use this feature to set
                         the font for an entire
                         worksheet, or a few cells
Formatting Cell Numeric Functions
                        Click on the Number tab
                        Choose Currency
                        Choose the option to
                        show Negative numbers
                        in red
Format Rows and Columns
                    Locate Format on the
                    Home tab
                    Click Row Height, and
                    change it to 50 on the
                    on the menu, and
                    click OK
                    As with rows, choose
                    Format Column
                    Width to adjust
                    column width
AutoFit
          AutoFit will make columns
          wide and tall enough for
          text to fit, no matter how
          much text is in the cell.
          Move the mouse to the
          line in between two
          columns (A and B)
          When the mouse becomes
          this symbol, ( ), double-
          click
View and Format Multiple Workbooks
                       Click the Workbook tab
                       Chicago
                       On the View tab, locate
                       and click New Window 3
                       times
                       Click on the tabs for the
                       worksheets National
                       Sales, Atlanta & Boston
                       4 worksheets are open
View and Format Multiple Workbooks
                       Click Arrange All
                       Choose Tiled
                       Select the Windows of
                       active workbook check
                       box
                       Click OK
                       This process will also
                       work with multiple
                       workbooks
GETTING RID OF ####
                      Sometimes, after
                      formatting a worksheet
                      numeric data will
                      appear as ####
                      Place the cursor
                      between the cells
                      When the cursor
                      changes to a double-
                      arrow line, ( ) click
  Formulas

Math-Excel Style
Formulas
           These are the basic rules
           of formulas.
           Formula are written in
           this manner:
           =cell address + cell
           address
           Note that you add the
           cells, not the cell content.
           Note that spaces are not
           used in formulas!
Formulas and Cell Notation




The Name Box displays the cell name of a selected cell
The Formula Bar shows the formula being entered a cell
Formulas can be entered in the formula bar or in a cell
Formulas can be copied to the formula bar
Formulas cam be edited in the formula bar
Formulas
Open the worksheet Formulas

Adding Multiple Cells   Sum Function
 Select Cell B8           Select Cell C8
 Type =B1+B2+B3+B4        Type =SUM(C1:C6)
 in the formula Bar       Press ENTER
 Press ENTER
Formulas
The Point and Click method of adding cells
  Select Cell E8
  Type an =
  Click Cell E1
  Type +
  Click Cell F2
  Type +
  Click Cell E3
  Type +
  Press ENTER
 Formulas

Auto Average
 Select Cells G1–G6
 Click Selector next to the Auto Sum Icon Σ on
 the Drop Down menu
 Choose Average
 Formulas
Auto Sum
 Open and Tile the Boston, Atlanta, Chicago and
 National Sales Worksheets
 Click on the tab for New York
 Select cells B4-B7
 Click the selector next to the Auto Sum Icon Σ on
 the Drop Down menu
 Choose SUM
Formulas for Multiple Worksheets
Open the National Totals Worksheet, and click Cell B7
Type =SUM(
Hold down the CTRL Key and Click in Cell B7 of the
Chicago Worksheet
Click B7 Again
Type a + sigh
Repeat the process for cells B7 in Atlanta, and Boston
Type a )
Look at the total in cell B7 of National Sales
Formulas for Multiple Worksheets
Hold down the Ctrl +` keys at the same time to
display a formula
Look at the Formula =SUM(Chicago!B7+‘Atlanta
s'!B7+Boston'!B7)
The ! Means that the formula spans across
worksheets.
Formulas may also span across workbooks using the
same method, However all of the workbooks must be
open
Copy Formulas

Click cell B7 in the National Worksheet
Move the mouse to the Bottom Right corner, so
that it becomes a black + sign
Click and drag the mouse from B7 to G7
The formula copies in each cell
Trends
                     Open “Trends”
                     Select Cells A1 & A2
         +           Hold the mouse over
                     the bottom right corner
                 +   until the pointer
                     becomes a thin + sign
                     Drag the mouse down to
             +       cell A10
                     Repeat the process in
                     column B, Highlighting
                     Cells B1-B5
Trends
         Select Cells A12 & B12
         Repeat the previous
         process, but drag across
         to column G
 Date Functions
Open the worksheet Dates
 In cell B1, Type +TODAY() This calculates today's date
 In cell B3, Type your date of birth
 In cell B5, Type =INT((TODAY()-B3)/365) This will give you
 your age (Don’t Lie)
 In cell B7 Type =NOW() This calculates the current date and
 time
 Current date and time can be calculated on the keyboard:
    Date: CTRL+;
    Time: CTRL+SHIFT+;
    Both: CTRL+; SPACE, then CTRL+SHIFT+;
Absolute and Relative Cell References
When copying and pasting cell formulas, some cell
values may vary each time a formula is copied, while
other cell values remain constant during the same
copy process.
Relative reference refers to cell values that vary as a
formula is copied from cell to cell.
Absolute Reference refers to cell values that remain
constant as a formula is copied from cell to cell An
absolute reference is noted by a dollar sign in front of
the Row and Column designation of the cell address:
$B$5.
Absolute Reference
Open the worksheet Absolute
Hold down the Ctrl +` keys to display the formulas
 When calculating cell values using absolute
references, it is best to use the pointing method.
We are going to calculate the future value of cell C5.
C5 is the value of cell B5 when a cost increase of 10%
is added to the cost of goods sold
10% is the value of cell D24 and is an Absolute
Reference
Absolute Reference in a Formula
Click in Cell C5.
Type =(
Click cell B5
Type a + sign
Click in cell B5 again,
Type *
Click D24
Push the F4 key to set the absolute reference
Type ) and Enter
The answer is $8250.00
Financial Functions
PMT Calculates periodic payments,
   i.e. How much will it cost to pay off a car if I
   finance $$ at 48 months with $$ down?
FV calculates future value,
   i.e. if I save $$ per month over 40 years, how
   much money will I have when I retire?
Goal Seek- A command that lets you enter the end
result ($$ Payment) and from that determine how
much you can spend to get that payment
Payment (PMT)
The PMT function requires arguments supplied as cell
references
   Interest rate per period
   Term of loan
   Amount of loan (as a negative value)
Payment is equal annual interest rate/12,term (#
months or # years x 12),-amount of loan as a negative
value.
Expressed in CELL NOTATION
PMT
      Open the Worksheet Car
      Calculate the amount
      financed in cells E4 & F5
      Click in cell H4
      Choose the Formula tab.
      Locate Financial in the
      Function Library
      Choose PMT from the list
PMT
      In Rate, click cell G4 and type /12
      (Interest rate /12 months)
      In Nper, click cell H4 (the
      calculation is for 36 month term, if
      the term is 3 years, reflect the
      term by multiplying the cell value
      by 12. (G4 * 12)
      In Pv, click in cell H4 (the value
      calculated as the price less trade
      and down payment)
      Type a – sign before F4
      Click OK
Future Value (FV) Function

Future value of an investment based on
  constant periodic payments
  Constant interest rate
There are still 3 Arguments
  Amount at retirement = Future Value, V (rate
  or return) , -Periodic Payment
  Expressed in CELL NOTATION
FV
Open the Worksheet FV
Click cell E8.
Type =( and click in B2
Express B2 as an Absolute Reference and
subtract it from B8, then type )
This will calculate the number of years you will
contribute to your retirement
FV
     Click in cell F8
     Choose the Formula tab
     Locate Financial in the
     Function Library
     Choose FV from the list
FV
     In Rate, click cell D8
     In Nper, click cell E8
     In PMT, click in cell C8
     Type a – sign before C8
     Click OK
     Type your age in B6 and
     $3,000 in cell C6 the
     value in F6 is $90,972.85
Goal Seek

Goal seek will allows you to set the end result in
order to determine the input
Using the care financing example, I want to
know how much I need to receive for my trade-
in (D5) to finance $18,000 (F5) (assuming that
my down payment Is the same).
Goal Seek
            In the Car worksheet,
            click cell F5
            On the Data tab, locate
            What-If Analysis
            Choose Goal Seek.
Goal Seek
                    Click In To Value, type
                    18,000
            18000
                    Click in By changing cell,
                    and click in cell D5
                    Click OK
                    The Goal Seek Status
                    dialogue opens
                    Look in cell D5 for the
                    new value $4,500
                    Click OK
Database Functions


     Sorts, Filters, and Lists
Sort Using Single Field
                          Open Addressees
                          A single field sort will
                          list using a single field.
                          Select Column B, by
                          clicking on it.
                          On the Home tab, locate
                          the editing group and
                          click Sort and Filter
                          Click Sort A to Z
Sort Using Single Field
                          Click Expand the
                          selection
                          Click Sort
Sort Using Multiple Fields
In this sort, the request is to rank the the GPA’s of
male and female students, by gender.
In Addressees, select cell A1
The sort can be accessed from the Home tab by
locating the editing group and clicking Custom Sort
Sort may also be accessed by locating the Data tab and
clicking Sort in the Sort and Filter group
Sort Using Multiple Fields




 Under Column, choose GPA
 Under Sort on use Values
 Under Order select Smallest to Largest
Sort Using Multiple Fields




 Click Add Level
 Under Column, choose GPA
 Under Sort on use Values
 Under Order select A to Z
 Click OK
Sort by Auto Filter
                      Sorting by Auto Filter
                      isolates data on the
                      Spreadsheet.
                      In Addresses, click cell A1,
                      then click Filter
                      Click on the toggle menu
                      for the First Name
                      category
                      Click Select All
                      Uncheck the box for Carl
Sort by Auto Filter
                      Carl’s name is isolated
                      Notice the toggle is
                      shaped like the filter icon
                      Click on the toggle again,
                      and click Clear Filter
                      Click OK, and the full
                      worksheet returns
Sorting with more than one filter
Data can also be filtered from more than one field
Data can be filtered to generate lists of more than, less
than, exact match or top ten
For example a list of students with a GPA of 3.2 or
higher
Sorting with more than one filter
                         Click the toggle in the
                         GPA field
                         Click Number filters
                         Choose greater than or
                         equal to
                         A new menu opens
                         Type 3.0 in the is
                         greater than field
                         Click OK
Database Add-Ins
                   Excel offers additional
                   tools for data analysis,
                   currency and statistical
                   analysis. These are
                   easily loaded into Excel
                   Click the Office Button
                   Click Excel Options
                   Click Add - ins
                   Click Analysis Tool Pack
                   Click OK
                   The Add – in will load
 Questions?
Terence Peak, M.Ed.
Coordinator of Technology Training
Blackboard Certified Trainer
The University of the Incarnate Word
(210) 829-3920
tpeak@uiwtx.edu

								
To top