BELAIR HIGH SCHOOL

CSEC INFORMATION TECHNOLOGY SBA

Description of the Project

Mr. Green owns a small landscaping, lawn care and tree trimming business with

six employees. He owns the lawn mowers, rakes, and weed-whackers and asks

you to help him to track customer service dates, payments and lawn care

revenue. He uses a book to hold details of his customers and payments, and the

prices of the various services and he wants to move to an electronic system. He

is considering using a loan to purchase some more equipment. To help Mr. Green

you need to work with different application software including word-processing,

web page design, spreadsheet, database management and a suitable

programming language.

Spreadsheet
Mr. Green has mandated you to a design a spreadsheet that will accept

pertinent data on Customers. Mr. Green wants to be able to use the data in the

spreadsheet to calculate the total amount paid for his services and their cost.

He would then like to be able to work out if he can afford a loan to buy new

equipment. The spreadsheet is design to have Twenty-Eight (28) customers.

You are required to:

Task 1
1. Rename sheet 1 to KEY and design a spreadsheet with the table below.

ServiceID Service Type Cost

LS01 Landscaping $ 57,000

LC02 Lawn Care $ 45,000

TT03 Tree Trimming $ 36,000

1
 BELAIR HIGH SCHOOL
CSEC INFORMATION TECHNOLOGY SBA

2. Create a new ServiceID, Service Type and Cost using the format in the table

above.

3. Using ANOTHER worksheet, design a table to record the following information

about Mr. Green’s customers: CUSTID, CustFName, CustLName, PayID,

Amount Paid, Payment Due, Payment Date, Comments, Serv ID, Service Type,

Cost, Service Day (Mon To Sat).

a) Add an additional suitable record for the customer information.

b) Increase the font size of all headings to 14 point.

c) Use the wrap text feature for the Amount Paid and Payment Date

headings.

d) Sort the data by the ServiceType field in ascending order.

e) Rename the worksheet as Data.

f) Make a duplicate of the worksheet Data. Name this sheet Data Backup.

4. Using the Data worksheet, in row 1 of the next blank column, type the heading

Discount. In this column use functions and/or formulas to deduct 10% on every

job carried out on Monday, otherwise leave the cell blank. Place the 10% value

in a new sheet, renamed as EXTRA. Format the column for currency with no

decimal places. Save this workbook as

FIRSTNAME_LASTNAME_SPREADSHEET.

Task 2
You will now use suitable areas of the Data worksheet within your spreadsheet
document to apply the following calculations to answer Mr. Green’s queries.

2
 BELAIR HIGH SCHOOL
CSEC INFORMATION TECHNOLOGY SBA
5. Use the following layout to compare the total amount paid by customers
and the total cost of the jobs. Be sure to deduct the discounts applied.

Total Paid by Customers

Total Cost of Services
Overall

6. Select the range of text and data in the above layout and name it as Summary.
7. Supplies for the landscaping machines are estimated at 8% of the total
amount paid by customers. Calculate the amount paid in supplies.
Place the 8% in the EXTRA sheet.
8. Use the advanced filter feature to extract data for customers who paid
on a specific date of your choice.

Charting and Summary Operations

9. Use the pivot table feature to determine how much money Mr. Green should

be earning from each type of service. Rename this sheet as Services.

10. Create only ONE of the following charts:

EITHER compare the total money paid by customers with total cost of services

OR

create a chart from the data in the Service sheet.

11. On the Data sheet, insert 2 blank row above the headings. Enter the title for

the Spreadsheet, “Mr. Green’s Lawn Care Service”:

(a) Merge and center this heading across the width of the data within your

spreadsheet document.

(b) Increase the font size to 16 and select a READABLE font of your choice.

12. Save your spreadsheet workbook as:

FIRSTNAME_LASTNAME_SPREADSHEET_FINAL.

3
 BELAIR HIGH SCHOOL
CSEC INFORMATION TECHNOLOGY SBA

Database Management
To replace Mr. Green’s book, you will create a database containing
information on customers, payment, services and prices. The data to
populate the tables must be sourced from the spreadsheet section of
your project.
Design and create a database with following the tables and fields. Determine
the most appropriate data type for each field and select suitable primary keys.

Table Name Customer Payment Price Job

Description of Customer’s name Captures the Various Day of service
table and other data of payment description and (Mon to Sat) and
information by customers fees and the type of
service required

Field#1 CUSTID PayID ServID CUSTID

Field#2 CustFName CUSTID ServiceType ServID

Field#3 CustLName Amount Paid Cost ServiceDay

Field#4 Your personal field Payment Due
Field#5 Comments

1. Ensure that you have:

a) Twenty-Eight (28) customers for the Customer Table
b) Twenty-Eight (28) payments for the Payments Table.
2. Join the tables to link the data.

You should test your database by generating queries, reports and control
forms:

4
 BELAIR HIGH SCHOOL
CSEC INFORMATION TECHNOLOGY SBA

Queries
3. Create the following queries to answer Mr. Green’s questions:

a) List the customers who have requested one type of service (for example,

lawn care), along with their service day. Include customers’ first and last

names, service type and service day. Sort the list by last name. Name this

query Q1Service.

b) List the customers who have requested lawn care on Friday. Include

customers’ id, first and last names and service day. Name this query

Q2LawnFri.

c) Create a new field called Increase which shows the updated cost when

10% is added to the original cost. Include the Cost field. Format the new

field for currency. Name this query Q3 Increase.

Forms
4. Create a form to enter customer payments. Use the CUSTID, CustFName
and CustLName fields for the main form, and the Amount Paid, Payment
Date and Comment fields for the sub-form. Save the title of the main form
as PAYMENTS FORM.

Report
6. Create a report containing the customers’ first and last names, service day,

service type, amount paid, payment date. Group the report by the Service Day

field and sort the customers’ last name in ascending order. Give the report the

title Customers by Service Day.

5
 BELAIR HIGH SCHOOL
CSEC INFORMATION TECHNOLOGY SBA

Word-Processing
Two task are provided. Candidates MUST complete both task.

1. Create a flyer, brochure or letter that informs Mr. Green’s customers of

upcoming specials for his business. Be sure to include a graphic that depicts

his business. Provide a merged document for any one of his services.

2. Create a fillable form for new customers. Be sure to include all relevant fields.

Give the form the heading ‘Form for New Customers’.

Web Page Design

Mr. Green is interested in having a web page. Since you are the expert you are

asked to create this website that would be used to keep his current and potential

customers informed about any specials and updated landscaping services.

To create this web page:

a) Use a free web application such as www.webnode.com, or www.wix.com

b) Determine the scope and content of the website once it meets the

needs of Mr. Green’s business.

c) Include your centre number and candidate number on the website for

moderating purposes.

d) Ensure that the website contains:

1. Branding (logo, name or other signage of the business)

2. Navigational links to specials and new services

3. Suitable graphics

4. A ‘contact us’ form.

6
 BELAIR HIGH SCHOOL
CSEC INFORMATION TECHNOLOGY SBA
e) Create a word processing document and place a link to the web page as well as

screenshots of the web page.

Problem-Solving
Mr. Green is thinking of purchasing some new equipment. He is thinking of

investing twice the amount of money that he made for the year or $5,000,000

whichever is greater. He has checked with three banks and their interest rates and

repayment periods in years are: Bank A: 7.25% over 3 years; Bank B: 6.75% over

4 years and Bank C: 6.45% over 5 years. You need to help Mr. Green to determine

what his monthly payments will be for each of these options, including his total

loan repayment amount and recommend which option he should choose.

1. Create an algorithm to input the loan amount, interest rate and number of

years to repay and then output the monthly payment amount, total

interest paid at the end of the loan, and total repayment amount.

2. Create a trace table with five samples of test data to test your algorithm

from question 1.

Program Implementation
1. Create a program in Pascal to input the loan amount, interest rate and

number of years to repay. Then display the monthly payment amount, total

interest paid at the end of the loan, and total repayment amount.