Economy

Excercise1-Student-1.xlsx

Home >Business & Finance homework help >Economics homework help >Economy

Entries

Discount Rate
		For Plan A (RA)	9%		RA - RB
		For Plan B (RB)	8%
Price			2	Thousand dollar/unit
Demand Expectation
		The first year	300	Thousand units
		The second year	600	Thousand units
		The third year	900	Thousand units
Capacity
		The big plant	900	Thousand Units/year
		A small plant	300	Thousand Units/year
Variable Cost
		The big plant	1.28	Thousand dollar/unit
		A Small Plant	1.5	Thousand dollar/unit
Capital expenditure
		The big plant	900	Million dollar
		A small plant	300	Million dollar
Uncertain factor			50%	of the expectation
Salvage value
		For Plan A	0	Million dollar
		For Plan B	300	Million dollar
Difference between NPVA and NPVB
		One-way data table
			NPVA - NPVB										NPVA - NPVB
		RB	0.00									RA	0.00
		10.0%										10.0%
		9.9%										9.9%													0
		9.8%										9.8%
		9.7%										9.7%
		9.6%										9.6%
		9.5%										9.5%
		9.4%										9.4%
		9.3%										9.3%
		9.2%										9.2%
		9.1%										9.1%
		9.0%										9.0%
		8.9%										8.9%
		8.8%										8.8%
		8.7%										8.7%
		8.6%										8.6%
		8.5%										8.5%
		8.4%										8.4%
		8.3%										8.3%
		8.2%										8.2%
		8.1%										8.1%
		8.0%										8.0%
		7.9%										7.9%
		7.8%										7.8%
		7.7%										7.7%
		7.6%										7.6%
		7.5%										7.5%
		7.4%										7.4%
		7.3%										7.3%
		7.2%										7.2%
		7.1%										7.1%
		7.0%										7.0%
		Two-way data table
		Max	0.00
		Min	0.00
		small plant discount rate
plan A discount rate	0.00 Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	9.0%	8.9%	8.8%	8.7%	8.6%	8.5%	8.4%	8.3%	8.2%	8.1%	8.0%	7.9%	7.8%	7.7%	7.6%	7.5%	7.4%	7.3%	7.2%	7.1%	7.0%
	10.0%
	9.9%
	9.8%
	9.7%
	9.6%
	9.5%
	9.4%
	9.3%
	9.2%
	9.1%
	9.0%
	8.9%
	8.8%
	8.7%
	8.6%
	8.5%
	8.4%
	8.3%
	8.2%
	8.1%
	8.0%
		Two-way data table
		Max	0.00
		Min	0.00
		small plant variable cost
BIG plant variable cost	0.00 Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	1.40	1.41	1.42	1.43	1.44	1.45	1.46	1.47	1.48	1.49	1.50	1.51	1.52	1.53	1.54	1.55	1.56	1.57	1.58	1.59	1.60
	1.20
	1.21
	1.22
	1.23
	1.24
	1.25
	1.26
	1.27
	1.28
	1.29
	1.30
	1.31
	1.32
	1.33
	1.34
	1.35
	1.36
	1.37
	1.38
	1.39
	1.40
	1.41
	1.42
	1.43
	1.44
	1.45
		Two-way data table
		Max	0.00
		Min	0.00
		First Year Demand
Second Year Demand	0.00 Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	150	160	170	180	190	200	210	220	230	240	250	260	270	280	290	300	310	320	330	340	350	360	370	380	390	400	410	420	430	440	450
	300
	310
	320
	330
	340
	350
	360
	370
	380
	390
	400
	410
	420
	430
	440
	450
	460
	470
	480
	490
	500
	510
	520
	530
	540
	550
	560
	570
	580
	590
	600
	610
	620
	630
	640
	650
	660
	670
	680
	690
	700
	710
	720
	730
	740
	750
	760
	770
	780
	790
	800
	810
	820
	830
	840
	850
	860
	870
	880
	890
	900
		Two-way data table
		Max	0.00
		Min	0.00
		Third Year Demand
Second Year Demand	0.00 Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	450	480	510	540	570	600	630	660	690	720	750	780	810	840	870	900	930	960	990	1020	1050	1080	1110	1140	1170	1200	1230	1260	1290	1320	1350
	300
	310
	320
	330
	340
	350
	360
	370
	380
	390
	400
	410
	420
	430
	440
	450
	460
	470
	480
	490
	500
	510
	520
	530
	540
	550
	560
	570
	580
	590
	600
	610
	620
	630
	640
	650
	660
	670
	680
	690
	700
	710
	720
	730
	740
	750
	760
	770
	780
	790
	800
	810
	820
	830
	840
	850
	860
	870
	880
	890
	900

Sensitivity on Discount Rate for Plan B

0.1 9.9000000000000005E-2 9.8000000000000004E-2 9.7000000000000003E-2 9.6000000000000002E-2 9.5000000000000001E-2 9.4E-2 9.2999999999999999E-2 9.1999999999999998E-2 9.0999999999999998E-2 0.09 8.8999999999999996E-2 8.7999999999999995E-2 8.6999999999999994E-2 8.5999999999999993E-2 8.5000000000000006E-2 8.4000000000000005E-2 8.3000000000000004E-2 8.2000000000000003E-2 8.1000000000000003E-2 0.08 7.9000000000000001E-2 7.8E-2 7.6999999999999999E-2 7.5999999999999998E-2 7.4999999999999997E-2 7.3999999999999996E-2 7.2999999999999995E-2 7.1999999999999995E-2 7.0999999999999994E-2 7.0000000000000007E-2

Discount Rate for Plan B

NPVA - NPVB

Sensitivity on Discount Rate for Plan A

9.9000000000000005E-2 9.8000000000000004E-2 9.7000000000000003E-2 9.6000000000000002E-2 9.5000000000000001E-2 9.4E-2 9.2999999999999999E-2 9.1999999999999998E-2 9.0999999999999998E-2 0.09 8.8999999999999996E-2 8.7999999999999995E-2 8.6999999999999994E-2 8.5999999999999993E-2 8.5000000000000006E-2 8.4000000000000005E-2 8.3000000000000004E-2 8.2000000000000003E-2 8.1000000000000003E-2 0.08 7.9000000000000001E-2 7.8E-2 7.6999999999999999E-2 7.5999999999999998E-2 7.4999999999999997E-2 7.3999999999999996E-2 7.2999999999999995E-2 7.1999999999999995E-2 7.0999999999999994E-2 7.0000000000000007E-2

Discount Rate for Plan A

NPVA - NPVB

1. Plan A vs Plan B

Plan A
Year	0	1	2	3
Number of plants
Capacity
Demand
Production
Unit Price (Thousand dollar)
Revenue (Million dollar)
Unit Variable Cost (Thousand dollar)
Variable Cost (Million dollar)
Investment (Million dollar)
Salvage (Million dollar)
Net value (Million dollar)
Discount Factor @ 9.0%
Present Value (Million dollar)
NPV (Million dollar)
Plan B
Year	0	1	2	3
Number of plants
Capacity
Demand
Production
Unit Price (Thousand dollar)
Revenue (Million dollar)
Unit Variable Cost (Thousand dollar)
Variable Cost (Million dollar)
Investment (Million dollar)
Salvage (Million dollar)
Net value (Million dollar)
Discount Factor @ .0%
Present Value (Million dollar)
NPV (Million dollar)

2. Sensitivity - Discount Rate

		One-way data table
		RB	NPVA - NPVB					RA	NPVA - NPVB
		10.0%	Microsoft Office User: copy from Entries page					10.0%
									Microsoft Office User: copy from Entries page	9.9%						9.9%
		9.8%						9.8%
		9.7%						9.7%
		9.6%						9.6%
		9.5%						9.5%
		9.4%						9.4%
		9.3%						9.3%
		9.2%						9.2%
		9.1%						9.1%
		9.0%						9.0%
		8.9%						8.9%
		8.8%						8.8%
		8.7%						8.7%
		8.6%						8.6%
		8.5%						8.5%
		8.4%						8.4%
		8.3%						8.3%
		8.2%						8.2%
		8.1%						8.1%
		8.0%						8.0%
		7.9%						7.9%
		7.8%						7.8%
		7.7%						7.7%
		7.6%						7.6%
		7.5%						7.5%
		7.4%						7.4%
		7.3%						7.3%
		7.2%						7.2%
		7.1%						7.1%
		7.0%						7.0%
		Two-way data table
		Max	0.00
		Min	0.00
		small plant discount rate
		RB = 9.0%					RB = 8.5%					RB = 8.0%					RB = 7.5%					RB = 7.0%
plan A discount rate	0.00 Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	9.0%	8.9%	8.8%	8.7%	8.6%	8.5%	8.4%	8.3%	8.2%	8.1%	8.0%	7.9%	7.8%	7.7%	7.6%	7.5%	7.4%	7.3%	7.2%	7.1%	7.0%
	10.0%
	9.9%
	9.8%
	9.7%
	9.6%
	9.5%
	9.4%
	9.3%
	9.2%
	9.1%
	9.0%
	8.9%
	8.8%
	8.7%
	8.6%
	8.5%
	8.4%
	8.3%
	8.2%
	8.1%
	8.0%

Sensitivity on Discount Rate for Plan A and B

RB = 9.0% 0.1 9.9000000000000005E-2 9.8000000000000004E-2 9.7000000000000003E-2 9.6000000000000002E-2 9.5000000000000001E-2 9.4E-2 9.2999999999999999E-2 9.1999999999999998E-2 9.0999999999999998E-2 0.09 8.8999999999999996E-2 8.7999999999999995E-2 8.6999999999999994E-2 8.5999999999999993E-2 8.5000000000000006E-2 8.4000000000000005E-2 8.3000000000000004E-2 8.2000000000000003E-2 8.1000000000000003E-2 0.08 RB = 8.5% 0.1 9.9000000000000005E-2 9.8000000000000004E-2 9.7000000000000003E-2 9.6000000000000002E-2 9.5000000000000001E-2 9.4E-2 9.2999999999999999E-2 9.1999999999999998E-2 9.0999999999999998E-2 0.09 8.8999999999999996E-2 8.7999999999999995E-2 8.6999999999999994E-2 8.5999999999999993E-2 8.5000000000000006E-2 8.4000000000000005E-2 8.3000000000000004E-2 8.2000000000000003E-2 8.1000000000000003E-2 0.08 RB = 8.0% 0.1 9.9000000000000005E-2 9.8000000000000004E-2 9.7000000000000003E-2 9.6000000000000002E-2 9.5000000000000001E-2 9.4E-2 9.2999999999999999E-2 9.1999999999999998E-2 9.0999999999999998E-2 0.09 8.8999999999999996E-2 8.7999999999999995E-2 8.6999999999999994E-2 8.5999999999999993E-2 8.5000000000000006E-2 8.4000000000000005E-2 8.3000000000000004E-2 8.2000000000000003E-2 8.1000000000000003E-2 0.08 RB = 7.5% 0.1 9.9000000000000005E-2 9.8000000000000004E-2 9.7000000000000003E-2 9.6000000000000002E-2 9.5000000000000001E-2 9.4E-2 9.2999999999999999E-2 9.1999999999999998E-2 9.0999999999999998E-2 0.09 8.8999999999999996E-2 8.7999999999999995E-2 8.6999999999999994E-2 8.5999999999999993E-2 8.5000000000000006E-2 8.4000000000000005E-2 8.3000000000000004E-2 8.2000000000000003E-2 8.1000000000000003E-2 0.08 RB = 7.0% 0.1 9.9000000000000005E-2 9.8000000000000004E-2 9.7000000000000003E-2 9.6000000000000002E-2 9.5000000000000001E-2 9.4E-2 9.2999999999999999E-2 9.1999999999999998E-2 9.0999999999999998E-2 0.09 8.8999999999999996E-2 8.7999999999999995E-2 8.6999999999999994E-2 8.5999999999999993E-2 8.5000000000000006E-2 8.4000000000000005E-2 8.3000000000000004E-2 8.2000000000000003E-2 8.1000000000000003E-2 0.08

BIG plant (A) discount rate

NPVA - NPVB

3. Sensitivity Variable Costs

		Two-way data table
		Max	0.00
		Min	0.00
		small plant variable cost
		Var Cost B = 1.40					Var Cost B = 1.45					Var Cost B = 1.50					Var Cost B = 1.55					Var Cost B = 1.60
BIG plant variable cost	0.00 Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	1.40	1.41	1.42	1.43	1.44	1.45	1.46	1.47	1.48	1.49	1.50	1.51	1.52	1.53	1.54	1.55	1.56	1.57	1.58	1.59	1.60
	1.20
	1.21
	1.22
	1.23
	1.24
	1.25
	1.26
	1.27
	1.28
	1.29
	1.30
	1.31
	1.32
	1.33
	1.34
	1.35
	1.36
	1.37
	1.38
	1.39
	1.40
	1.41
	1.42
	1.43
	1.44
	1.45

Sensitivity on Variable Cost for Plan A and B

Var Cost B = 1.40 1.2 1.21 1.22 1.23 1.24 1.25 1.26 1.27 1.28 1.29 1.3 1.31 1.32 1.33 1.34 1.35 1.36 1.37 1.38 1.39 1.4 1.41 1.42 1.43 1.44 1.45 Var Cost B = 1.45 1.2 1.21 1.22 1.23 1.24 1.25 1.26 1.27 1.28 1.29 1.3 1.31 1.32 1.33 1.34 1.35 1.36 1.37 1.38 1.39 1.4 1.41 1.42 1.43 1.44 1.45 Var Cost B = 1.50 1.2 1.21 1.22 1.23 1.24 1.25 1.26 1.27 1.28 1.29 1.3 1.31 1.32 1.33 1.34 1.35 1.36 1.37 1.38 1.39 1.4 1.41 1.42 1.43 1.44 1.45 Var Cost B = 1.55 1.2 1.21 1.22 1.23 1.24 1.25 1.26 1.27 1.28 1.29 1.3 1.31 1.32 1.33 1.34 1.35 1.36 1.37 1.38 1.39 1.4 1.41 1.42 1.43 1.44 1.45 Var Cost B = 1.60 1.2 1.21 1.22 1.23 1.24 1.25 1.26 1.27 1.28 1.29 1.3 1.31 1.32 1.33 1.34 1.35 1.36 1.37 1.38 1.39 1.4 1.41 1.42 1.43 1.44 1.45

BIG plant (A) variable cost

NPVA - NPVB

4. Sensitivity - Demand

		Two-way data table
		Max	0.00
		Min	0.00
		First Year Demand
		1st Yr Demand = 150					1st Yr Demand = 200					1st Yr Demand = 250					1st Yr Demand = 300					1st Yr Demand = 350					1st Yr Demand = 400					1st Yr Demand = 450
Second Year Demand	0.00 Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	150	160	170	180	190	200	210	220	230	240	250	260	270	280	290	300	310	320	330	340	350	360	370	380	390	400	410	420	430	440	450
	300
	310
	320
	330
	340
	350
	360
	370
	380
	390
	400
	410
	420
	430
	440
	450
	460
	470
	480
	490
	500
	510
	520
	530
	540
	550
	560
	570
	580
	590
	600
	610
	620
	630
	640
	650
	660
	670
	680
	690
	700
	710
	720
	730
	740
	750
	760
	770
	780
	790
	800
	810
	820
	830
	840
	850
	860
	870
	880
	890
	900
		Two-way data table
		Max	0.00
		Min	0.00
		Third Year Demand
		3rd Yr Demand = 450					3rd Yr Demand = 600					3rd Yr Demand = 750					3rd Yr Demand = 900					3rd Yr Demand = 1050					3rd Yr Demand = 1200					3rd Yr Demand = 1350
Second Year Demand	0.00 Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	Alexander Fadeev: Alexander Fadeev: Reference to model outputs cells for data table	450	480	510	540	570	600	630	660	690	720	750	780	810	840	870	900	930	960	990	1020	1050	1080	1110	1140	1170	1200	1230	1260	1290	1320	1350
	300
	310
	320
	330
	340
	350
	360
	370
	380
	390
	400
	410
	420
	430
	440
	450
	460
	470
	480
	490
	500
	510
	520
	530
	540
	550
	560
	570
	580
	590
	600
	610
	620
	630
	640
	650
	660
	670
	680
	690
	700
	710
	720
	730
	740
	750
	760
	770
	780
	790
	800
	810
	820
	830
	840
	850
	860
	870
	880
	890
	900

Sensitivity on 1st and 2nd Year Demand for Laptops

1st Yr Demand = 450 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 1st Yr Demand = 400 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 1st Yr Demand = 350 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 1st Yr Demand = 300 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 1st Yr Demand = 250 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 1st Yr Demand = 200 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 1st Yr Demand = 150 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900

Second Year Demand

NPVA - NPVB

Sensitivity on 1st and 2nd Year Demand for Laptops

3rd Yr Demand = 450 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 3rd Yr Demand = 600 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 3rd Yr Demand = 750 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 3rd Yr Demand = 900 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 3rd Yr Demand = 1050 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 62 0 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 3rd Yr Demand = 1200 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900 3rd Yr Demand = 1350 300 310 320 330 340 350 360 370 380 390 400 410 420 430 440 450 460 470 480 490 500 510 520 530 540 550 560 570 580 590 600 610 620 630 640 650 660 670 680 690 700 710 720 730 740 750 760 770 780 790 800 810 820 830 840 850 860 870 880 890 900

Second Year Demand

NPVA - NPVB