mel cima excel
TRANSCRIPT
-
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
-
7/31/2019 Mel Cima Excel
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
-
7/31/2019 Mel Cima Excel
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
-
7/31/2019 Mel Cima Excel
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%
-
7/31/2019 Mel Cima Excel
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
-
7/31/2019 Mel Cima Excel
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/31/2019 Mel Cima Excel
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
-
7/31/2019 Mel Cima Excel
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%
-
7/31/2019 Mel Cima Excel
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
-
7/31/2019 Mel Cima Excel
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
-
7/31/2019 Mel Cima Excel
11/28
16
8530
single linear equation
0.6 0.8 1 1.2
6 8 10 12
y=3x+20
y=3x+20
-
7/31/2019 Mel Cima Excel
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
-
7/31/2019 Mel Cima Excel
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
-
7/31/2019 Mel Cima Excel
14/28
-0.94557
1277
MOVEMENT EACH YEAR
-35000060000
95000
120000
180000
200000 1.007422105
Y5
2%
-
7/31/2019 Mel Cima Excel
15/28
5MTHS
11MTHS
20 40 60 80 100 120 140
Series1
-
7/31/2019 Mel Cima Excel
16/28
Series1
5 10 15 20x axis-sales persons
Series1
Linear (Series1)
5 10 15 20
Axis Title
Series1
Linear (Series1)
-
7/31/2019 Mel Cima Excel
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))
-
7/31/2019 Mel Cima Excel
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
-
7/31/2019 Mel Cima Excel
19/28
pyramid shape
eu europe north america australia
Series1
Series2
eu europe north
america
australia
Series1
Series2
-
7/31/2019 Mel Cima Excel
20/28
-
7/31/2019 Mel Cima Excel
21/28
-
7/31/2019 Mel Cima Excel
22/28
-
7/31/2019 Mel Cima Excel
23/28
less peeked.
-
7/31/2019 Mel Cima Excel
24/28
-
7/31/2019 Mel Cima Excel
25/28
-
7/31/2019 Mel Cima Excel
26/28
-
7/31/2019 Mel Cima Excel
27/28
rend occuring every yr frm the first quarter to the last quarter.
-
7/31/2019 Mel Cima Excel
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