Assignment 2
A dataset containing sales data for a company is
provided. A spreadsheet to be create that calculates
monthly sales totals, identifies top-selling products, and
visualizes sales trends using line charts or bar graphs.
Use conditional formatting to highlight exceptional sales
performances.
Steps:-
Step 1: Data Structuring
The raw data is organize into a tabular format with the
following core input columns:
Month: The time period of the sale.
Product: The specific item sold (A, B, or C).
Units Sold: The quantity of items sold.
Unit Price (₹): The cost per individual item.
Step 2: Calculating Individual Sales
The "Sales (₹)" column was create by applying a
multiplication formula to the input columns for every
row:
Sales=Unit Sold*Unit Price
For example, in January for Product A:
120 units*250 price=30,000
Step 3: Calculating Monthly Totals
Appling the formula=SUMIF(A2:A19,A2,E2:E2)
for cell F2
Then drag this formula
Step 4: Conditional Formatting
Select F2:F19 range
Then click home and go option conditional
formatting
Then select color Scales
Then select one of these
Month Product Units Sold Unit Price(₹) Sales(₹) Monthly Total(₹)
Jan Product A 120 250 30000 64000
Jan Product B 80 300 24000 64000
Jan Product C 50 200 10000 64000
Feb Product A 100 250 25000 58000
Feb Product B 70 300 21000 58000
Feb Product C 60 200 12000 58000
Mar Product A 150 250 37500 72500
Mar Product B 90 300 27000 72500
Mar Product C 40 200 8000 72500
Apr Product A 130 250 32500 61500
Apr Product B 60 300 18000 61500
Apr Product C 55 200 11000 61500
May Product A 140 250 35000 76500
May Product B 95 300 28500 76500
May Product C 65 200 13000 76500
Jun Product A 160 250 40000 85000
Jun Product B 100 300 30000 85000
Jun Product C 75 200 15000 85000
Step 5: Line Chart
Select the whole table
Then go to insert option
Then go to recommended charts option & click all
charts
Then click line charts
Select one and click ok
Then click axis titles option
Then change the chart title to Monthly Sales Trend
and change the axis title to Sales(₹)
Monthly Sales Trend
Sales(₹)
200000
0
Product C
Product C
Product C
Product C
Product C
Product C
Product A
Product A
Product A
Product A
Product A
Product A
Product B
Product B
Product B
Product B
Product B
Product B
Axis
Ja Ja Ja Fe Fe Fe M M Title
M Ap Ap Ap M M M Ju Ju Ju
n n n b b b ar ar ar r r r ay ay ay n n n
Units Sold Unit Price(₹)
Sales(₹) Monthly Total(₹)
Step 6: Column Charts
Select the whole table
Then go to insert option
Then go to recommended charts option & click all
charts
Then click column charts
Select one and click ok
Then click axis titles option
Then change the chart title to Sales by product and
change the axis title to Sales(₹)
Sales by product
150000
Sales(₹)
100000
50000
0
Ja
n
Ja
n
Fe
b ar ar Ap
r ay ay Ju
n
M M M M
Axis Title
Units Sold Unit Price(₹)
Sales(₹) Monthly Total(₹)