Solving Problems with Spreadsheet Models

1. Solving Problems with Spreadsheet Models

2. This lesson will cover: • Using spreadsheet models to assist with assessment. • Financial forecasting. • Using Goal Seek to reduce queues.

3. Case study: assessment A teacher has been looking at the marks for her class and wondering how to improve their results. She has set a target grade for each of her students; some have reached it and some have not. Her students have been working on 4 different modules. They can resit one of those modules in the final session. She needs to decide which module each student should resit. The extra revision is likely to improve their marks, but she is not sure by how much.

4. The rules • If a student resits a module, the highest mark counts • Each module is marked out of 100 • At the end of the course, the best mark for each module is added up to form a total • You need these marks for each grade.

5. Pick a function

6. Meeting targets Now she has the totals, the teacher wants to know which students have met their target scores. An IF function will compare the two values and give a message based on any rule she chooses. In this case she will set up a message that says “met target” if the student got their target mark or better. The message will say “below target” if the student did not get their target points.

7. Conditional formatting Another way of showing how well the students perform is called conditional formatting. This changes the way the cell looks depending on a rule the teacher sets. In this case, she has chosen to colour cells like this: • Better than target – green • Met target – yellow • Lower than target – red

8. Looking up the grades Remember the table of grades? How do we find a grade from it? We take the mark the student got, and find it in the first column. We then write down the mark from the second column Raj got 241 points. What grade did she get? 241 is over the B threshold but not enough for an A, so she got a B.

9. VLOOKUP Spreadsheets can speed up this process by using the VLOOKUP function. • Look up the mark in H2 in the table called grades • Give the answer from column 2

10. Counting up the grades The teacher wants to know how many students got each grade. We can use a COUNTIF function to do that: =COUNTIF($I$2:$I$8,"E") The column with the grades in. The grade we are counting.

11. Modelling Now the teacher has set up the rules, she can experiment with the values to find the best way to increase the overall grades. She can ask questions such as “What if all students got ten more marks in Module 3?”

12. Financial forecasting Changing data in a spreadsheet allows you to model theeffect of altering different variables. For example, a fashion company has decided to put on a fashion show. They have added up all their costs andrevenues and put them into a spreadsheet. On their first projectionit looks like they will make a big loss. There are savingsavailable to cover this but they still want to make a profit. Look at the next slide and decide what they could alter.

13. Changing variables Some factors would be fixed costs. But other variables canbe changed. What could you alter to make an estimated profit?

14. Case study: the supermarket