Scribd
Upload
155 views2 pages

ABC Bakery Q1 Sales Report Guide

The document provides instructions for a finance consultant to build a spreadsheet for ABC Bakery's first quarter 2022 sales report. It involves entering sales data for 10 products, formatting the table, calculating totals and other metrics, using conditional formatting and VLOOKUP functions, and creating a 3D pie chart to visualize total sales by product. The consultant is asked to save the file with their name and student ID and email it to their lecturer once completed.

Uploaded by

Muhd zafri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
155 views2 pages

ABC Bakery Q1 Sales Report Guide

The document provides instructions for a finance consultant to build a spreadsheet for ABC Bakery's first quarter 2022 sales report. It involves entering sales data for 10 products, formatting the table, calculating totals and other metrics, using conditional formatting and VLOOKUP functions, and creating a 3D pie chart to visualize total sales by product. The consultant is asked to save the file with their name and student ID and email it to their lecturer once completed.

Uploaded by

Muhd zafri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

ASM452 : End User Applications – Assignment 2

Instructions:
As a Finance Consultant, you have been asked to build a spreadsheet for a company, ABC Bakery Sdn Bhd for
its first quarter sales report. Perform the following tasks to build the First Quarter 2022 Sales Report Spreadsheet
for ABC Bakery 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 Arial Font size 12. Start your data entry at Cell B3.

Question 2: Table Style [10 marks]


i. Insert the table title (ABC BAKERY SDN BHD) at B1 and table subtitle (Sales Report 1 st Quarter 2022) at
B2. Insert a new row between the subtitle row and the table.
ii. Merge and center the table title, B1-F1. Change the font to Arial 18 pt, purple font color, bold the title.
iii. Merge and center the table subtitle, B2-F2. Change the font to Arial 14 pt, purple font color, italic the
subtitle.

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
ASM452 : End User Applications – Assignment 2

Question 4: Using Formulas and Functions [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, Arial Narrow 16 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.
b. Insert the chart title as “Total Sales” and change the font to Arial 24 pt, purple color
c. Separate the Raindrop Cake slice from the chart by 10%.
d. Change the font of data labels to Arial 14 pt.
e. Insert “percentage” at Slice label.
ii. Save the document.
iii. Email the document to your lecturer. ( akademik1a370@[Link] )

You might also like