CHECK LIST
1 TEXT FORMATTING
2 NUMBER FORMATTING
3 CONDITIONAL FORMATTING
4 DATA VALIDATIONS
5 GOAL SEEK
6 DATA TABLES
7 SCENARIO MANAGER
8 GRAPHS
9 SOLVER
10 GRAPHICAL TOOLS
11 VBA
12 PIVOT TABLES
13 DOCUMENTATION
14 SECURITY
input
sale 200000
cost of good sold 50% MODEL
opp exp 10% SALE 200000 1 INPUT 3 OUTPUT
tax rate 30% CGS 100000
#of share 10000 GP 100000
OPP EXP 20000
EBIT 80000
TAX RATE 24000
OUTPUT NI 56000
GP 100000 EPS 5.6
NI 56000
EPS 5.6
140000 130000
120000
120000 110000
100000 100000
2 INPUT 1 OUTPUT
100000 90000
77000 80000
80000 70000 70000
63000 60000
56000 56000 GP
60000 49000 50000
42000 NI
40000 35000
28000 EPS
21000
20000
5.6 7.7 7 6.3 5.6 4.9 4.2 3.5 2.8 2.1
0
1 2 3 4 5 6 7 8 9 10
GP 1E+ 1E+ 1E+ 1E+ 1E+ 900 800 700 600 500
NI 560 770 700 630 560 490 420 350 280 210
EPS 5.6 7.7 7 6.3 5.6 4.9 4.2 3.5 2.8 2.1 39.0%
DATA TABLE
GP NI EPS
100000 56000 5.6
35% 130000 77000 7.7
40% 120000 70000 7
45% 110000 63000 6.3
50% 100000 56000 5.6
55% 90000 49000 4.9
60% 80000 42000 4.2
65% 70000 35000 3.5
70% 60000 28000 2.8
75% 50000 21000 2.1
5.6 30% 35% 40% 45% 50% 55% 60%
15% 7.7 7 6.3 5.6 4.9 4.2 3.5
17.0% 7.42 6.72 6.02 5.32 4.62 3.92 3.22
19.0% 7.14 6.44 5.74 5.04 4.34 3.64 2.94
21.0% 6.86 6.16 5.46 4.76 4.06 3.36 2.66
23.0% 6.58 5.88 5.18 4.48 3.78 3.08 2.38
25.0% 6.3 5.6 4.9 4.2 3.5 2.8 2.1
27.0% 6.02 5.32 4.62 3.92 3.22 2.52 1.82
29.0% 5.74 5.04 4.34 3.64 2.94 2.24 1.54
31.0% 5.46 4.76 4.06 3.36 2.66 1.96 1.26
33.0% 5.18 4.48 3.78 3.08 2.38 1.68 0.98
35.0% 4.9 4.2 3.5 2.8 2.1 1.4 0.7
37.0% 4.62 3.92 3.22 2.52 1.82 1.12 0.42
39.0% 4.34 3.64 2.94 2.24 1.54 0.84 0.14
65% 70%
2.8 2.1
2.52 1.82
2.24 1.54
1.96 1.26
1.68 0.98
1.4 0.7
1.12 0.42
0.84 0.14
0.56 -0.14
0.28 -0.42
0 -0.7
-0.28 -0.98
-0.56 -1.26
Scenario Summary
Current Values: BEST SCENARIO
Changing Cells:
SALE 120000 200000
CGS 60% 50%
OPP_EXP 18% 10%
Result Cells:
GP 48000 100000
NI 18480 56000
EPS 1.848 5.6
PREPARED BY :
NOMAN AKHTER
WORST SCENARIO AVERAGE SCENARIO
20000 120000
35% 60%
13% 18%
13000 48000
7280 18480
0.728 1.848
PRODUCT NORTH EAST SOUTH WEST
A 10000 1222 20000 3000
B 5666 333343 34567 67566
C 87777 56779 44444 121112
PRODUCT GRAP
350000
300000
250000
87777
200000
150000
100000 5666
50000 10000 1222
0
NORTH EAST
A 10000 1222
B 5666 333343
C 87777 56779
PRODUCT GRAPH
333343
121112
56779 44444
67566
34567 C
20000 B
1222 3000
A
EAST SOUTH WEST
1222 20000 3000
333343 34567 67566
56779 44444 121112
QUESTION NO # 1
BASE SALE 100000 PROJECT I/S FOR BASE + 5 YEARS
COST OF SALE 60% APPLY TEXT AND NUMBER FORMATING
SALE INCREASE 10% DATA VALIDATIONS ON INPUT
GEN&ADMIN EXP 15% CONDITIONAL FORMATING
SALE&MKT 10% GOAL SEE TARGET NI=14500, SALE+ ?
TAX RATE 30% 1 DATA TABLE OF 1 INPUT & 2 OUTPUT
# OF SHARES 100000% 3 SCENARIOS OF YOUR CHOICE
2 GRAPH OF YOUR CHOICE
INPUT MODEL
BASE SALE 100000
COST OF SALE 60%
SALE INCREASE 10%
GEN&ADMIN EXP 15%
SALE&MKT 10%
TAX RATE 30%
#OF SHARES 10000
OUTPUT
BASE YEAR 1YEAR 2YEAR
GP 40000 44000 48400
NI 10500 11550 12705
EPS 1.05 1.16 1.27
DATA TABLE ( 1INPUT 3OUTPUT)
COST OF SALE
DATA TABLE (2 INPUT 1 OUTPUT)
COST OF SALE
1 data table of 2input and 1 out put
BASE YEAR 1 2 3 4
SALE 100000 110000 121000 133100 146410
COST OF SALE 60000 66000 72600 79860 87846
GROSS PROFIT 40000 44000 48400 53240 58564
GEN&ADMIN EXP 15000 16500 18150 19965 21962
SALES&MKT 10000 11000 12100 13310 14641
EBIT 15000 16500 18150 19965 21962
TAX RATE 4500 4950 5445 5990 6588
NI 10500 11550 12705 13976 15373
EPS 1.05 1.16 1.27 1.40 1.54
3YEAR 4YEAR 5YEAR
53240 58564 64420
13976 15373 16910
1.40 1.54 1.69
1 INPUT COST OF SALE & 3 OUTPUT GP, NI, EPS
GP NI EPS
40000 10500 1.05
35% 65000 28000 2.8 100000
40% 60000 24500 2.45
50000
45% 55000 21000 2.1
0
50% 50000 17500 1.75
35%
40%
55% 45000 14000 1.4 -50000
60% 40000 10500 1.05
65% 35000 7000 0.7
70% 30000 3500 0.35
75% 25000 0 0 35% 40%
80% 20000 -3500 -0.35 GP 40000 6500 6000
85% 15000 -7000 -0.7 NI 10500 2800 2450
90% 10000 -10500 -1.05 EPS 1.05 2.8 2.45
IF CGS INCREASE BY 60% AND THE TAX RATE CHANGE SO IMPACT ON EPS
2 INPUT 1 OUTPUT
1.05 10% 20% 30% 40% 50%
35% 3.6 3.2 2.8 2.4 2
40% 3.15 2.8 2.45 2.1 1.75
45% 2.7 2.4 2.1 1.8 1.5
50% 2.25 2 1.75 1.5 1.25
55% 1.8 1.6 1.4 1.2 1
60% 1.35 1.2 1.05 0.9 0.75
65% 0.9 0.8 0.7 0.6 0.5
70% 0.45 0.4 0.35 0.3 0.25
75% 0 0 0 0 0
5
161051
96631
64420
24158
16105
24158
7247
16910
1.69
GRAPH
40%
GP 40000
45%
50%
55%
60%
65%
70%
NI 10500
75%
80%
85%
90%
EPS 1.05
40% 45% 50% 55% 60% 65% 70% 75% 80% 85% 90%
6000 5500 5000 4500 4000 3500 3000 2500 2000 1500 1000
2450 2100 1750 1400 1050 7000 3500 0 -350 -700 -105
2.45 2.1 1.75 1.4 1.05 0.7 0.35 0 -0.4 -0.7 -1.1
4
3.5
TAX RATE
3
2.5
2
1.5
1 Series1
0.5
Series2
01
Series3
3 Series4
Series9
Series8
Series7
Series6 Series5
5 Series5
Series4
Series3 Series6
Series2
Series1
1 2 3 4 5 Series7
Series1 3.6 3.2 2.8 2.4 2 Series8
Series2 3.15 2.8 2.45 2.1 1.75 Series9
Series3 2.7 2.4 2.1 1.8 1.5
Series4 2.25 2 1.75 1.5 1.25
Series5 1.8 1.6 1.4 1.2 1
Series6 1.35 1.2 1.05 0.9 0.75
Series7 0.9 0.8 0.7 0.6 0.5
Series8 0.45 0.4 0.35 0.3 0.25
Series9 0 0 0 0 0
Series1
Series2
Series3
Series4
Series5
Series6
Series7
Series8
Series9
Scenario Summary
Current Values: BEST SCENARIO
Changing Cells:
SALE_S 50000 200000
COST_SALE 75% 65%
SALE_PERCENTAGE 25% 25%
Result Cells:
GROSS_P 12500 70000
NET_INCOME 0 14000
EPS_PERCENTAGE 0.00 1.40
PREPARED BY:
NOMAN AKHTER
AVERAGE SCENARIO WORST SCENARIO
150000 50000
70% 75%
25% 25%
45000 12500
5250 0
0.53 0.00
Scenario Summary
Current Values: BEST SCENARIO AVERAGE SCENARIO
Changing Cells:
SALE_S 100000 200000 150000
COST_SALE 60% 65% 70%
SALE_PERCENTAGE 10% 25% 25%
Result Cells:
GROSS_P 40000 70000 45000
NET_INCOME 10500 14000 5250
EPS_PERCENTAGE 1.05 1.40 0.53
PREPARED BY:
NOMAN AKHTER
WORST SCENARIO CURRENT SCENARIO
50000 100000
75% 60%
25% 10%
12500 40000
0 10500
0.00 1.05