Q1. (15 marks)
A company specializing in selling hot tubes to spa houses has two main models: Aqua and Hydro. To
install a hot tube, the company needs to spend on three elements: common water pump, amount of
tubing, and installation labour hours. Related information for the two models are listed in the Table 1 below: Hot Tube
Installation Labour Hours Tubing Required Water Pumps Profit Model (hours) (feet) Required Aqua 9 12 $ 350 1 Hydro 6 16 $ 300 1 Table 1
Determine the optimal numbers of Aqua and Hydro which needs to be sold such that the company can
earn maximum profit. Known that, the company expects to have 200 water pumps, 1566 hours of
labour, and 2800 feet of tubing available during the production cycle. Name the worksheet as Company. Q2. (35 marks) a.
Create a worksheet named as Report and then create the following table: Document Tax Inclusive Supplier Reference Description Date Amount 14-02-11 XY Solutions S77782 Opening Balance $ 5,100 IS 15-02-11 Communications Invoice EXP22 Internet Service Provider $ 179 16-02-11 Newscorp I381119 Subscriptions $ 478 17-02-11 EAG Brokers Debit Order Insurance $ 340 18-02-11 Capital Bank Bank Statement Service Fees $ 50 19-02-11 Capital Bank Bank Statement Service Fees $ 35 20-02-11 IAS Accountants Invoice Bookkeeping $ 1,000 21-02-11 Interflora Cash Flowers $ 90 22-02-11 QQ International TR6998 Parking $ 200 23-02-11 Example (Pty) Ltd Transfer Inter Account Transfer $ 15,000 24-02-11 Example (Pty) Ltd Transfer Inter Account Transfer $ (13,000) Total $ - Table 2
Calculate the Total of the Tax Inclusive Amount.
b. The Report needs to include 3 months. Table 2 has shown the details in the 1st month. Details of
the remaining 2 months are described as follows: 2 nd month : Document Tax Inclusive Supplier Reference Description Date Amount 14-02-11 XY Solutions S77782 Opening Balance $ 5,100 IS 15-02-11 Communications Invoice EXP22
Internet Service Provider $ 200 16-02-11 Newscorp I381119 Subscriptions $ 500 17-02-11 EAG Brokers Debit Order Insurance $ 380 18-02-11 Capital Bank Bank Statement Service Fees $ 20 19-02-11 Capital Bank Bank Statement Service Fees $ 35 20-02-11 IAS Accountants Invoice Bookkeeping $ 1,500 21-02-11 Interflora Cash Flowers $ 60 22-02-11 QQ International TR6998 Parking $ 250 23-02-11 Example (Pty) Ltd Transfer Inter Account Transfer $ 15,000 24-02-11 Example (Pty) Ltd Transfer Inter Account Transfer $ (13,000) 3 rd month: Document Tax Inclusive Supplier Reference Description Date Amount 14-02-11 XY Solutions S77782 Opening Balance $ 5,100 IS 15-02-11 Communications Invoice EXP22
Internet Service Provider $ 150 16-02-11 Newscorp I381119 Subscriptions $ 400 17-02-11 EAG Brokers Debit Order Insurance $ 200 18-02-11 Capital Bank Bank Statement Service Fees $ 50 19-02-11 Capital Bank Bank Statement Service Fees $ 35 20-02-11 IAS Accountants Invoice Bookkeeping $ 900 21-02-11 Interflora Cash Flowers $ 90 22-02-11 QQ International TR6998 Parking $ 100 23-02-11 Example (Pty) Ltd Transfer Inter Account Transfer $ 15,000 24-02-11 Example (Pty) Ltd Transfer Inter Account Transfer $ (13,000) i.
Record the data using Scenario Manager with 3 scenarios named as 1st month, 2nd month,
and 3rd month. Notice that the changing variables are displayed in italic and bold in Tax
Inclusive Amount in 2nd month and 3rd month tables. ii.
Create a Summary Report to
summarize the 3 scenarios with Total of the Tax Inclusive
Amount (mentioned in part a) as output. Indicate the names in the Summary Report properly. Q3. (35 marks) a.
Create 4 tables: Customer, Food and Drink, Movies, and Payment. Details of the given tables are described as below:  Customer Customer ID Last name First name Mobile Address 1 Allen Eli (+45)7891023 15 Hackney Street 2 Barnes Sarah (+75)1235894 19 London Road 3 Cates Pearl (+44)2356890 28 Sackville Street 4 Sarah Yates (+55)7456341 12/34 Oxford 5 George Washington (+14)7294354 14/14/17 Portal 6 Theory Bank (+88)4589712 233 Portland Street Table 3. Customer
Requirements of field names:
Mobile: follows the style “(+11)111111”
Others: Use appropriate arguments to choose the suitable data types  Food and Drink Food Order Food Types Quantity Price Date of Order FD1 Meat 2 $30.00 1/8/2015 FD2 Extra 5 $15.00 12/9/2016 FD3 Meat 1 $12.00 9/11/2016 FD4 Vegetables 2 $40.00 7/8/2017 FD5 Vegetables 7 $35.00 3/4/2017 FD6 Extra 10 $50.00 4/4/2017 Table 4. Food and Drink
Requirements of field names:
Food Types: create a (single selection) drop down list contains: Meat, Vegetables, and Extra
Price: follows exactly the style given in the Table 4.
Date of Order: follows the style “01/01/1111”
Others: Use appropriate arguments to choose the suitable data types  Movies Countr Movie Order Movie names Movie Types Date of release Quantity Price y Action, M1 Destroyer 09-09-2017 China 2 $30.00 Detective Action, M12 The Mummy 10-10-2017 India 1 $10.50 Supernatural M2 Avengers Action 06-06-2017 US 3 $5.00 Horror, M3 The Magicians 11-07-2017 US 7 $85.00 Supernatural Sherlock Detective, TV M4 02-03-2017 UK 4 $60.00 Holmes Series Action, TV M56 Sharpe 04-04-2017 UK 12 $110.00 Series M78 Dark Water Horror 05-05-2017 Other 1 $6.00 Table 5. Movies
Requirements of field names:
Movie Types: create a multi-selection drop down list contains: Action, Detective,
Supernatural, Horror, and TV Series
Date of Order: follows the style “01-01-1111”
Price: follows exactly the style given in the Table 5.
Others: Use appropriate arguments to choose the suitable data types Custo Payment Payment Card Card Expire Food Movie Sort code mer ID method types number date Order Order ID 1 Card Visa 1234 5678 40-25-44 02-2019 1 FD1 M78 2 Card Maestro 1567 8944 55-55-55 08-2019 2 FD6 M56 3 Cash 0 3 M4 4 Cheque 0 4 FD3 5 Transfer 0 5 FD4 6 Card Visa/Debit 7744 5566 12-23-32 11-2020 5 M12 7 Card Master 4457 7888 96-96-96 04-2020 6 FD2 M2 8 Card Maestro 7452 1230 45-45-45 04-2021 6 FD5 M3 Table 6. Payment  Payment
Requirements of field names:
Payment method: create a (single selection) drop down list contains: Card, Cash, Cheque, and Transfer
Card types: create a (single selection) drop down list contains: Visa, Maestro, Visa/Debit, and Master
Card number: follows the style “1111 1111”
Sort code: follows the style “11-11-11”
Expire date: follows the style “month-year”
Others: Use appropriate arguments to choose the suitable data types
b. Create relationships for the 4 tables. Known that, the three tables Customer, Food and Drink, and
Movies have “One-to-Many” relationship to table Payment. Show clearly these relationships. Q4. (15 marks)
Use the tables in Q3 to create a.
Investigation query to show Last name and First name of any persons who has number 7 at the
middle position of Mobile and has the Payment method as “Cheque”.
b. Total price query to show
i. Last name, First name, Price of Food and Drink table, Quantity
of Food and Drink table, Movie names Price ,
of Movies table, Quantity of Movies table.
ii. Add additional column named Total which shows the following formula:
Total=Price ( Food∧Drink )∗Quantity (Food∧Drink )+Price (Movies)∗Quantity ( Movies) c.
Movie price query to show
 Movie names and Date of release
 Additional column named as Late month Price which calculates the total price of each of
listed movie names (by multiplying with the Quantity). Known that this column only
calculates the price for any movies whose Date of release has value of the month less than
7 and the Payment method as “Card”. Otherwise, “N/A” is displayed if the month of
release is greater or equal to 7. For examples, the results could be obtained as follows: Movie names Date of release Payment method Late month Price Dark Water 05-05-2017 Card 6 Sharpe 04-04-2017 Card 1320 Avengers 06-06-2017 Card 15 The Magicians 11-07-2017 Card N/A