ITE3008 Information Technology Essentials - Business (Mini Project) Part 2: Microsoft Excel 2016 or Office 365 Excel (20% of Total Module Marks) Background to the problems Hong Kong Science Museum sells its souvenirs through the physical gift shop inside the Museum and the online shop on Web. Assume you are the IT specialist of Hong Kong Science Museum, the shop manager asked you for help to use an Excel file to record the sales occurred at the two channels and analyse the sales data to improve the business. You are also asked to modify the Excel file to reduce input errors and increase work efficiency. " O O O Here are the requirements from your shop manager: The sales officer will input the "Ordering Date, Product Code, "Quantity", " Sold Location" and "No. of Coupons Used" in the Excel file (i.e. the columns in light yellow color). The information on the light-green columns will be calculated from these input data by using suitable Excel formula and functions. You are required to use suitable Excel features to avoid data error in all the data input columns. For example, but not limited to: Add data validation on the columns.e.g. Limit the ordering date to a date on or before today's date Provide suitable input message to remind the officer Provide a list for the officer to select the product code Alert the officer on wrong data Additional requirements for you to prepare the Excel file: Customer can use a maximum of 5 coupons each time, each coupon has a fixed value of HK$100, but the total coupon value cannot be greater than the total balance after discount. If the customer is buying in the gift shop, a 10% discount will be granted. Cash coupons (if any) are used after the discount. There are only three categories of product: Decorative Items, Stationery, and Scientific Toys. You may find the price of the products from a table in "Categories" worksheet in the given Excel file "Class-YourName-Spreadsheet.xlsx. Highlight the figure if the remaining balance exceeds or equal to $500. o O o o o o After modifying the spreadsheet in accordance with the above requirements, you can input the data from the given data file "Part2-DataFile.xlsx" to the Sales Record file. Rename the given Excel file "Class-YourName-Spreadsheet.xlsx" with your personal particulars, e.g."BA114052-1-ChanTaiMan-Spreadsheet.xlsx". AY2021-22 (S2) Page 4 of 6 ITE3008 Information Technology Essentials - Business (Mini Project) Besides modifying the spreadsheet, the shop manager also asks you to help him to analyze the sales using suitable Excel tools. Therefore, you are going to analyze the data in the worksheet Sales using Pivot Table and Pivot Chart. Analysis 1 The manager wants to know the total sales of each month. By using a chart, illustrate the relationship between the Total sales amount and "Ordering Date" then group "Ordering Date" by month (include the example row record in your analysis). Summarize your findings in one short paragraph with the aid of graphical tools. The analysis should be saved in a new worksheet named "Analysisl". Analysis 2 The manager wants to know the number of pieces of product sold of each category by the Gift Shop and Web (include the example row record in your analysis). Summarize your findings in one short paragraph with the aid of graphical tools. The analysis should be saved in a new worksheet named "Analysis2". . . It is required that the completed workbook must contain the following four worksheets: The first worksheet (named "Sales") records the sales details The second worksheet (named "Categories") shows the detail of each category The third worksheet (named "Analysis1") shows the analyzed results The fourth worksheet (named "Analysis2") shows the analyzed results Mark Allocation The first worksheet (named "Sales") The third and fourth worksheet (named "Analysisl" and "Anaylsis2") 10% 10% Marking Criteria (Relative weighting for this part): [25%] Correct use of formula and conditional formatting as stated in the requirements above. [25%] Appropriate feature to avoid input error. [20%] Effective use of analysis tools and clear presentation of findings - Analysis 1. [30%] Effective use of analysis tools and clear presentation of findings - Analysis 2. ITE3008 Information Technology Essentials - Business (Mini Project) Part 2: Microsoft Excel 2016 or Office 365 Excel (20% of Total Module Marks) Background to the problems Hong Kong Science Museum sells its souvenirs through the physical gift shop inside the Museum and the online shop on Web. Assume you are the IT specialist of Hong Kong Science Museum, the shop manager asked you for help to use an Excel file to record the sales occurred at the two channels and analyse the sales data to improve the business. You are also asked to modify the Excel file to reduce input errors and increase work efficiency. " O O O Here are the requirements from your shop manager: The sales officer will input the "Ordering Date, Product Code, "Quantity", " Sold Location" and "No. of Coupons Used" in the Excel file (i.e. the columns in light yellow color). The information on the light-green columns will be calculated from these input data by using suitable Excel formula and functions. You are required to use suitable Excel features to avoid data error in all the data input columns. For example, but not limited to: Add data validation on the columns.e.g. Limit the ordering date to a date on or before today's date Provide suitable input message to remind the officer Provide a list for the officer to select the product code Alert the officer on wrong data Additional requirements for you to prepare the Excel file: Customer can use a maximum of 5 coupons each time, each coupon has a fixed value of HK$100, but the total coupon value cannot be greater than the total balance after discount. If the customer is buying in the gift shop, a 10% discount will be granted. Cash coupons (if any) are used after the discount. There are only three categories of product: Decorative Items, Stationery, and Scientific Toys. You may find the price of the products from a table in "Categories" worksheet in the given Excel file "Class-YourName-Spreadsheet.xlsx. Highlight the figure if the remaining balance exceeds or equal to $500. o O o o o o After modifying the spreadsheet in accordance with the above requirements, you can input the data from the given data file "Part2-DataFile.xlsx" to the Sales Record file. Rename the given Excel file "Class-YourName-Spreadsheet.xlsx" with your personal particulars, e.g."BA114052-1-ChanTaiMan-Spreadsheet.xlsx". AY2021-22 (S2) Page 4 of 6 ITE3008 Information Technology Essentials - Business (Mini Project) Besides modifying the spreadsheet, the shop manager also asks you to help him to analyze the sales using suitable Excel tools. Therefore, you are going to analyze the data in the worksheet Sales using Pivot Table and Pivot Chart. Analysis 1 The manager wants to know the total sales of each month. By using a chart, illustrate the relationship between the Total sales amount and "Ordering Date" then group "Ordering Date" by month (include the example row record in your analysis). Summarize your findings in one short paragraph with the aid of graphical tools. The analysis should be saved in a new worksheet named "Analysisl". Analysis 2 The manager wants to know the number of pieces of product sold of each category by the Gift Shop and Web (include the example row record in your analysis). Summarize your findings in one short paragraph with the aid of graphical tools. The analysis should be saved in a new worksheet named "Analysis2". . . It is required that the completed workbook must contain the following four worksheets: The first worksheet (named "Sales") records the sales details The second worksheet (named "Categories") shows the detail of each category The third worksheet (named "Analysis1") shows the analyzed results The fourth worksheet (named "Analysis2") shows the analyzed results Mark Allocation The first worksheet (named "Sales") The third and fourth worksheet (named "Analysisl" and "Anaylsis2") 10% 10% Marking Criteria (Relative weighting for this part): [25%] Correct use of formula and conditional formatting as stated in the requirements above. [25%] Appropriate feature to avoid input error. [20%] Effective use of analysis tools and clear presentation of findings - Analysis 1. [30%] Effective use of analysis tools and clear presentation of findings - Analysis 2