Finance Spreadsheets by eyv16957

VIEWS: 14 PAGES: 57

More Info
									Spreadsheets in Finance
       and Forecasting
        Lecture 2:
        Introduction to the
        Project
Session Objectives
The purpose of this session is to:
 Ensure that you understand what is
  required;
 Discuss the project timescale, and
  indicate some of the milestones;
 Offer some examples of what might be
  the end-product;
 Explore some of the methods by which
  you might investigate the context.
The Project Brief
   Within a realistic context of application,
    identify a range of sources and targets of
    data both external and internal to the
    context, which are generated or supplied
    as part of the normal business working
   Formulate a range of spreadsheet models
    and applications which can be used to
    work with the data.
   Evaluate the usefulness of these models
    and applications.
Breakdown of the task
The task has five parts:
1. Finding and
   investigating a
   context
2. Discussing Initial
   Ideas
3. Development Work
4. Presentation of
   proposals
5. Creation and
   Submission of a
   report
The Timescale
Weeks 2-5:
 Find context, collect data, create
  draft systems analysis
Weeks 5-7:
 Attend Formative Tutorial and
  discuss context
Weeks 8-9:
 Create project outline proposals
Week 10:
 Submit draft proposals as a
  presentation
Weeks 11,12:
 Develop spreadsheet solutions
Week after Easter Break
 Submit full project proposals
The Formative Tutorial
   In Weeks 5, 6 or 7         Attendance at the
    you will attend an          tutorial is
    individual formative
                                compulsory and is
    tutorial.
                                worth 5% of the
   Before this you will
    have submitted an A4        marks
    sheet with details of
    your context and
    what you have been
    doing
The Formative Tutorial
   The purpose of the
    tutorial is to ensure
    that you are going
    along the right lines,
   and that:
       you have found a
        suitable context
       your data is suitable
       your analysis is
        effective
       you have good ideas
The Presentation
   By the end of week            The Presentation
    10, you will submit a
    Powerpoint                     should be designed
    presentation.                  to last about 10
   In this presentation           minutes, use
    you will describe the          PowerPoint and is
    context and outline
    your proposals.
                                   worth 10%
   You will only produce
    the slides, but will not
    be required to give an
    oral presentation.
The Presentation
                The purpose of the
                 presentation is to cause
                 you to summarise your
                 ideas thus far.
                It is likely that you will
                 receive suggestions as
                 to how you might
                 improve, or what else
                 you could do.
Development Work
            This is the hard graft of the
             project
            You will need to devise
             spreadsheet models, and
             produce story boards and
             mock-ups.
            You are not required to
             produce fully working
             spreadsheets, but you do
             need to have realistic and
             fully developed ideas, which
             are capable of being
             subjected to critical
             analysis.
Submission of the Report
                About 1000 words
                Fully structured
                To include full
                 descriptions of the
                 proposed spreadsheet
                 solutions
                Analysis and
                 Justification
                Sources, Bibliography
                 etc.
In this session
 We now consider the project brief,
  and some of the issues which you
  should be addressing over the next
  few weeks.
 There will be further lectures on the
  content of each of the other aspects.
Finding and Investigating
               a Context
What do we mean by a
“context of application” ?
           Any organisation or
           situation which
           deals with money or
           information.
The Context
   The context would
    normally be a small
    business enterprise,
    or a similar set-up
    which involves data
    and/or financial
    matters.
   One of the criteria
    here is that you
    should be thoroughly
    familiar with the
    context.
Some Ideas (1)
A small retail outlet:
 a newsagents
 a greengrocer
 a charity shop
or possibly:
 a single small
  outlet of a large
  retail chain
Some Ideas (2)
             A small organisation
               offering a service:
              a doctor’s or dentist’s
               surgery
              a travel or estate
               agent
              a branch of a building
               society or bank
              a hairdresser
              A Citizen’s Advice
               Bureau
Some ideas (3)
Trades people:
 a window cleaning
  firm
 a small builder
 a decorating firm
 an electrical
  contractor
Some ideas (4)
             Other organisations:
              A Club
              A school
              A Charity
              A local action
               group
Can I invent a context?
                 Yes, but only do
                  this as a last
                  resort.
                 You are unlikely to
                  be able to invent
                  the depth and
                  complexity of data
                  relationships
                  without a real
                  focus.
Your Own Ideas
   Spend 2 minutes
    now, jotting down
    your ideas of some
    of the contexts
    you might use.
   Then spend 5
    minutes discussing
    this with another
    person.
Sharing Ideas
                   We will spend a
                    few minutes
                    “thinking aloud”,
                    where each person
                    will describe a
                    context they think
                    they may be able
                    to use.
Criteria for selecting the
context:
   You will be assessed on the quality of your
    product and on the relevance and “fit” of the
    product to the context.
   This means that you must have a thorough
    insight into the context, for example if you have
    worked there, or you have a friend or relative
    who has worked there.
   In addition, the context must provide a variety
    of different types of problem, and you need to
    have insights into the way things are currently
    done.
What will I do in the
            context?
     The project models
     a scenario in which
     you are a consultant
     brought in to
     suggest
     improvements
The Scenario
   You are an           Ideally this should
    employee of           improve service to
    CSTSB: Computer        customers
    Solutions for         improve the flow
    Today’s Small          of data
    Business              improve the
   Your brief is to       presentation of
    provide the            data
    manager of your       improve ways of
    context with           using the data
    computer solutions
    to their problems     improve ways of
                           managing data
Be Selective
   The scenario is
    deliberately open.
   This means you
    are able to choose
    any aspect which
    has potential for
    improvement .
Using Spreadsheets
   It is crucial that you develop spreadsheet
    solutions as the major element in your
    project.
   This means carefully selecting those
    elements from the context where
    spreadsheet solutions are relevant and
    applicable.
   You do not have to cover everything!
 What should I
  investigate?
You should observe,
interview and
interact with the
people, systems and
documents
Observing
 Watch what
  happens
 See what people
  do
 See how people
  react
Participating
   It is not strictly
    necessary that you
    involve yourself in
    the day-to-day
    organisation
   However, this may
    help to give you
    insights, and to
    find out how
    systems work
Interviewing
                  Find out about
                   systems, policies ,
                   procedures
                  Find out what is
                   important
                  Find out what
                   people would like
                  Use multiple
                   perspectives
Looking at Documents
               It will be very helpful
                to obtain access to
                current files, in both
                paper-based and
                electronic forms.
               If you are not allowed
                to do this, perhaps
                you could get them
                described to you.
Resources
   In Week 2 Resources
    you will find a
    document called
    Investigating Your
    Context.
   This document offers
    detailed suggestions
    as to how to carry
    out investigations.
What sort of data is
          required?
    Any kind of data
    (financial or other)
    which is collected,
    used, transmitted or
    stored by the
    organisation.
The Data
   The data you use does not have to be
    real, but it does have to be “realistic”.
   One consequence of this is that you
    should know how the data is produced,
    who it is for, what happens to it, and how
    it is used within the system.
   The formats of the data that you use for
    your project should have all the features
    of the real thing.
Data Protection Act
Considerations
   You should take
    care in your
    project not to uses
    real names, nor
    actual data which
    is traceable to a
    particular source.
The Flow of Data
   It is important that you model the whole flow of data,
    and include processes as well as produce products.
   You need to consider all the aspects of the Input –
    Process- Output chain
   Also, consider whether there is any feedback from
    the output back to input which helps to modify the
    process
Data Sources
                  Who or what
                   generates the
                   data?
                  How is it
                   generated?
                  What form does it
                   take?
                  How often?
Processes
   What happens to the
    data along the way?
   Is it summarised,
    averaged, filtered or
    consolidated?
   Is it used in
    scenarios, “what if”
    situations or other
    forecasting tools?
Data Destinations
               Who is the data
                for?
               What form does
                the final data
                take?
               What use will it
                be put to?
The Feedback Loop
   Does the person or
    organisation
    receiving the data
    inform the person
    producing it or
    modifying it
    whether the data
    is of sufficient
    quality or value?
Data Flow Diagrams
 As part of the project, you will need
  to construct diagrams which
  describe the current data flow, and
  your proposed amendments to it.
 These diagrams should give an
  overview of the sources and
  destinations of data.
Data Flow Diagrams
    Sales
Representative   Record Sale     Data Flow
                                 Diagrams will
                                 be crucial in
                                 helping you
                 Invoice Clerk   understand
You will need                    and explain the
to use these                     systems
diagrams to
explain both     Bill Customer       Customer
the current
systems and
your proposals
What sort of things am I
    looking to do in the
                 project?
        These might cover a
        whole range of
        activities but which
        offer potential for
        using a spreadsheet.
A Systems Approach
   The approach adopted
    in this module
    attempts to model
    how a project would
    be tackled in a real
    context.
   This normally occurs
    as a cycle of
       analysis-proposal-
        development-testing-
        evaluation..
Project Development
               Problem
             Specification

Product                      Problem
Evaluation                   Analysis


Product                      Design
Testing                      Proposal

             Product
             Development
Project                      Remember, you do
                             not have to actually

Development
                             create the
                             spreadsheets, only
                             think through the
                             process to the design
                             proposal stage.
               Problem
             Specification

Product                      Problem
Evaluation                   Analysis


Product                      Design
Testing                      Proposal

             Product
             Development
Ideas and Examples
Annual Budget for a Small
Business
This might consist of:
 Opening Balance
  Sheet
 Profit & Loss Account
 Cash Flow Statement
 Closing Balance
  Sheet
Expenses Claims and
Records
             Travel Claims System to
               include:
              Journey details
              Tables to summarise
               data
              Claims tracking
              Summaries, e.g.
               claims per employee,
               totals, averages
              Charts and
               comparisons
Making Predictions
              Looking at Sales Figures
                and/or profitability of
                investments
               Measures of
                profitability
               Forecasting using a
                range of statistical
                and graphical
                techniques
Keeping Accounts
An accounting
  system for a
  charity:
 Subscriptions
 Expenses
 Purchases
 Cash Ledger
 Bank Account
 Transactions
Providing a Quotation or an
Estimate
              Production of a
                quotation using
                standard forms:
               Job details sheet
               Calculation of
                costings
               Printed quotation
Automating Tasks
             A range of tasks,
               including:
              Standard Invoices and
               Receipts at billing and
               payment
              Month-end summaries
              Customer information
               updates
              Automated reports
Other Suggestions
   Think about your
    contexts that you
    described earlier in
    the session.
   Which of the previous
    suggestions are
    relevant to your
    context?
   What other
    suggestions can you
    offer?
The Scope of the Project
 As you can see, some of these are
  major undertakings, taking many
  hours and you will probably only be
  able to develop one or possibly two
  of these
 Some are “one off” sheets which
  could take you a few minutes to do.
What should you do now?
   Spend some time now
    thinking about what
    you might do.
Note down:
 A brief description of a possible context
 A short list of potential data sources there:
   o Financial data
   o Other data


You should discuss this with the tutor before
the end of the session.

								
To top