--- title: "Q1 P2" space: "Student Handbook" url: "https://portal.cs17.org/student-handbook/assignments/q1-p2" updated: "2026-06-04" --- ## Introduction In this assignments, you will get to practice what you have learned about LibreOffice calc, and also learn a few new things. Let's get started! ## Jugaad Hardware & Tools Jugaad Hardware & Tools is a hardware shop in your locality. Now that you have learned LibreOffice Calc, you have decided to [digitize](https://dictionary.cambridge.org/dictionary/english/digitize) their inventory management, which has been done manually till now. As they have grown a lot over the past year, manually managing this data has become tedious and also a lot of calculations have to be done manually and repeatedly as new items get delivered and existing items are sold (stock movement or transactions). CS17 student to the rescue! :::note[Inventory Management] In simple terms, Inventory management is the task of tracking stock (how many do we have?) of items in a shop or business. You can read more [here](https://en.wikipedia.org/wiki/Inventory_management_(business)). ::: Basic data entry has already been done in a spreadsheet file. Start by downloading that file using [this link](https://portal.cs17.org/files/jugaad_inventory.ods). ## The Sheets Open the downloaded file. It has a total of 5 sheets: 1. **Item_Master**: A list of all the different items they buy/sell. 2. **Suppliers**: These are the entities/businesses from which *Jugaad* *Hardware* purchases or procures these items. 3. **Stock_Transactions**: Every stock movement that happens. For example, they sell 5 *fevicol* boxes, the transaction will be of type "**OUT**" meaning stock moved out of their store. 4. **Current_Stock**: This sheet is mostly empty and will contain the stock levels of different items and related columns/fields. 5. **Dashboard**: Will contain an overview of their inventory with basic summary. ## Basics None of the sheets have any header/title yet. Your first task is to add nicely formatted headers. Let's start with the `Item_Master` sheet: - Add 2 new rows at the top of the **Item_Master** sheet - Merge the cells: C1, C2, D1, D2 - Add the title: "Items: Jugaad Hardware & Tools" - Format as below: - Black background - Font should be white and 16pt - Center Aligned **both** horizontally and vertically **Hint for centring vertically** ![](/files/image.png) Now do the same for other sheets too. Replace "Items" with relevant name: "Dashboard", "Supplier", "Current Stock", and "Stock Transactions". ## Current Stock Sheet This sheet already has the `Item ID` column, look up and fill these columns from `Item_Master` sheet: - Item Name - Category - Opening Stock - Unit Cost - Reorder Level ### SUMIF We have already learned about `COUNTIF`, which counts the number of rows where a particular condition is satisfied or TRUE. There is another similar function named `SUMIF` which will add (sum) the values instead of counting them. Here is an example: ![](/files/CleanShot 2026-05-27 at 00.06.09@2x.png) - First you give the range in which you want to apply the condition: `A1:A6` - Second is the condition, in this case it is which cells are equal to `"Apple"` - Third is which column / range to sum / add, in this case we want to add the values in the B column, hence `B1:B6` Once you hit enter, you will see the answer comes out to be 10, which is correct. It adds `B1` and `B6` (rows where value of `A` column is `"Apple"`). SUM adds everything in the range, but SUMIF gives you power to add rows based on some condition of a particular column. ### SUMIFS & COUNTIFS Both `COUNTIF` and `SUMIF` take a single condition, but there are variants of this function which can take multiple conditions and only return TRUE when all the conditions are TRUE, here are examples: ``` =COUNTIFS(A1:A6, "Apple", B1:B6, ">5") ``` Here we are applying two conditions, one on column `A` value should be equal to `"Apple"` **AND** column `B` value should be greater than 5. Similarly, there is `SUMIFS` : ``` =SUMIFS(B1:B6, A1:A6, "Apple", C1:C6, "In Stock") ``` The above will add the values of column `B`, where value of column `A` is "Apple" **and** values of column `C` is "In Stock". Notice the difference, in `SUMIF` you had the condition first, here we have the range to sum first. You can add as many range-condition pairs here: ``` =COUNTIFS(range1, condition1, range2, condition2, range3, condition3, ....) =SUMIFS(range_to_sum, range1, condition1, range2, condition2, ...) ``` ### Current Stock Phase 2 Now that you know about `SUMIFS`, use your new found knowledge to implement the below columns in the `Current_Stock` sheet: - Total Purchased — sum of `Quantity` where `Type` = "*IN*" for this **Item ID** - Total Sold, Damaged Qty, Returned Qty — same pattern as Total Purchased over types *OUT*, *DAMAGE*, *RETURN*. - Current Stock — compute as Opening Stock + Total Purchased − Total Sold − Damaged Qty + Returned Qty - Stock Value — Current Stock × Unit Cost :::caution[Save Changes!] Don't forget to **save** your file frequently to prevent data loss ::: ### IF & IFS The `IF` function let's you take a conditional and output a value if the expression evaluates to TRUE and another value if it evaluates to FALSE. Here is the example usage: ``` =IF(10>12, "yes", "nope") # will show "nope" in the cell =IF(15>12, "yes", "nope") # will show "yes" in the cell =IF(TRUE, "yes", "nope") # will show "yes" always =IF(FALSE, "yes", "nope") # will show "nope" always =IF(D2="OUT", "Sale", "Purchase") # if cell D2 is equal to OUT, Sales else Purchase ``` Just like `SUMIFS` and `COUNTIFS` allow you to add multiple conditions, `IFS` also lets us do the same: ``` =IFS(A2>=80, "A", A2>=60, "B", A2>=40, "C") ``` In the above example, the cell will show: - A if `A2`>=80 - B if `A2`>=60 - C if `A2`>=40 - `#N/A` if no condition matches To tackle the case when no condition matches, we can add an always TRUE condition: ``` =IFS(A2>=80, "A", A2>=60, "B", A2>=40, "C", TRUE, "Fail") ``` Now the cell will show **Fail** if none of the first three condition match. ### Application of IFS Cool, enough with new functions now, let's use them to calculate **Stock Status** column in the `Current_Stock` sheet. Stock Status should be shown as follows: - **"Out of Stock"** if Current Stock ≤ 0 - **"Reorder"** if Current Stock ≤ Reorder Level - **"OK"** otherwise Use IFS just like we did in last example of the previous section. ### Conditional Formatting Formatting cells based on certain conditions on the values of the cell makes it easier to makes sense of a long list of data. A user can easily peek and see what cells to pay attention to. Format the **Stock Status** column to be of style: 1. Error if it is "Out of Stock" 2. Warning if it is "Reorder" 3. Good if it is "OK" After seeing the above formatting, Jaggu Uncle of Jugaad Hardware has requested you to add a **Data Bar conditional formatting** to the **Stock Value** column, so he can easily see which items are taking the most capital (money). ## Dashboard A dashboard is a **single screen** that shows you the **most important numbers** about your business at a glance. These numbers generally are statistics like total, average, etc. Dashboards also commonly have nice **charts**. BTW quick tip before we proceed for the tasks, you can use the merge cells button from the toolbar to quickly select and merge required cells: ![](/files/CleanShot 2026-05-27 at 16.30.45@2x.png) Add the following values (USE FORMULAs for NUMBERS!) to the dashboard in the cells and styles mentioned below: 1. "Total Items" (B4) - Style with **Accent 3** - The value will be the count of all items in the `Item_Master` sheet (hint: use `COUNTA`) - B5 and B6 will be merged for this - Style with **Heading 1** - Align the value centre horizontally and vertically 2. "Total Stock Value" - Style with **Accent 3**, should be in cells D4 and E4 - The value will be the sum of all stock value in `Current_Stock` sheet - Cells D5:E6 will be merged for this - Align the value centre horizontally and vertically - Style with **Heading 1** - Format as INR currency 3. "Items to reorder" - Style with **Accent 3,** should be in B10 - The value will be in B11 and is the count of items that are needed to be re-ordered - Style to be used is **Neutral** 4. "Out of stock" - Style with Accent 3, should be in C10 - The value will be in C11 and is the count of items that are out of stock - Style to be used is **Error** Here is how the completed dashboard sheet should look like: ![](/files/CleanShot 2026-05-27 at 16.47.00@2x.png) ## Submission Rename the file to `_jugaad_inventory.odt` and send it to `hussain@cs17.org`.