Sample Final Question - Business Computing Skills | Trường Đại học Quốc tế, Đại học Quốc gia Thành phố HCM

Sample Final Question - Business Computing Skills | Trường Đại học Quốc tế, Đại học Quốc gia Thành phố HCM được sưu tầm và soạn thảo dưới dạng file PDF để gửi tới các bạn sinh viên cùng tham khảo, ôn tập đầy đủ kiến thức, chuẩn bị cho các buổi học thật tốt. Mời bạn đọc đón xem!

Thông tin:
5 trang 7 tháng trước

Bình luận

Vui lòng đăng nhập hoặc đăng ký để gửi bình luận.

Sample Final Question - Business Computing Skills | Trường Đại học Quốc tế, Đại học Quốc gia Thành phố HCM

Sample Final Question - Business Computing Skills | Trường Đại học Quốc tế, Đại học Quốc gia Thành phố HCM được sưu tầm và soạn thảo dưới dạng file PDF để gửi tới các bạn sinh viên cùng tham khảo, ôn tập đầy đủ kiến thức, chuẩn bị cho các buổi học thật tốt. Mời bạn đọc đón xem!

86 43 lượt tải Tải xuống
Q1. (15 marks)
A company specializing in selling hot tubes to spa houses has two main models: and . ToAqua Hydro
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
Model
Installation Labour Hours
(hours)
Tubing Required
(feet)
Profit
Water Pumps
Required
Aqua 9 12 $ 350 1
Hydro 6 16 $ 300 1
Table 1
Determine the optimal numbers of and which needs to be sold such that the company canAqua Hydro
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 and then create the following table:Report
Document
Date
Supplier Reference Description
Tax Inclusive
Amount
14-02-11 XY Solutions S77782 Opening Balance $ 5,100
15-02-11
IS
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 of the .Total Tax Inclusive Amount
b. The Report needs to include 3 months. has shown the details in the 1 month. Details of
Table 2
st
the remaining 2 months are described as follows:
2
nd
month :
Document
Date
Supplier Reference Description
Tax Inclusive
Amount
14-02-11 XY Solutions S77782 Opening Balance $ 5,100
15-02-11
IS
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
Date
Supplier Reference Description
Tax Inclusive
Amount
14-02-11 XY Solutions S77782 Opening Balance $ 5,100
15-02-11
IS
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 $ 50Service Fees
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 , ,
1
st
month 2
nd
month
and . Notice that the changing variables are displayed in italic and bold in
3
rd
month Tax
Inclusive Amount
in 2 month and 3 month tables.
nd rd
ii. Create a to summarize the 3 scenarios with of the Summary Report Total Tax Inclusive
Amount (mentioned in part a) as output. Indicate the names in the Summary Report properly.
Q3. (35 marks)
a. Create 4 tables: , , , and . Details of the given tables areCustomer Food and Drink Movies Payment
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
Movie Order Movie names Movie Types Date of release
Countr
y
Quantity Price
M1 Destroyer
Action,
Detective
09-09-2017 China 2 $30.00
M12 The Mummy
Action,
Supernatural
10-10-2017 India 1 $10.50
M2 Avengers Action 06-06-2017 US 3 $5.00
M3 The Magicians
Horror,
Supernatural
11-07-2017 US 7 $85.00
M4
Sherlock
Holmes
Detective, TV
Series
02-03-2017 UK 4 $60.00
M56 Sharpe
Action, TV
Series
04-04-2017 UK 12 $110.00
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
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 , , andCustomer Food and Drink
Movies Payment have “One-to-Many” relationship to table . Show clearly these relationships.
Q4. (15 marks)
Use the tables in to createQ3
a. Investigation query to show and of any persons who has number 7 at theLast name First name
middle position of and has the as “Cheque”. Mobile Payment method
b. Total price query to show
i. Last name First name Quantity , , Price of table, Food and Drink of table,Food and Drink
Movie names Price , of table, Movies Quantity of table.Movies
ii. Add additional column named which shows the following formula:Total
=
(
)
(
)
+
( )
( )
c. Movie price query to show
Movie names Date of release and
Additional column named as which calculates the total price of each ofLate month Price
listed movie names (by multiplying with the ). Known that this column onlyQuantity
calculates the price for any movies whose has value of the month lessDate of release
Payment
ID
Payment
method
Card
types
Card
number
Sort code
Expire
date
Custo
mer
ID
Food
Order
Movie
Order
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
than 7 and the as “Card”. Otherwise, “N/A” is displayed if the month ofPayment method
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
| 1/5

Preview text:

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. PaymentPayment
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: = ( ∧ )∗ ( ∧ )+ ( )∗ ( ) 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