mel cima excel

7/31/2019 Mel Cima Excel

1/28

5 6 8 53

3 1 2 8

7 2 20 6 4

ROUNDING NUMBERS

89.56 56.4 4.3 76.44

5.9 8.2 3.6 7.1 0.7

8539 349.1 32.548 1 8527.934

89.56 56.4 4.3 76

3 squared 9 9

3 cubed 27 27

3 to the power 4 81 81

4 to the power 4 256 256

square root of 4 2

cubed root of 64 4

y=mx+c

values for x y=3x+20

1 23

2 26

3 29

4 32

5 356 38

7 41

8 44

9 47

10 50

2linear equation

values for x y=3x+20 y=6x+1

1 23 7

2 26 13

0

0.2

0.4

0.6

0.8

1

1.2

0 0.2 0.4

0

10

20

30

40

50

60

0 2 4

60

70


2/28

3 29 19

4 32 25

5 35 31

6 38 37

7 41 43

8 44 49

9 47 55

10 50 61

single quadratic equation

values for x x^2+5x+10

-25 510

-20 310

-15 160

-10 60

-5 10

0 10

5 60

10 160

15 310

20 510

2 quadratic equations

values for x x^2+5x+10 -2x^2-x+100

-25 510 -1125

-20 310 -680

-15 160 -335

-10 60 -90

-5 10 55

0 10 100

5 60 45

10 160 -11015 310 -365

20 510 -720

graph of a hyperbola

0

10

20

30

40

50

0 2 4

-30 -20 -10

-140

-120

-100

-80

-60

-40

-20

20

40

60

-30 -20 -10


3/28

x a n y=a(1/x)+n

1 10 1 11

2 10 1 6

3 10 1 4.333333

4 10 1 3.5

5 10 1 3

6 10 1 2.666667

7 10 1 2.428571

8 10 1 2.25

9 10 1 2.111111

10 10 1 2

11 10 1 1.909091

12 10 1 1.833333

13 10 1 1.769231

14 10 1 1.714286

15 10 1 1.666667

16 10 1 1.625

17 10 1 1.588235

18 10 1 1.555556

19 10 1 1.526316

20 10 1 1.5

21 10 1 1.47619

22 10 1 1.454545

23 10 1 1.434783

24 10 1 1.416667

25 10 1 1.4

26 10 1 1.384615

27 10 1 1.37037

555 852 258 789 254

456 215 225 525 254

254 459 545 636 254

369 254 789 458 658

245 235 569 484 952

458 231 456 254 987

MEAN S 389.5 374.3333333 473.666667 524.3333 559.8333STDEV 123.4224453 251.0614799 210.841805 179.7917 353.9788

COUNT 6 6 6 6 6

STANDA 50.38700229 102.49542 86.0758064 73.39967 144.5112

MED 412.5 244.5 500.5 504.5 456

QUARTI 282.75 232 307.5 464.5 254

QUARTI 457.5 407.75 563 608.25 878.5

INTER-Q 174.75 175.75 255.5 143.75 624.5

SKEW -0.058247766 1.860807501 0.22954641 -0.009372 0.361101

0

2

4

6

8

10

12

0 5 10


4/28

KURTOS -1.528800903 3.222602476 -0.5885666 0.733843 -2.53663

OUTLIE 720 671 946 824 1815

MEAN 464.3333333 464 *

MODE 254

MEDIAN 457

MINIMUM 215

MAX 987

RANGE 772

STDEV 231.9906855 232 *

VARIANCE 53819.67816 53820 *

PART 4. NO. OF EMAILS RECEIVED BY DAY FOR EACH MONTH

MON TUE WED THU FRI

JAN 570 539 580 563 497

FEB 520 480 510 500 490

MAR 562 588 502 516 540

APR 568 516 550 562 556

MAY 555 562 548 548 546

JUN 549 576 560 498 554

JUL 562 553 575 539 531

AUG 586 567 509 529 587SEP 596 577 574 555 580

OCT 569 550 557 558 563

NOV 562 519 569 530 560

DEC 567 553 524 501 550

TOTAL 6766 6580 6558 6399 6554

MEAN 563.8333333 548.3333333 546.5 533.25 546.1667

STDEV 18.7026169 30.75514246 28.1699388 24.79415 29.07618

CAPITAL INVESTMENT APPRAISAL SYSTEM

CASH OUT CASH IN NET CASH

IT INVSTMNT CASH OUT 350000NET BENEFITS YR 1 60000

YR 2 95000

YR 3 120000

YR 4 180000

YR 5 200000

FIXED COST OF CAPITAL/INT RATE 20.00% 0.2

Y1 Y2 Y3 Y4

ESTIMATED INFLATION RATES 3% 4% 4% 3%


5/28

INVESTMENT REPORTS FOR PROPOSED INVESTMENT

PAYBK IN YRS & MTHS 3YRS

DISCOUNTD PYBK FDR IN YRS 4YRS

RATE OF RETURN (%) 0.374285714 37.4285714 37.43

NPV AT A FIXED DISCOUNT RATE 2597.736626 2598

PROFITABILITY INDEX(PI) 1.007422105 1.01

INTERNAL RATE OF RETURN (IRR) 20% 20%

VARIABLE DISCOUNT RATES

NPV VARIABLE DISCOUNT RATES (VDR)

y x

200 30

400 7

VDR WORK AREA

YR NO 1 2 3 4 5

CASH FL 60000 95000 120000 180000 200000

DISCOU 48780 76613 97561 146341 162602

2 77236

3

4

5

SUM OF PRESENT VALUES

OUTLET FLOOR SPC L MA 75 22.4 30.7

B 60 21.1 12.9

C 108 29.6 47.1

D 94 27.1 38.8

E 92 27 41.5

F 130 36.9 79

G

0

5

10

15

20

25

30

35

40

0

70

80

90


6/28

x y

11 236

12 234

18 298

15 250

13 24610 202

y=115.05+10.12x

region x y least sqaure line

a 11 236 222.3968872

b 12 234 232.5214008

c 18 298 293.2684825

d 15 250 262.8949416e 13 246 242.6459144

f 10 202 212.2723735

least squared line

0

10

20

30

40

50

60

0 20 40 60 80 100 120 140

0

50

100

150

200

250

300

350

0

yaxis-sales(units)

0

50

100

150

200

250

300

350

0

AxisTit

le


7/28

TIME SERIES GRAPH

TIME(x) PERIOD SALES '000 (y)

1 42

2 41

3 52

4 39

5 45

6 48

7 61

8 46

9 5210 51

11 60

12 46

78 583

a. TIME PERIOD(x) SALES '000(y) xy x^2

1993 Q1 1 24.8 24.8 1

Q2 2 36.3 72.6 4

Q3 3 38.1 114.3 9

Q4 4 47.5 190 16

1994 Q1 5 31.2 156 25

Q2 6 42 252 36Q3 7 43.4 303.8 49

Q4 8 55.9 447.2 64

1995 Q1 9 40 360 81

Q2 10 48.8 488 100

Q3 11 54 594 121

Q4 12 69.1 829.2 144

1996 Q1 13 54.7 711.1 169

Q2 14 57.8 809.2 196

Q3 15 60.3 904.5 225

Q4 16 68.9 1102.4 256

136 772.8 7359.1 1496every year the sales are increasing every quarter and decreasing the beginning of the f

y x

200 30

400 70

0

10

20

30

40

50

60

70

0

sales'000

SALES

250

300

350

400

450


8/28

linear graph

uk 59.3

eu 61.6

europe 10.3

north america 15.8

australia 9.9

settees 34

armchairs 27

dining sets 38

shelving 18

others 12

uk 59.3 60.2

eu 61.6 69

europe 10.3 11.1

0

50

100

150

200

0

europ

7%

north ame

10%

australia

6%


9/28

north america 15.8 18

australia 9.9 8.8

stacked bar chart

horizontal bar chart

PARETO ANALYSIS- 80-20 RULE

PRODUCT LINE NO OF ITEMS UNIT SALES PARETO

F 3 1720

U 20 1513

L 6 1234

0

10

20

30

40

50

60

70

80

uk

0

20

40

60

80

100

120

140

uk eu europe north

america

australia

Series2

Series1

0

10

20

30

40

50

60

70

uk

0 20 40 60 80

uk

eu

europe

north america

australia

Series2

Series1


10/28

T 17 1011

I 4 891

D 2 656

A 1 598

R 12 521

G 3 484

J 4 309

B 1 267

O 9 102

Q 11 70

N 7 55

K 5 32

S 13 18

P 9 10

H 3 5

M 6 4

E 2 2

C 1 1

TOTAL 9503


11/28

16

8530

single linear equation

0.6 0.8 1 1.2

6 8 10 12

y=3x+20

y=3x+20


12/28

6 8 10 12

y=3x+20

y=6x+1

0

100

200

300

400

500

600

0 10 20 30

x^2+5x+10

x^2+5x+10

0

0

0

0

0

0

0

0

0

0

0

0 10 20 30

x^2+5x+10

-2x^2-x+100


13/28

221

777

363

652

981

477 477

578.5279.7712

6 SKEWNESS should be between -1 and +1 otherwise not symmetric

114.2161 KURTOSIS should be between -1 and +1 otherwise if >1 more peeked than the normal and if


14/28

-0.94557

1277

MOVEMENT EACH YEAR

-35000060000

95000

120000

180000

200000 1.007422105

Y5

2%


15/28

5MTHS

11MTHS

20 40 60 80 100 120 140

Series1


16/28

Series1

5 10 15 20x axis-sales persons

Series1

Linear (Series1)

5 10 15 20

Axis Title

Series1

Linear (Series1)


17/28

scatter diagram

ollowing yr meaning there is seasonal component occuring every beginning of the yr and there is the linear t

5 10 15

t(quarters)

SALES '000 (y)

Linear (SALES '000 (y))

0

10

20

30

40

50

60

70

80

0 5 10 15 20

T(QUARTER)

SALES '000(y)

Linear (SALES '000(y))


18/28

pie chart

pie chart

multiple bar chart

50 100 150 200 250

Series1

uk

38%

eu

39%

e

rica

Chart Title

settees

armchairs

dining sets

shelving

others

Chart Title


19/28

pyramid shape

eu europe north america australia

Series1

Series2

eu europe north

america

australia

Series1

Series2


20/28


21/28


22/28


23/28

less peeked.


24/28


25/28


26/28


27/28

rend occuring every yr frm the first quarter to the last quarter.


28/28

VDR WORK AREA

YR NO 1 2 3 4 5

CASH FLOW 60000 95000 120000 180000 200000

DISCOUNTED 1 PERIOD 48780 76613 96774 146341 163934

2 62287 61785 78044 118977

3 50231 49826 629384 40509 40182

5 32669

SUM OF PRESENT VALUES

mel cima excel

Documents