Spreadsheets in Finance and Forecasting

Lecture 2:Introduction to the Project

Session ObjectivesThe 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 taskThe task has five parts:1. Finding and

investigating a context

2. Discussing Initial Ideas

3. Development Work4. Presentation of

proposals5. Creation and

Submission of a report

The TimescaleWeeks 2-5: Find context, collect data, create draft

systems analysisWeeks 5-7: Attend Formative Tutorial and discuss contextWeeks 8-9: Create project outline proposalsWeek 10: Submit draft proposals as a presentationWeeks 11,12: Develop spreadsheet solutionsWeek after Easter Break Submit full project proposals

The Formative Tutorial In Weeks 5, 6 or 7 you

will attend an individual formative tutorial.

Before this you will have submitted an A4 sheet with details of your context and what you have been doing

Attendance at the tutorial is compulsory and is worth 5% of the marks

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

10, you will submit a Powerpoint presentation.

In this presentation you will describe the context and outline your proposals.

You will only produce the slides, but will not be required to give an oral presentation.

The Presentation should be designed to last about 10 minutes, use PowerPoint and is worth 10%

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 shopor 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 ScenarioIdeally this should improve service to

customers improve the flow of

data improve the

presentation of data

improve ways of using the data

improve ways of managing data

You are an employee of CSTSB: Computer Solutions for Today’s Small Business

Your brief is to provide the manager of your context with computer solutions to their problems

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 DiagramsSales

Representative

Invoice Clerk

Bill Customer Customer

Record Sale Data Flow Diagrams will be crucial in helping you understand and explain the systems

You will need to use these diagrams to explain both 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

Problem Analysis

Design Proposal

Product Development

Product Testing

Product Evaluation

Project Development

Problem Specification

Problem Analysis

Design Proposal

Product Development

Product Testing

Product Evaluation

Remember, you do not have to actually create the spreadsheets, only think through the process to the design proposal stage.

Remember, you do not have to actually create the spreadsheets, only think through the process to the design proposal stage.

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 PredictionsLooking at Sales Figures

and/or profitability of investments

Measures of profitability

Forecasting using a range of statistical and graphical techniques

Keeping AccountsAn 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 TasksA 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 datao Other data

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