As a Finance Consultant, you have been asked to build a worksheet for a company, AZ Sweet Sdn Bhd

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Instructions:

As a Finance Consultant, you have been asked to build a worksheet for a company, AZ Sweet Sdn Bhd for its first quarter sales report. Perform the following tasks to build the First Quarter 2023 Sales Report Worksheet for AZ Sweet Sdn Bhd.

Question 1: Data Entry [10 marks]

i. Start Microsoft Excel and create a new workbook and save as GROUP, YOUR FULL NAME & STUDENT MATRIC NUMBER.

ii. Enter the below data by using Cambria Font size 12. Start your data entry at Cell B3.

 

 

 

Question 2: Table Style [10 marks]

i. Create any appropriate table layout.

ii. Insert the table title at B1 and table subtitle at B2. Insert a new row between the subtitle row and the table.

iii. Merge and center the table title, B1-F1. Change the cell style to Title Style and change the font to Cambria 18 pt, light green font color, bold the title.

iv. Merge and center the table subtitle, B2-F2. Change the cell style to Title Style and change the font to Cambria14 pt, light green font color, italic the subtitle.

v. Change the table style to Gold, Table Style Medium 12.

 

Question 3: Computation [10 marks]

i. Insert another column, name it as “Total Sales (RM)”. Calculate the total sales for each product.

ii. Sort “Name of Product” by A-Z.

iii. For the “Price per unit (RM)”, “Total Sale (unit)” &"Total Sales (RM)", calculate/find the following:

a. The Total/Sum

b. The Maximum score

c. The Minimum score

d. The Average score

 

Question 4: Using Formulas and Function [10 marks]

i. In the “Total Sales” column, by using conditional formatting, create a new formatting rule by using icon sets as the format style. Choose 3 symbols (circled) as the icon style. The rules of the value are as follow:

a. Green check symbol when value more or equal than 5600

b. Yellow exclamation symbol when value more or equal than 3600

c. Red cross symbol when value less than 3600

ii. Insert another column and name it as “Sales Rating”.

iii. Based on the table below, create a table array in the range of I1:J6. Type “Rating Table” in cell I1 and apply Title style, bold text, Cambria16 pt. Merge and center the cells, I1:J1. Copy the format of the table style to the column headings.

 

 

iv. Using the VLOOKUP function, determine the sales rating for all products.

 

 

Question 5: Charts/ Graph [10 marks]

i. Based on the 10 products and the total sales (RM),

a. Create a 3-D Pie Chart in a new sheet and rename the sheet as “Chart”. Change the tab sheet colour to red.

b. Change the chart layout to Layout 1 and change the style to Style 8.

c. Insert the chart title as “Total Sales” and change the font to Arial Black28 pt, light green color

d. Rotate the raindrop cake slice to 260 degrees and separate the slice from the chart by 10%.

e. Change the font of data labels to Lucida Fax12 pt

ii. The “Chart” sheet will be rearranged to be the second sheet. Rename the first sheet as “Sales Report” and change the sheet tab color to yellow.

iii. Save the document.

 

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme