Applied Productivity Tools
with Advance Application
Techniques
Empowerment Technologies || Quarter 1: Module 2
ELECTRONIC SPREADSHEET
Electronic spreadsheet refers to a collection of text and
numbers laid out in a rectangular grid. It is an
application program commonly used for budgeting,
inventory management, decision making, forecasting
and other finance-related tasks.
ELECTRONIC SPREADSHEET
•It replaces the traditional financial modeling tools, the
accountant’s columnar pad, pencil and calculator. It a
spreadsheet program, data and formulas used to calculate
those data are entered into ledge-like forms (Spreadsheets or
Worksheets) for analysis, tracking, planning, or “what-if”
evaluations of the impact of real or proposed changes on an
economic strategy.
Microsoft Excel is a helpful and
powerful program for data analysis and
documentation.
It is a spreadsheet program, which
contains a number of columns and
rows, where each intersection of a
column and a row is a “cell.”
By organizing the information in this
way, you can make information easier
to find, and automatically draw
information from changing data.
BASIC ARITHMETIC OPERATIONS
FORMULA – is an equation that performs operation on worksheet
data. A formula in Microsoft Excel always begins with an
equal sign (=).
FUNCTIONS
These are predefined formula that performs calculations using specific
values in a particular order.
The parts of a function are shown below:
=SUM (A1:A20)
Equal Sign
Argument
Function Name
FUNCTION LIBRARY
ABSOLUTE REFERENCE
These are cell references that do not change when copied
or filled.
You can use an absolute reference to keep a row and/or
column constant.
These reference cells can be made constant by inserting $
sign in between or before the row and column.
The column and the row do not change when
$A$2
copied.
A$2 The row does not change when copied.
$A2 The column does not change when copied.
IF FORMULA
Perform a logical test and return one value for a TRUE result, and
another for FALSE result.
More than one condition can be tested by nesting IF functions.
The IF function can be combined with logical functions like AND
and OR.
Syntax: =IF(logical_test,[value_if_true],[value_if_false])
logical_test is a value or logical expression that can be evaluated as TRUE or
FALSE.
value_if_true (optional) is the value to return when logical_test evaluates to
TRUE.
value_if_false (optional) is the value to return when logical_test evaluates to
FALSE.
VLOOKUP
• The VLOOKUP function performs a vertical lookup by searching for a value in
the first column of a table and returning the value in the same row in the
index_number position.
•The VLOOKUP function is a built-in function in Excel that is categorized as a
Lookup/Reference Function. It can be used as a worksheet function in Excel.
As a worksheet function, the VLOOKUP function can be entered as part of a
formula in a cell of a worksheet.
ACTIVITY 1: MICROSOFT EXCEL
Directions: Read and follow the instructions carefully
1. Encode the given data.
2. Apply the text format you desire.
3. Remaining Stock = Beginning Stock minus Purchased
4. Compute the price based on the category
Category Price
Softdrinks 15.00
Juice 18.00
5. Total Purchased Price = Price * Purchased
6. Total Remaining Stock Price = Price * Remaining Stock
7. Save your work as INVENTORY_ Complete name
8. Send it to the link provided.
LETTY’S GROCERY STOCK REPORT
As of December 2021
Total Total Total
Item Beginning Remaining
Category Description Purchased Stock Purchased Remaining
Code Stock Stock
Price Price Stock Price
BEVO1 Softdrinks Coke 25 12
BEV02 Softdrinks Sprite 40 20
BEV03 Softdrinks Royal 30 13
BEV04 Juice Orange 25 10
7
BEV05 Juice Mango 20
