LibreOffice Calc Practicals

Step 1: Prepare the Data

1. Create a new sheet named Jan:
   • Click the + icon at the bottom sheet tab
   • Right-click the new sheet and rename to "Jan"

2. Enter sales data with columns: Items, Quantity, Amount

3. Create another sheet named Feb with similar data

Step 2: Create a Consolidated Sheet

1. Insert a new sheet and rename it Consolidated
2. Click in cell A1 (where combined data will appear)

Step 3: Open the Consolidate Dialog

1. Go to menu: Data → Consolidate...
2. The Consolidate dialog box will open

Step 4: Add Source Ranges

1. Click inside the Source data range box

2. Go to the Jan sheet → select the data range (e.g., B8:D11)

3. Click Add button

4. Go to the Feb sheet → select data range (e.g., B5:D8)
5. Click Add again

Step 5: Configure Settings

1. Under Function, select Sum

2. Verify "Copy results to" points to Consolidated sheet

3. Click OK

Step 1: Open and Save Spreadsheet

1. Open LibreOffice Calc

2. Save as Macro_Function.ods

3. Enter sample data in the sheet

Step 2: Enable Macro Recording

1. Go to Tools → Options → LibreOffice → Advanced
2. Check "Enable macro recording (limited)"
3. Click OK and restart LibreOffice Calc

Step 3: Record the Macro

1. Select Tools → Macros → Record Macro

2. A small floating recorder window will appear

3. Perform formatting actions:
   • Select cells A1:B1
   • Make them Bold
   • Apply background color
   • Center align the text
4. Click Stop Recording

Step 4: Save the Macro

1. In the dialog, choose settings and click Save

Step 5: Run the Macro

1. Go to Tools → Macros → Run Macro
2. Navigate to your macro and click Run

Grading Scale

• ≥75% → A1
• 60-74.99% → A2
• 45-59.99% → B1
• 33-44.99% → B2
• <33% → Fail

Formula 1: Total Marks (Column G)

In cell G2:

Drag down through G11:

Formula 2: Percentage (Column H)

In cell H2:

Formula 3: Grade (Column I)

In cell I2 (nested IF):

Formula 4: Rank (Column J)

In cell J2:

Formula 5: Remark (Column K)

In cell K2 (to flag if any subject < 33):

Step 1: Enter Sales Data

Step 2: Sort the Data

1. Select your data range

2. Go to Data → Sort

3. Choose Category as Sort Key and click OK

Step 3: Apply Subtotals

1. Select the data range again

2. Go to Data → Subtotals

3. Configure: Group by Category

4. Calculate subtotals for Sales, function Sum

5. Click OK

Problem Setup

Compare three scenarios for product profit:

• Normal Case: Current CP and SP
• Best Case: SP +10%, CP -5%
• Worst Case: SP -10%, CP +5%

Step 1: Setup Data and Formulas

Create table with Product, CP, SP, Quantity, Profit

Profit formula in E2:

Fill down to E4:

Total Profit in E5:

Step 2: Create Normal Case Scenario

1. Select range B2:C4 (CP and SP cells that will change)

2. Go to Tools → Scenarios...
3. Click Add
4. Name: Normal Case
5. Description: Base data
6. Click OK to save current values

Step 3: Create Best Case Scenario

1. Click Add again in the Scenarios dialog
2. Name: Best Case
3. Enter the Best Case values:
   • Pen: CP = 4.75 (5 × 0.95), SP = 11 (10 × 1.10)
   • Notebook: CP = 28.5 (30 × 0.95), SP = 55 (50 × 1.10)
   • Eraser: CP = 1.9 (2 × 0.95), SP = 4.4 (4 × 1.10)
4. Click OK

Step 4: Create Worst Case Scenario

1. Click Add once more
2. Name: Worst Case
3. Enter the Worst Case values:
   • Pen: CP = 5.25 (5 × 1.05), SP = 9 (10 × 0.90)
   • Notebook: CP = 31.5 (30 × 1.05), SP = 45 (50 × 0.90)
   • Eraser: CP = 2.1 (2 × 1.05), SP = 3.6 (4 × 0.90)
4. Click OK

Step 5: Switch Between Scenarios

1. To apply a scenario, simply double-click its name in the Scenarios dialog
2. Watch how the Total Profit in cell E5 changes:
   • Normal Case: Original profit values
   • Best Case: Higher profit (lower costs, higher prices)
   • Worst Case: Lower profit (higher costs, lower prices)