Excel by wuxiangyu


									          Group Assignment
        “Making Sense of Data”
     (60% of overall module mark)
• Financial
• Based on: A mobile phone shop.
• Business projections from now and 3 further
• Spreadsheet enables owner to test the effect
  of possible growth or decline in business.
• Demonstrate the “what-if” functionality.
                 BEM1005 Assignment Briefing     1
            Marking scheme
• 11 key factors.
• Each worth 5 to 20 marks: Total 100
• Examples:
  Layout = 10 marks
• Correct use of formula = 16 marks
• Correct numeric results = 20 marks
• Correct application of IF function = 12 marks

                  BEM1005 Assignment Briefing     2
      How will marks be awarded?
• Structural integrity : Consistency of
  presentation : X answer sheets all matching in
  layout, font, numeric formatting.
• Formula in main table must always be based
  on data held in an assumptions table.
• In main tables, all values to be based on data
  in adjoining tables and variables in
  assumptions tables. Example: % growth rate
  used, must be part of an assumptions table.
                  BEM1005 Assignment Briefing      3
                       Excel 2007
• Cell addresses in formula (=G8 * G9)
• 2 types : (1)Relative and (2)Absolute
• Excel records the relationship between other cells by
  recording how many cells to the right or left and up or
  down. This action is invisible to the user.
• Why is this important to know?
• Because when “copying” one cell which contains a formula,
  to many cells, the copy cannot carry the relationship
  correctly and errors will be created in the spreadsheet..
• If the source is single cell and the target is multiple cell, you
  must get a “fix” on the source by making part of the
  formula “absolute”

                         BEM1005 Assignment Briefing              4
                More Excel
• Creating spreadsheets : Speed up process :
  use tools effectively.
• Copy formula ; beware the effect of copying a
  single formula to multiple cells.
• Use Absolute references. (Use F4 key)
• Toggle :

                 BEM1005 Assignment Briefing      5
Using Formulas : Relative addressing

           BEM1005 Assignment Briefing   6
Using Formulas : Relative addressing

                                           If we copy E2 down
                                           to E3 to E7 we will
                                           get a result, but it will
                                           be incorrect.

             BEM1005 Assignment Briefing                               7
Using Formulas : Absolute addressing
                                           •   If an item in a
                                               formula is in one
                                               cell and is to be
                                               used in many
                                               cells, it must be
                                           •   Select the part of
                                               the formula in the
                                               formula bar and
                                               press Function
                                               key F4 to find the
                                               most appropriate
                                               column and row

             BEM1005 Assignment Briefing                            8
Absolute Address in E2
    Can be copied

      BEM1005 Assignment Briefing   9
Inserting Functions

    BEM1005 Assignment Briefing   10
Using the FunctionWizard

       BEM1005 Assignment Briefing   11

To top