| Date | November 2017 | Marks available | 2 | Reference code | 17N.2.SL.TZ0.5 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | Suggest | Question number | 5 | Adapted from | N/A |
Question
A manufacturing company that produces several products is using spreadsheet software to model its finances. This includes calculations that will estimate different quantities including the profit that the company will make in future years.
The model involves the use of spreadsheet software which will be organized using different sheets for different areas of the company’s finances. Previously less sophisticated methods were used to keep track of costs and sales.
By including examples where appropriate, describe a basic structure for this model.
Suggest how the reliability of the model could be tested.
The company has established certain profit targets that it wishes to achieve over the next three years.
Explain how this model can be used to investigate different strategies that will reach these targets.
Markscheme
Award up to [6 max].
Different sheets will be dedicated to different categories, e.g. salaries, running
expenses, sales, different products, different months etc.;
Note: Award [2 max] for identifying at least two categories.
Each sheet will contain a list of items for that category including associated values;
Formulas will be included as necessary;
Each sheet/category will include a formula that totals the values in that category;
Intermediate values will be calculated, e.g. tax to be paid;
The final profit will be determined from the previous totals;
Figures from previous years can be entered into the model;
With the results checked against the previously calculated results;
Award up to [4 max].
Different (“what-if”) scenarios can be run;
In which the values of different variables are changed;
For example, the number of items sold / the increase in the level of salaries;
Note: Award [2 max] for identifying at least two items.
Selling prices/other (acceptable) parameters can be adjusted to achieve the desired profit;
Examiners report
Syllabus sections
-
18M.2.SL.TZ0.4a:
Copy and complete the following table showing the variables, each variable’s data type and range of values that would represent the information shown above.
-
18M.2.SL.TZ0.4d:
With the help of a diagram, suggest an appropriate design for a spreadsheet used to calculate the final selling price, following this model.
-
19M.2.SL.TZ0.4a:
Copy and complete the following table showing each variable’s data type and a suitable range of values that would represent the information shown above.
-
19M.2.SL.TZ0.5b:
Identify three other variables that could be included in this model.
-
20N.2.SL.TZ0.4a:
Identify the data types for the
House_Type,House_NumandProfitvariables. -
18M.2.SL.TZ0.6a.i:
With relation to the activities that the software of an ECU has to perform, identify two of the sources that provide input data to the ECU.
- 17N.2.SL.TZ0.4b: Identify two reasons why some systems are difficult to model successfully.
-
17N.2.SL.TZ0.5c:
The company has established certain profit targets that it wishes to achieve over the next three years.
Explain how this model can be used to investigate different strategies that will reach these targets.
-
17N.2.SL.TZ0.5a:
By including examples where appropriate, describe a basic structure for this model.
-
18M.2.SL.TZ0.6a.ii:
With relation to the activities that the software of an ECU has to perform, suggest one reason why the auto-diagnostic program in the ECU depends upon the make and model of the vehicle.
-
18N.2.SL.TZ0.4a:
Identify the possible range of values and their data type for each of the above variables.
-
19N.2.SL.TZ0.5b.i:
Identify two data inputs for this computer simulation model.
-
18M.2.SL.TZ0.4e:
Identify two tests that should be included in the test plan for this model.
-
19N.2.SL.TZ0.5b.ii:
Identify two criteria that may be used to determine the best location to build the distribution centre.
