Production of a pond kit modelling calculator.

The output at the end of this should an A4 printed report that takes up one side. The customer's name and the Plant House Garden Centre will head the page and descending the page will be distinct sections detailing the parameters and cost of the liner, underwater plants, decorative plant packs, additional items and the overall cost.

In order to fulfil each section of the task I need an appropriate spreadsheet software package - and for that I have chosen Microsoft (c) Excel 97/XP. It is a well - renowned piece of software and is specifically ideal for the following features:

* It has a function ability which allows the user to enter specific commands to which the system automatically calculates without user intervention or repetition of the same calculation. This would be particularly useful to find the sum of all the individual costs, without manually adding them up. Another specific example of this is the roundup function which is very helpful when determining the number of underwater plant bunches/decorative plant packs.

* It has the ability to extend decimal places which is useful for the accurate representation of for example pond liner dimensions and cost.

* It has an array of formatting functions, e.g. the addition of colour and reformatting text, allowing the positioning and aesthetics of the report to be good for a clear report - importantly the bold and underline functions accentuate important text, especially the overall cost sections of say the liners and underwater plant bunches, allowing for quick summary of costs

* In ensuring the effectiveness of the individual formula, a formula view option is available which allows for the quick checking and correction of complicated formula, such as perimeter of pond liner - crucial for determining the number of decorative plant packs there should be.

There will be two tests performed to ensure the effectiveness of the calculation systems:

. A pre-set and pre-solved set of dimensions (2m x 1.5m x 1) and calculation stages will have been made and at each stage of function, the answer will be checked against what the spreadsheet gives to pinpoint any error.

2. An unsolved problem (where the dimensions are 3m x 2m, depth unknown) will be presented and trial and error will be used to find a depth value that results in a pond kit valuing under £300. This will of course be checked against manual calculations.

Implementation

Testing

Worked Example (where pond length = 2m, pond width = 1.5m, pond depth = 1m)

Tested Component

Manual Calculation

Length of Pond Liner/Underliner (m)

2.5 x 1 + 2 = 4.5

Width of Pond Liner/Underliner (m)

2.5 x 1 + 1.5 = 4.0

Area of Pond Liner/Underliner (m squared)

4.5 x 4 = 18.00

Perimeter of Pond Liner/Underliner (m)

2 x (4.5 + 4) = 17.00

Total Cost of Pond Liner (£)

4.40 x 18 = 79.20

Total Cost of Pond Underliner (£)

0.90 x 18 = 16.20

Overall Cost of Pond Lining/Underlining (£)

79.20 + 16.20 = 95.40

Number of Underwater Plant Bunches

5 x 18 = 90

Overall Cost of Underwater Plant Bunches (£)

0.5 x 90 = 45

Number of Decorative Plant Packs

7 x 1 = 17

Overall Cost of Decorative Plant Packs (£)

3.50 x 17 = 59.50

Water Lily (£)

8.50

Fountain (£)

55.00

Total Cost of Pond Kit (£)

95.40 + 45 + 59.50 + 8.50 + 55.00 = 263.40

Spreadsheet Result

Validity

4.50

CORRECT

4.00

CORRECT

8.00

CORRECT

7.00

CORRECT

79.20

CORRECT

6.20

CORRECT

95.40

CORRECT

90.00

CORRECT

45.00

CORRECT

7.00

CORRECT

59.50

CORRECT

8.50

CORRECT

55.00

CORRECT

263.40

CORRECT

From this test, I can conclude no alterations need to be made (see implementation section for verification of these results)

Unsolved Example (where the length is 3m, the width is 2m and the depth is unknown an trial and improvement must be used to find its maximum value)

(For verification of these results, see implementation section)

Depth = 1m --> £326.68 --> TOO BIG

Depth = 0.85m --> £295.05 --> NOT CLOSE ENOUGH

Depth = 0.87m --> £299.01 --> CLOSE AS POSSIBLE

So it seems 3m x 2m x 0.87m is the optimum solution. A manual calculation is a way to check this

Liner Length = 2.5 x 0.87 + 3 = 5.28

Liner Width = 2.5 x 0.87 + 2 = 4.18

Liner Area = 5.28 x 4.18 = 21.61

Cost of Liner = 21.61 x 4.40 = 95.06

Cost of Underliner = 21.61 x 0.90 = 19.45

Cost of Underwater Plant Bunches = 5 x 21.61 = 109 (rounded up from 108.05) x 0.5

Cost of Decorative Plant Packs = 19 (rounded from 18.70) x 3.50 = 66.50

Total (including Water Lily and Fountain) = 299.00

Again, my spreadsheet has had its results verified so I feel no need to alter the formula structure of my spreadsheet

Evaluation

The desired outcome of this task was to create a spreadsheet system that employed effectively linked formulae to give an accurately modelled pricing system for a pond. My performance criteria were that it had to be clearly presented on one side of A4, with all formulae working accurately to give a reliable result, along with the appropriate input and embedded data, and that it was visually pleasing. Overall I can say I have achieved this - the testing results show that all my formula link in a linear way, but retain their accuracy in giving results, especially with the round up feature. Given that, my results are extremely accurate, but could be improved by removing the decimal point constraints on measurements for full accuracy. Of its cosmetics, I can say that it has been clearly presented on A4 and the block colour differentiation of section adds to the clarity, but still remains relatively proletarian in image. If I had more time, I would have liked to link to a word processor, so DTP could have improved the output design.

Mo Rahman 10B (J)

AQA ICT A Coursework