Information Technology (INT1001) School of Computing in Technology In course Excel Test 2021 Instructions A. DO NOT SHARE THIS PAPER WITH ANY OTHER CLASS GROUP AS THE DOCUMENT IS BEING TRACKED. B. No communication through any medium to your classmates or external help is strictly prohibited. C. No solution will be offered by the lecture nor your peers, you are now being tested and the time for assistance has long passed. 1. Save your test document as your “YOUR ID NO – FIRSTANDLASTNAME - EXCEL TEST (1 mark) 2. Change the name of Sheet 1 to VLOOKUP QUESTION AND CHANGE THE TAB COLOR TO (PURPLE, ACCENT 4 AND DARKER 25%) (1 mark) 3. Enter the following information in the order presented belows: (5 marks) a A B C D E F 1 UPTOPBOSS INVENTORY MANAGEMENT 2 DATE ITEM QUANTITY LOCATION COST PER ITEM DISCOUNT TO BE APPLIED FINAL TOTAL 3 22/1/2022 Iphone 13 Pro Max 15 Barnett Street 4 23/1/2022 Samsung Galaxy 22 13 Dome Street 5 24/1/2022 Huawei P50 Pro 10 North Street 6 25/1/2022 Google Pixel 6 7 26/1/2022 Moto G7 Pro 5 Dome Street 40 Barnett Street 8 27/1/2022 Oneplus Max 12 Dome Street 9 28/1/2022 Xiaomi Note 15 Pro 30 Dome Street 10 29/1/2022 Samsung Galaxy 22 16 Barnett Street 234 11 30/1/2022 Huawei P50 Pro 12 31/1/2022 Moto G7 Pro 1/2/2022 Samsung Galaxy 22 2/2/2022 Iphone 13 Pro Max 3/2/2022 Xiaomi Note 15 Pro 15 16 4/2/2022 Iphone 13 Pro Max 17 5/2/2022 Moto G7 Pro 18 19 MAXIMUM QUANTITY 20 MINIMUM QUANITY 14 Barnett Street 2 Barnett Street 9 North Street 25 North Street 18 North Street 25 Dome Street 50 Dome Street TOTAL SALES AVERAGE OF SALES G b A B 16 4/2/2022 Iphone 13 Pro Max 17 5/2/2022 Moto G7 Pro 18 19 20 MAXIMUM QUANTITY MINIMUM QUANITY 21 с D E F G H 9 North Street 50 Dome Street TIMES QUANTITIES 22 WERE 23 24 26 27 222222 BRANCHES Dome Street North Street Barnett Street FINAL TOTAL PER LOCATION с TOTAL SALES AVERAGE OF SALES TARGET TOTAL SALES | J K L M N ° P Q 13 14 15 16 17 18 19 20 ITEMS ITEM COST 21 IPHONE 13 PRO MAX 350,000 22 SAMSUNG GALAXY 22 320,000 23 HUAWEI P50 PRO 220,000 24 GOOGLE PIXEL 6 200,000 25 MOTO G7 PRO 120,000 26 ONEPLUS MAX 150,000 27 XIAOMI NOTE 15 PRO 80,000 28 29 30 31 32 33 30-ABOVE 20 - ABOVE 15-ABOVE ANY THING ELSE 30,000 25000 10,000 5000 4. The words Uptop Boss Inventory Management should be in font type Arial Black, font Colour-navy blue, Size- 36 and fill colour – green. (2 marks) R 20 5. Cells a2 to g2 should consist of font type Britannic bold, size – 12, font colour - white, fill colour-blue, accent 1, lighter 40%, underlined (2.5 marks) 6. Fill in cells E3 TO E17 by utilizing the VLOOKUP Function ONLY (5marks) 7. Fill cells F3 TO F15 (by using the IF Function ONLY). Quantities of 30 or above receive a $30,000.00 discount, 20 or above receive a $25,000.00 discount, 15 or above $10,000.00 discount and anything else $5,000.00 discount. All the products whose quantity amounts are listed in 029:031. (8 marks) 8. List the Amount of Quantities ordered in cells A23 to A25 utilizing the COUNTIF functions. (3 marks) 9. Using a SUMIF function provide the solution for cells C23 to C25. (3 marks) 10. Create a pie-chart showing Product Name and Final cost with the title (3 marks). 11. By increasing the quantity for the product contained in C15, how can a goal total of $20,000,000.00 in cell F19 be achieved? ( 4 marks) 12. Find the Maximum and Minimum Sales, total Sales and average sales. (4marks) QUESTION 2. 1) Rename sheet 2 to Amortization Semester 3 (1 mark) 2) Complete the following question: Drax Hall Real Estate has found a piece of property in the posh area of Ironshore, Montego Bay St. James valued at 80 million Jamaican dollars. He has accessed financing through a mortgage that was acquired through the BITCOIN BANK. The Bank's annual interest rate is 9 % for the next 10 years. Using the pictorial template below, calculate the amortized monthly rate, payments, principal, interest, beginning and ending balance. (As described in your practical class sessions). (12 marks) 1 AMORTIZATION CALCULATOR 2 PRINCIPAL 3 LOAN PERICO (YEARS) 4 LOAN PERIOD (MONTHS) 5 TOTAL INTEREST RATE 6 MONTHLY INTEREST RATE 7 MONTHLY PAYMENT 8 9 D E F G 10 PERIODS MONTH OPENING BALANCE 11 INTEREST PER PRINCIPAL PAID PRE-PAYMENT MONTH CLOSING BALANCE 12 13. After Completion please upload your file to the google class room link (2marks).

Solved: Information Technology (INT1001) School of Computing in Technology In

Home
questions and answers
information technology int1001 school of computing in technology in co

Question

Information Technology (INT1001) School of Computing in Technology In course Excel Test 2021 Instructions A. DO NOT SHARE THIS PAPER WITH ANY OTHER CLASS GROUP AS THE DOCUMENT IS BEING TRACKED. B. No communication through any medium to your classmates or external help is strictly prohibited. C. No solution will be off