Question 1 You will be creating an invoicing system for the sales of PC parts. The...

 

Transcribed Image Text:

Question 1 You will be creating an invoicing system for the sales of PC parts. The file "Assignment_Q1.xlsm" contains the worksheets "Sales Point", "Items Database", and "Voucher Database". The worksheet "Items Database" contains information on all the items that are for sale. The worksheet "Voucher Database" contains information on all the vouchers that are available to be applied to a sale and the conditions to use those vouchers. Note that only ONE voucher can be applied to a sale. The worksheet "Sales Point" will be the location for the invoice that will be generated for each sale. An example of the worksheet "Sales Point' is shown in Figure 1. Item Type Item Name Item Code Unit Price Quantity Memory Add to Cart Clear Last Item Clear Cart / Sales Add Voucher MEMOS Confirm Sales Cart Date: No Item Code Figure 1 Item Name Quantity Unit Price Total Figure 2 shows an example of having an item added to the cart. Note that the cells with yellow fill are the cells where the user may change values. You can use dropdown lists for the "Item Type" cell, "Item Name" cell, and "Add Voucher" cell. Note that the voucher MEM05 is active. Item Type Item Name Item Code Unit Price Quantity Memory Sandisk CZ50 16 GB S001 $17.00 Add to Cart Clear Last Item Clear Cart / Sales Add Voucher MEMOS Confirm Sales Cart Date: No 02/08/2021 Item Code 1 5001 Item Name Sandisk CZ50 16 GB Figure 2 Quantity Unit Price $16.15 1 Total $16.15 Figure 3 shows an example of a complete invoice that is generated after the button "Confirm Sales" is pressed. Note that the grand total, discounted grand total, GST calculated at 6% of the discounted grand total, and the final grand total which is the sum of the discounted grand total and the GST must be calculated and shown in the invoice. Notice that the invoice also has the date of the day. Item Type Item Name Item Code Unit Price Quantity Motherboard AMD Ryzen 5600X 3.7 GHz PO04 $1,368.00 Add to Cart Clear Last Item Clear Cart / Sales Add Voucher ALL10 Confirm Sales 2 Cart Date: No 02/08/2021 Item Code 1 5001 2 G001 3 P004 Item Name Sandisk CZ50 16 GB Palit RTX3060 Dual 12 GB AMD Ryzen 5600X 3.7 GHz Quantity Unit Price $17.00 $2,898.00 $1,368.00 Grand Total: Discounted Grand Total: GST 6%: Final Grand Total: $5,391.05 Marks will be given according to the following guideline: Correct calculations and information are shown in the invoice. Pressing buttons randomly do not give illogical results. User-friendly and visually pleasing. Total $17.00 $2,898.00 $2,736.00 $5,651.00 $5,085.90 $305.15 1 1 2 Figure 3 The button "Add to Cart" adds the selected item to the cart. The button "Clear Last Item" removes the last item from the cart. The button "Clear Cart / Sales" clears the whole cart or removes the invoice of a confirmed sales. Do note that these buttons can only be pressed if certain conditions are met. For example, pressing "Confirm Sales" if nothing is added to the cart or if the sales is already confirmed should result in some warning message. Or pressing "Add to Cart" when quantity is 0 or no item is selected should also result in some warning message. (6 marks) (3 marks) (1 mark) [Total: 10 marks] Question 1 You will be creating an invoicing system for the sales of PC parts. The file "Assignment_Q1.xlsm" contains the worksheets "Sales Point", "Items Database", and "Voucher Database". The worksheet "Items Database" contains information on all the items that are for sale. The worksheet "Voucher Database" contains information on all the vouchers that are available to be applied to a sale and the conditions to use those vouchers. Note that only ONE voucher can be applied to a sale. The worksheet "Sales Point" will be the location for the invoice that will be generated for each sale. An example of the worksheet "Sales Point' is shown in Figure 1. Item Type Item Name Item Code Unit Price Quantity Memory Add to Cart Clear Last Item Clear Cart / Sales Add Voucher MEMOS Confirm Sales Cart Date: No Item Code Figure 1 Item Name Quantity Unit Price Total Figure 2 shows an example of having an item added to the cart. Note that the cells with yellow fill are the cells where the user may change values. You can use dropdown lists for the "Item Type" cell, "Item Name" cell, and "Add Voucher" cell. Note that the voucher MEM05 is active. Item Type Item Name Item Code Unit Price Quantity Memory Sandisk CZ50 16 GB S001 $17.00 Add to Cart Clear Last Item Clear Cart / Sales Add Voucher MEMOS Confirm Sales Cart Date: No 02/08/2021 Item Code 1 5001 Item Name Sandisk CZ50 16 GB Figure 2 Quantity Unit Price $16.15 1 Total $16.15 Figure 3 shows an example of a complete invoice that is generated after the button "Confirm Sales" is pressed. Note that the grand total, discounted grand total, GST calculated at 6% of the discounted grand total, and the final grand total which is the sum of the discounted grand total and the GST must be calculated and shown in the invoice. Notice that the invoice also has the date of the day. Item Type Item Name Item Code Unit Price Quantity Motherboard AMD Ryzen 5600X 3.7 GHz PO04 $1,368.00 Add to Cart Clear Last Item Clear Cart / Sales Add Voucher ALL10 Confirm Sales 2 Cart Date: No 02/08/2021 Item Code 1 5001 2 G001 3 P004 Item Name Sandisk CZ50 16 GB Palit RTX3060 Dual 12 GB AMD Ryzen 5600X 3.7 GHz Quantity Unit Price $17.00 $2,898.00 $1,368.00 Grand Total: Discounted Grand Total: GST 6%: Final Grand Total: $5,391.05 Marks will be given according to the following guideline: Correct calculations and information are shown in the invoice. Pressing buttons randomly do not give illogical results. User-friendly and visually pleasing. Total $17.00 $2,898.00 $2,736.00 $5,651.00 $5,085.90 $305.15 1 1 2 Figure 3 The button "Add to Cart" adds the selected item to the cart. The button "Clear Last Item" removes the last item from the cart. The button "Clear Cart / Sales" clears the whole cart or removes the invoice of a confirmed sales. Do note that these buttons can only be pressed if certain conditions are met. For example, pressing "Confirm Sales" if nothing is added to the cart or if the sales is already confirmed should result in some warning message. Or pressing "Add to Cart" when quantity is 0 or no item is selected should also result in some warning message. (6 marks) (3 marks) (1 mark) [Total: 10 marks]

Answer rating: 100% (QA)

The images show an assignment question and figures associated with creating an invoicing system for ... View the full answer