PRACT

No. 0
Prof. Martn B
TICAS
05
Becerra
 Reference Example Description

The column and the row can

Related A1 change at the moment of 14 15
copy the formula.

Neither the column nor the row can

Absolute $A$1 15 15
change.

The column does not change,

Mixed $A1 only the row can 16 24
change.

The line does not change, only

Mixed A$1 the column may change. 18 15
10
0

14
0

20
0

14
0
 Formulas
Formulas are the core of a spreadsheet in Excel. Formulas do the work
They can use simple calculation formulas, such as addition, subtraction, multiplication, and division.
complex such as financial, statistical, scientific calculations. It can also be
comparisons and text manipulation.

Operators

They are used to indicate to the formulas which operation should be performed. Excel uses four types of operators.

Arithmetic Operators
Operator Formula Result Type of operation
+ =5+2 7 Sum
- 3 Remaining
=5-2
* 10 10 Multiplication
/ 2.5 Division
=5/2
% 0.05 Percentage
5%
^ 25 Exponent
25
Text Operators
Operator Formula Result Tipo de operación
& Hello Luis Hello Luis Concanetation
& =A12&" "&A13 Hola Luis Concanetation

Comparison Operators
Operator Type
= Just like
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
<> Different

Reference Operators
Operator Type
: Range Rango
, Union Union

FORMULAS

When a formula is entered in Excel, it appears in the formula bar, while

result. The formula input must begin with the sign (=) followed by cons
using values, operators, cell references, functions, and names to calculate the re
To enter a formula, follow these steps:
1.- Select the cell that will contain the formula.
2.- Write the sign =
3.- Write the formula
4.- Press [Enter]
below it means the calculations. It
vision as well as formulas
in using formulas to make

ores

ADVANCE hello gabo

day of summer

that in the cell the is displayed

build the formula step by step
desired result.
1st 2nd
hello gabo hello gabo
summer days
COMPARISON OPERATORS

=D4 = E4 0 250 580 0

D5 is greater than E5 1 550 130 1
D6 is less than E6 0 490 380 0
=D7 >= E7 1 590 180 1
=D8 <= E8 0 590 480 0
D9 is not equal to E9 1 250 560 1

=D12 = E12 0 43696 43648 0

=D13 > E13 1 43489 43474 1
=D14 < E14 0 43830 43546 0
D15 is greater than or equal to E15 0 43713 43750 0
=D16 <= E16 0 43714 43692 0
D17 is not equal to E17 1 43830 43532 1

=D20 = E20 0 April October 0

=D21 > E21 1 July February 1
=D22 < E22 0 January August 0
D23 is greater than or equal to E23 1 Luis Doris 1
=D24 <= E24 FALSE H G 0
D25 is not equal to E25 0 May May 1
Calculate percentages with a single cell

Find 30% of 200 60

Find 20% of 1000 20

Find 35% of 350 122.5

Find 20% of 460

92

Find 10% of 5600 560

Hallar 18% de 1200 216

1st form 2nd form sum percentage

60 60 260

#NAME? #NAME? #NAME?

decrease percentage

140

#NAME?
 Quantity Product Prec-UnitSub-Total
10 Rice/Kg S/. 3.00 30.00
6 Noodles/Kg S/ 3.50 21.00
12 Sugar/Kg S/. 2.50 30.00
8 Flour/Kg S/. 2.80 22.40
1 Butter/Can S/. 3.20 3.20
9Oil/Bottle S/. 3.70 33.30
T-Gross 106.60
IGV 19.19
T-Neto 125.79

IGV = 18% of the Gross Total

Exercises
En la Bodega el "Cabezón" se adquieren los siguiente productos :

SALE RECEIPT
No.1111-11111
Grocery Distributor for Wholesale and Retail

Mr./Ms. Fecha :

CODIGO CANTIDAD DESCRIPCION PRE-UNI Total

A01 12 Milk jars 2.5 S/ 30.00
A02 3 Sugar bags 1 kg. 1.5 $4.50
A03 4 1 Lt Oil Bottles 3.5 $14.00
A04 6 Paper Rolls H. 0.8 S/ 4.80
A05 2 Legia Bottles 2 S/ 4.00
A06 1 Focus 3 S/ 3.00
A07 6 1 kg noodle bags. 2.5 $15.00
A08 8 Kitchen salt bags 0.5 $4.00
Subtotal 75.30
IGV 18% 13.55
Net Total 0.00

to add 88.854
IGV = 18% OF THE SUBTOTAL stay 61.746

30
 4.5
14
4.8
4
3
15
4
79.3
14.274
93.574
CODIGO DESCRIPCION PRICE CANTIDAD TOTAL
P-01 SILK TIE/. 60.00 5 $300.00
P-02 PANTS $120.00 2 S/. 240.00
P-03 SHOES $220.00 4 $880.00
P-04 SHIRT $60.00 6 S/. 360.00
P-05 SACO S/. 290.00 2 $580.00
CODE PRODUCTO PRE-UNIT QUANTITY TOTAL
H-01 CANDLES $1.50 14 S/ 21.00
H-02 MATCHBOX $0.50 5 S/ 2.50
H-03 FOCUS S/. 2.30 3 S/ 6.90
H-04 BUTTER JAR $3.20 2 $6.40
H-05 MILK BOX $1.80 15 S/ 27.00
H-06 TEA BOX $0.60 6 S/ 3.60
H-07 BAG OF NOODLES $1.80 20 S/ 36.00
H-08 BOTTLE OF OIL $3.50 6 $21.00
H-09 SACK OF SALT S/. 0.80 3 S/ 2.40
H-10 SODA BOTTLE $2.50 4 S/ 10.00
SUB-TOTAL S/ 126.80
IGV 18% S/ 4.82
T-PAY S/ 131.62
 EXCHANGE RATES
DOLLAR S/ 3.60
LIBRA S/ 5.40

PRICE IN PRICE IN PRICE IN

CODE DESCRIPTION
SOLES DOLARES LIBRAS
P-01 SILK TIE $60.00 $17 £11.11
P-02 PANTALON S/. 120.00 $22 £22.22
P-03 SHOES S/. 220.00 61.11 £40.74
P-04 SHIRT $60.00 $17 £11.11
P-05 BAG $290.00 $81 £53.70

ABSOLUTE REFERENCES
 VAT FACTOR 18%

CODE PRODUCT PRE-UNIT CANTIDAD SUB-TOTAL IGV TOTAL

H-01 WICKS $1.50 14 21.00 0.27 21.27
H-02 MATCHBOX $0.50 5 2.50 0.09 2.59
H-03 FOCUS $2.30 3 6.90 0.41 7.31
H-04 BUTTER TUB $3.20 2 6.40 0.58 6.98
H-05 milk carton S/. 1.80 15 27.00 2.70 29.70
H-06 TEA BOX $0.60 6 3.60 0.30 3.90
H-07 BAG OF NOODLES S/. 1.80 20 36.00 4.14 40.14
H-08 BOTTLE OF OIL $3.50 6 21.00 11.20 32.20
H-09 SALT BAG $0.80 3 2.40 1.44 3.84
H-10 SODA BOTTLE $2.50 4 10.00 1.50 11.50
TOTAL TO PAY 159.43
 LIST OF RETAIL PRICES
PRICE PRICE
PRODUCT PROFIT
DE COSTO DE VENTA
MOUSE $5.00 $0.50 $ 5.50
KEYBOARDS S/ 8.00 S/ 0.80 S/ 8.80
SCREEN S/ 120.00 S/ 12.00 S/ 132.00
PADS $2.00 S/ 0.20 S/ 2.20
HARD DRIVES S/ 160.00 S/ 16.00 S/ 176.00
MICROPROC. $320.00 S/ 32.00 S/ 352.00
VIDEO CARD S/ 25.00 $2.50 $27.50
CASE S/ 32.00 S/ 3.20 $35.20
DRIVE S/ 20.00 S/ 2.00 $22.00

10% PROFIT ON COST PRICE

CIBERTRONIC Computing Institute Microsoft Excel for Windows

DAILY INCOME
ARTICLE S-T TOTAL OF ING S/228.00
MOUSE 60.00
pencil 15.00
PILLOWS 48.00
NOTEBOOK 90.00
WINEMAKER 15.00

DAILY SALES TABLE

Pencil Mouse VINIFILE NOTEBOOK MARKERS
QUANTITY 4 15 8 20 10
PRICE 15.00 1.00 6.00 4.50 1.50
 DESCRIPCION PRECIO. CANT SUBTOTAL IGV DESC
RICE BAG 105 3 315 56.7 252
OIL BOX 48 2 96 17.28 76.8
SUGAR BAG 68 4 272 48.96 217.6
MILK CARTON 81 5 405 72.9 324
BAG OF NOODLES 25 6 150 27 120
SALT BAG 25 1 25 4.5 20
TOTAL A PAGAR -->

SUBTOTAL=PRICE * QUANTITY
IGV=18% * SUBTOTAL
DISCOUNT=20% * SUBTOTAL
NET = (SUBTOTAL + VAT) - DISCOUNT
NETO
-132.30
-40.32
-114.24
-170.10
-63.00
-10.50
-530.46
,

DATE COST STOCK

CODE PRODUCTO EXPIRATION UNITARY INITIAL
101 CHICHARRONES 1/15/1998 18.25 315.00
102 TOR TEES 10/10/1996 20.35 102.00
103 TICO TICO 4/20/1996 17.35 189.00
104 Cheez-Its 4/15/1997 21.45 175.00
105 Picarones 11/12/1998 19.35 101.00
106 Fried habits 6/2/1996 16.52 130.00
107 Spicy Shrimp 12/20/1996 17.54 210.00
108 Spicy Tor Tees 12/20/1995 22.22 215.00
109 MANI SALTED 5/20/1996 16.98 302.00
110 CHEESE BALLS 9/30/1996 15.45 250.00
111 SWEET MANI 4/23/1998 23.55 289.00
TOTAL OF
TOTAL OF E
TOTAL UTIL
 INCOME PRODUC. STOCK TOTAL IN
PRODUC. SOLD FINAL CASH
100.00 280.00
123.00 120.00
320.00 300.00
126.00 150.00
145.00 205.00
123.00 150.00
206.00 200.00
245.00 400.00
263.00 500.00
105.00 210.00
350.00 600.00
AND INCOME FOR THE MONTH
MONTHLY EXPENSES
MONTHLY LIABILITIES
 PROJECTION OF BENEFITS 1,993 - 1,997
INCOME 1993 1994 1995 1996 1997
Sodas 150,000 163683 210916 220741 233750
SODAS
MINERALS
TOTAL INGRESOS
EXPENSES 1993 1994 1995 1996 1997
RAW MATERIAL 15600 4581 1589 3256 1566
LABOR 23600 5689 1563 2654 1589
GENERAL EXPENSES 1562 1635 1568 2569 1566
TOTAL EXPENSES
NET INCOME

DATA TO CALCULATE INCOME

Soda beverages 1993 1994 1995 1996 1997

UNITS 40000 42515 52078 53578 55000
PRICE 3.75 3.85 4.05 4.12 4.25
SOFT DRINKS 1993 1994 1995 1996 1997
UNITS 45000 48090 51000 49500 52589
PRICE 3.80 3.90 4.10 4.15 4.25
MINERALS 1993 1994 1995 1996 1997
UNITS 52000 53567 56087 62078 63589
PRICE 1.65 2.75 1.85 1.90 2.15
TOTALS

TOTALS
 PRICE LIST

CODE PRODUCTO P-COST P-SALE

P01 Pliers 68
P02 HAMMER 45
P03 RAKE 74
P04 LAMP 24
P05 SCREWDRIVER 15
P06 SIERRA 50
P07 FLASHLIGHT 23

LIST OF PARTS

CODE PRODUCT P-COST P-SALE

MICROPROCESSOR
A01 PENTIUM INTEL III
190
A02 MOTHERBOARD TOMATO 120
A03 128 MB SDRAM MEMORY 84
FIREBALL HARD DRIVE
A04 6.4 GB (GIBA BYTES)
250
A05 COOLER PENTIUM III 12
A06 CASE ATX 55
A07 DRIVE 1.44 MB BRAND NET 25
3D SOUND CARD
A08 CREATY LABSCARD
64 BITS
35
FAX MODEM
A09 64 KPS
35
A10 MICROSOFT MOUSE 45
17" GLSI MONITOR
A11 MARCA SAMSUNG
230
A12 48X READER BRAND HACER 45
 PRICE LIST

CODE PRODUCTO P-COSTO GANANCIA P-VENTA

P01 Pliers 68
P02 HAMMER 45
P03 RAKE 74
P04 LAMP 24
P05 Screwdriver 15
P06 SIERRA 50
P07 FLASHLIGHT 23

LIST OF PARTS

CODE PRODUCTO P-COST PROFIT P-SALE

MICROPROCESSOR
A01
PENTIUM INTEL III 190
A02 MOTHERBOARD TOMATO 120
A03 128 MB SDRAM MEMORY 84
FIREBALL HARD DRIVE
A04
6.4 GB (GIBA BYTES) 250
A05 COOLER PENTIUM III 12
A06 CASE ATX 55
A07 DRIVE 1.44 MB BRAND NET 25
3D SOUND CARD
A08
CREATY LABSCARD
64 BITS 35
FAX MODEM
A09
64 KPS 35
A10 MICROSOFT MOUSE 45
17" GLSI MONITOR
A11
MARCA SAMSUNG 230
A12 48X READER BRAND HACER 45
No.__________