**Unit 42 - Spreadsheet Modelling**

Mrs Duffy

Mrs Duffy

Objectives...

**How will I be assessed?**

You will be graded against the criteria shown here

Some tasks are stand alone Pass grade tasks while others provide opportunities for you to demonstrate Merit or Distinction criteria.

These are clearly highlighted throughout the assignment brief and the Frog VLE pages you will be using.

Getting Started...

What is a spreadsheet?

On completion of this Unit, you should be able to:

understand how spreadsheets can be used to solve complex problems

be able to develop complex spreadsheet models

be able to automate and customise spreadsheet models

be able to test and document spreadsheet models

How?!

An Introduction

... and P1

Have a look at some of the stimulus below. What do you think are the key uses of spreadsheets? How might these help people in business or day to day activities?

Can you think of any examples of spreadsheets that affect you?

**Uses of Spreadsheets...**

Any problem which involves analysing lots of numerical data or completing calculations is ideally suited to spreadsheet software.

Often used for accountancy and finance

lots of numerical data

can record transactions made by that organisation

extra functionality in the spreadsheet (formulas mean that people don't need specialist maths skills to understand the info or complete them)

Data can easily be presented as graphs

Basic design of spreadsheets echoes 2D table of rows and columns

Using Spreadsheets for Complex Problems...

Essentially, spreadsheets are models that simulate a real-life situation. Spreadsheet models can be used to solve a number of problems, for example:

Cash Flow Forcasting

Budget Control

Sales Forecasting

Payroll projections

Statistical Analysis

Trend Analysis

Example

Your coursework Trackers are used to record a real-life situation (what work you have completed). They can Forecast what grade and UCAS points you are likely to get overall.

Q - What are the benefits and limitations of having the information stored in this way?

What are the benefits and limitations of having the information stored in this way?

What happens when information is changed in the spreadsheet?

Questions

Task...

Cash Flow Forecasting

Budget Control

What If Scenarios

Sales Forecasting

Payroll Projections

Statistical Analysis

Trend Analysis

You need to focus on one of the 'Complex Problems' below.

You need to explain how spreadsheets can be used to solve the problem.

You should consider:

Time

Money

Resources

Transferability

Educated Guesses

Presenting the information

Skill set of the user

Filters

Functions and Formulas

Errors (Validation of Data)

Storage

You will feed back your section to the rest of the class.

Assessment Task...

P1 - Explain how spreadsheets can be use to solve complex problems

Task...

Your line manager has asked you to write a report to explain how spreadsheets can be used to solve complex problems. The report will need to explain how spreadsheets can be used to; manipulate complex data, present for requirements and support decision making and support decision making covers (e.g. analysis of data, goal seeking, scenarios, regression and data mining)

Complex problems include: types (e.g. cash flow forecasting, budget control, what-if scenarios, sales forecasting, payroll projections, statistical analysis and trend analysis.

Use examples to support your explanations.

Submit: Report 3-6 pages (P1)

Deadline: 15/10/12

You will create a spreadsheet that includes:

multiple linked worksheets

complex formulae

data entry forms

lookup tables

...... and much more!!!

Watch the following video clip...

Questions

What is a Cash Flow Forecast?

How could a spreadsheet be used to create one?

Why would this be more efficient than on paper?

How could spreadsheets be used to solve this business problem?

Watch the following video clip...

How could spreadsheets be used to solve this business problem?

How could spreadsheets be used to solve this business problem?

How could spreadsheets be used to solve this business problem?

How could spreadsheets be used to solve this business problem?

How could spreadsheets be used to solve this business problem?

Do you think it would be easier or more difficult to use spreadsheets for Trend Analysis and forecasting data? Why?

What type of things could this be used for in business?

Questions

Question

Questions

Questions

Question

Questions

What is a Budget Control?

How could a spreadsheet be used to help with this?

Why would this be more efficient than on paper?

What is a Cash Flow Forecast?

How could a spreadsheet be used to create one?

Why would this be more efficient than on paper?

What is Sales Forecasting?

Why do businesses use it?

How could a spreadsheet be used to help with Sales Forcasting?

Why might this be more efficient than other methods?

Improving your Spreadsheet Skills!

Log onto the VLE, Unit 42 - Task 1a...

You need to practice /update some of your spreadsheet skills. Work through the activities on Formula and Functions tab of this page.

You will need to open Microsoft Excel to complete these activities.

Watch the following video clip...

Watch the following video clips...

How could spreadsheets be used to solve this business problem?

Watch the following video clip...

Why might you use spreadsheets to work out peoples pay?

Would this be easier than working out payroll on paper? Why?