--- title: "Q1 P3" space: "Student Handbook" url: "https://portal.cs17.org/student-handbook/assignments/q1-p3" updated: "2026-06-16" --- ***The reward for good work is more work*** 😃 Jaggu Uncle is back. He was very happy with your work on the inventory management worksheet you setup for Jugaad Hardware and have some more requirements. Last time (Q1-P2) you helped him get his stock under control. This time they are digitizing their sales tracking and analytics. :::tip[What is Analytics?] Read more about what "Analytics" is [here](https://en.wikipedia.org/wiki/Analytics). ::: ## The Worksheet Jugaad Hardware has handed you six months of sales records (October 2025 – March 2026), which you can download by clicking [here](https://portal.cs17.org/files/jugaad_sales.ods). It contains the following sheets: 1. `Item_Master`: Same as the last problem set (P2), contains a list of their products. 2. `Sales`: Contains all the sales. Each row is one sale. 3. `Sales_Analytics`: Will contain the analytics you will carry out based on the requirements. 4. `Charts`: Blank for now, will have the colourful charts you will add. ## Sales Analytics Use your lookup and `SUMIFS` skills to fill the following columns in the `Sales_Analytics` sheet: 1. Item Name, Category, Selling Price: simple lookup 2. Units sold: total number of units sold for **this** item 3. [Revenue](https://en.wikipedia.org/wiki/Revenue): the total money made from the sale of **this** item ## Charts :::tip[Charts in LibreOffice Calc] Please refer to [this tutorial video](https://youtu.be/YskcMMEfxZs?si=uI1hKcHVHT87x219) on YouTube for a revision on charts in LibreOffice Calc. Also, refer your class notes. ::: This task involves building some useful charts, but before that, some calculations (summary tables) need to be in place. Open up the `Charts` sheet, you will see three empty tables: ![](/files/CleanShot 2026-06-09 at 18.15.11@2x.png) *Sheet: Charts* ### Revenue by Category This chart will help Jugaad Hardware to visually see the distribution of [revenue](https://en.wikipedia.org/wiki/Revenue) coming from each of the item categories. Manually fill the 10 unique category names in **column A** (`A6:A15`), you can use the **Category** column in the Items list sheet to figure out the same. To find the revenue `B6:B15`, you will have to use `SUMIFS` to get the revenue for items belonging to this particular category (you have calculated the revenue column in the last section), then use auto-fill for rest of the rows. Now that we have the table, let's create a chart! Actually, two charts using this same data! First up, we have to create a **Pie Chart** which will show the share of revenue that each of the category brings. Add a title to the chart: "Revenue by Category" and also change the "Legend" position to **Top**. ![](/files/CleanShot 2026-06-09 at 22.34.12@2x.png) But we also need % labels, here is how you can add the same: 1. Be in the Chart edit mode (if not sure, click outside the chart and then double click the chart) 2. Right click on the Pie and select **Insert Data Labels**... 3. Step 2 will enter actual revenue, to get percentage instead, right click the Pie again and select **Format Data Labels...** 4. Go to the **Data Labels** menu option 5. Uncheck "**Value as number"** and check "**Value as percentage"** 6. Click "**OK**" If you followed the steps properly, the chart should look like this: ![](/files/pie.png) The next chart you have to create is a **Column (vertical bar) chart** which will display the revenue per category in descending order, i.e. it will help Jaggu Uncle to see which are the top selling categories. Start by selecting the data and sorting (Data > Sort) it by **Revenue** column in descending order. :::caution[Note] While sorting, make sure you select only the table data and not the headers! ::: Then select the data (along with the column headers) and insert a column chart with the title **Top Selling Categories**. Customise the color of the bars to something else as per your liking. Here is how it should look: ![](/files/column.png) ### Monthly Revenue Now we want to create a chart that shows revenue over past months. Start by adding the following rows in the **Month** column of the **Monthly Revenue** table: ![](/files/imaged2cdbb.png) Now we need to fetch the revenue (total sales) for each of these months. But how do we do that? We have a `Date` column in the `Sales` sheet but not in the `MMM, YYYY` format like above, which is needed if we want to do `SUMIFS` of the sales. We will need to first add a new column to `Sales` sheet which will use the `Date` column to extract the `Month`. You can use this formula to do it: ``` =TEXT(B4,"MMM YYYY") ``` Then go back to the `Charts` sheet and add the revenue for the **Oct 2025** row. Once that is done, you can auto-fill to the other months. Then you can use this table (data) to create a new column chart which should look like this: ![](/files/re-trend.png) *The title and bar color should match (pick any green)* :::tip Notice how in order to create charts, we first have to *derive* some **summary table** using the sheets we have, then use that table to create charts. ::: ### LARGE **function** Consider the below sample data: ![](/files/image3927a5.png) I want to get the largest (highest) value from the column B (`B2:B5`). For this I can use a new function named `LARGE` : ``` =LARGE(B2:B5, 1) # highest =LARGE(B2:B5, 2) # 2nd highest ``` The above will give you **18** (highest) and **10** (second highest) respectively. With this information, you are ready to tackle the final chart of this assignment. ### Top 10 Items by Revenue The last chart we need to create will help Jugaad Hardware to quickly view the top 10 selling items (by revenue). Go to the `Charts` sheet and let's start filling in the **Top 10 Items by Revenue** table. Your first task is to use the `LARGE` function get the highest revenue of any item from the `Sales Analytics` sheet (in cell `I6`). Then `I7` will contain the second highest revenue, `I8` 3rd highest, and so only till 10th highest. Sorry, you won't be able to use auto-fill here :) Once you have the revenue column filled in, you can *lookup* the item name (**Column H**) based on the Revenue. Now it is just a matter of adding a **Bar Chart**. Select the data (10 rows + header row) and insert a bar chart. Set the title to "**Top 10 Items by Revenue**". Here is how the chart will look like: ![](/files/top-10-rev.png) But there is a problem here, it is in reverse order of what we want, because Calc will plot the last row first. To fix this, just sort the items in **ascending** order of **Revenue** instead, which should fix the chart. ## Slides Jugaad Hardware has asked you to create a presentation for their different products using LibreOffice Impress. The presentation will be based on the item categories and items you found on the `Item_Master` sheet. Here are the mandatory requirements for the slideshow: 1. It should have at least 15 slides 2. It should have relevant images and icons 3. Use proper alignment and layout 4. Proper logos if using company Feel free to use your own creativity on top of this and also images. You can use websites like [https://unsplash.com/](https://unsplash.com/) to download high quality images for your slideshow. ### Submission Save your spreadsheet file as `sales_analytics_by_` and your slideshow as `presentation_by_` where name is your first name. Compress both the files to ZIP and then submit the assignment via this link.