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 Profit variable and the No_of_Days variable 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.

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 statement
NUM_DAYS: Award [1] for the correct number of days * house_num.
LABOUR_COSTS: Award [1] for NUM_DAYS * 2500
MATERIAL_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)