| Date | November 2020 | Marks available | 5 | Reference code | 20N.2.SL.TZ0.4 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | Construct | Question number | 4 | Adapted from | N/A |
Question
EnviroBuild is a construction company that recently purchased land. It has permission to build either small houses or large houses but not both types of house. The maximum number of small houses that can be built is 10. The maximum number of large houses that can be built is five.
The cost of building the houses is calculated using a model with the following three variables:
House_Type: The type of house (small or large).
House_Num: The number of houses to be built.
Profit: The total sales revenue minus the land costs, labour costs and material costs.
The decision whether to build small houses or large houses is a purely financial one and is based on the following information:
- A single payment of $500 000 for the land, regardless of whether 10 small houses or five large houses are to be built.
- The revenue from each house sale will be $220 000 for a small house and $400 000 for a large house.
- The labour costs will be $2500 per day, regardless of the type of house built.
- It will take 17 days to build each small house and 23 days to build each large house.
Houses are built sequentially so that they can be sold as soon as they are completed. - The material costs will be $100 000 for each small house and $190 000 for each large house.
Identify the data types for the House_Type, House_Num and Profit variables.
Construct a spreadsheet model that shows the total profit for the chosen type of house. The user must input the House_Type and House_Num to calculate the total profit.
Outline two validation tests that should be included in the test plan for this spreadsheet model.
To finance this project, EnviroBuild took out a bank loan of $400 000 and will be required to pay interest on this loan. The project starts on 1 January 2021.
The following steps are used to calculate the total profit:
- Read the
Profitvariable and theNo_of_Daysvariable from the spreadsheet model in (b). - Calculate the number of months that the project will take.
- There are no partial months.
- For example, if a project finished on 1 July 2021, the loan interest rates will include July: the project will last for 7 months.
- The rate of interest on the bank loan of $400 000 is 1 % per month.
- The land tax is $500 per month.
Construct the pseudocode that will calculate the profit after these additional costs have been considered. You can introduce any new variables, if necessary.
Markscheme
Award [3 max]. House_Type: char/string/Boolean;House_Num: integer;Profit: fixed-point decimal/currency/float/double;
Award [6 max].
Award [1] for including HOUSE_TYPE and HOUSE_NUM as input
Award [1] for calculating total revenue;
Award [1] for calculating the number of days for each project
Award [1] for calculating the labour costs
Award [1] for calculating material costs
Award [1] for calculating the profit
REVENUE: Award [1] for Sales_price * House_Num based on the If statementNUM_DAYS: Award [1] for the correct number of days * house_num.LABOUR_COSTS: Award [1] for NUM_DAYS * 2500MATERIAL_COSTS: Award [1] for checking the HOUSE_TYPE and then multiplying HOUSE_NUM by the correct material cost.PROFIT: Award [1] for deducting all of the costs from the revenue.
Example answer:
Alternative solution:
Note: accept cell references such as B3 instead of variables
Award [4 max].
Award [2 max] for each variable tested.
Award [1] for the type of test and [1] for example test data.
Type test for HOUSE_TYPE
Normal: s or l / small or large; Abnormal: m / medium.
If Boolean has been used Normal: True or False; Abnormal: “small”
Range test for NUM_HOUSES
Normal HOUSE_NUM = 5 / Abnormal HOUSE_NUM = 12 / Extreme HOUSE_NUM = 6 or HOUSE_NUM = 10
Accept any testing that relates to:
- normal data // Note: an example will get this mark
- extreme data
- abnormal data.
Award [5 max].
Award [1] for reading the profit, number of days from csv file / cell range.
Award [1] for adding the number of days to the date.
Award [1] for extracting the month from the date.
Award [1] for calculating interest and rates.
Award [1] for deducting the costs from the profit and displaying result.
Example pseudocode:
import FILE.CSV As SS
read PROFIT, NUM_DAYS from SS
END_DATE = #01/01/2021 + NUM_DAYS
NUM_MONTHS = MONTH(END_DATE)
COSTS = (NUM_MONTHS * 500) + (NUM_MONTHS * (400000 * .01))
output (PROFIT – COSTS)
Alternative pseudocode:
input Profit, NumDays from spreadsheet
endDate = date(dateval("1/1/21") + NumDays)
monthsTaken = month(endDate)
interest = 400000 * 0.01 * monthsTaken
output "Net profit = $", (Profit – interest)
