Part B : Subject Specific Skills
CHAPTER 2
INFORMATION TECHNOLOGY ( 402 )
L-1
OPEN OFFICE CALC
An electronic spreadsheet is a tool for analyzing, sharing, and managing information
for accounting, mathematical calculations, budgeting, and billing. It consists of rows
and columns, similar to an accounting ledger.
Spreadsheet programs, like Lotus 1-2-3, Quattro-Pro, MS-Excel, VisiCalc, and
OpenOffice Calc, provide features for data analysis, graph creation, and picture
insertion.
OpenOffice Calc is user-friendly and caters to both beginners and professionals. It
offers features not found in Excel, including automatic series definition for graphing
and the ability to save spreadsheets as PDF files. Calc can handle up to 65,536 rows,
1,024 columns per sheet, and a maximum of 256 sheets.
Analyze Data Using Scenarios and Goal Seek
CONSOLIDATING DATA
Data Consolidation allows you to gather data from multiple worksheets into a single
master worksheet. Follow these steps:
1. Open the worksheet with the data to be consolidated.
2. Select the Consolidate option under the Data menu.
3. In the Consolidate dialog box, select your source data ranges. If the ranges are not
named, type a reference or select the range using your mouse.
4. Click Add for each selected range.
5. Specify the target range for the results by selecting it in the Copy results to box.
6. Choose a function from the Function list, with Sum as the default.
Analyze Data Using Scenarios and Goal Seek
7. Optionally, click More for additional settings:
Select Link to source data to generate formulas that update with changes.
Choose to consolidate by Row labels or Column labels if matches exist in the data.
8. Click OK to complete the consolidation.
9. For consistent ranges, consider naming them using Data → Define Range.
Your consolidated data will be saved when you save the worksheet, and it will be
available when you reopen it.
Creating Subtotals
The SUBTOTAL function, found in the Mathematical category of the Function Wizard
(Insert → Function), allows you to add subtotals to data arranged in an array with
labeled columns and rows. You can access it through the Data menu.
To insert subtotal values, follow these steps:
1. Ensure columns have labels.
2. Select the range for subtotal calculation and choose DATA → SUBTOTALS. The
Subtotals dialog box will appear.
3. In the "Group by" box, select the column for subtotals. Changes will auto-
recalculate.
Creating Subtotals
4. In "Calculate subtotals for," select the columns with values to subtotal.
5. In "Use function," choose the desired function.
6. Click OK.
For multiple groups, you can organize subtotals by accessing the Options page for
sorting (ascending or descending) through the Tools tab and selecting Options.
What -if Analysis
What-if analysis examines how uncertainties in a model's inputs affect its outputs,
helping to assess the model's robustness. It identifies potential errors through
unexpected input-output relationships.
OpenOffice Calc offers tools for what-if analysis, including:
(i) Scenarios
(ii) Goal Seek
(iii) Solver
Using Scenarios
Scenarios are tools for testing "what-if" questions, each with a unique name that can
be edited separately. Only the active scenario's content is printed from the
spreadsheet. A scenario saves specific cell values, allowing you to switch between
them using the Navigator or a drop-down list. For example, you can create scenarios
for different interest rates on an investment. When a scenario is opened, formulas
relying on its values are updated, enabling the efficient creation of complex income
models.
Creating Scenarios
To create a scenario, follow these steps:
1. Select the cells with changing values (hold Ctrl for multiple selections).
2. Go to Tools → Scenarios. The Create Scenario dialog will appear.
3. Enter a name for the new scenario, which will be displayed in the Navigator.
4. Optionally, add a comment for the scenario.
5. Adjust options in the Settings section if needed.
6. Click OK to activate the new scenario. You can create multiple scenarios for the
same range of cells.
Using Goal Seek
Goal seeking is a process used to find the input value needed to achieve a known
output. It can be utilized in software like OpenOffice Calc.
To use Goal Seek:
1. Select the cell with the formula for your desired result.
2. Go to Tools → Goal Seek to open the dialog box.
3. The formula cell will be pre-filled.
4. Click in the Variable cell field and select the cell to change.
5. Enter your desired result in the Target value.
6. Click OK, and if successful, confirm to enter the result in the variable cell.
Using the Solver
Solver is an advanced version of Goal Seek that handles multiple variables instead of
just one. It helps you minimize or maximize results based on rules you set, allowing
you to define whether values should be greater than, less than, or equal to certain
thresholds. For adjustable arguments, you can set limits and specify if variables should
be integers or binary (0 or 1). After setting up your rules, click the Solve button to see
results.
To use Solver in OpenOffice Calc:
1. Create a table.
2. Choose Tools > Solver.
3. In the Solver dialog, enter your target value, optimization criteria, and limiting
conditions.
4. Press OK.
Question
[QUESTION] Name any two spreadsheet software.
[QUESTION] By which tab, you can use Function wizard?
[QUESTION] What is the use of Goal Seek?
[QUESTION] How many rows and columns are there in Calc?