LocationAnalysis.xls

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3	Locational Break-Even Analysis with Data Table
4	เพื่อตัดสินใจเลือก Site ซึ่งทำให้ต้นทุนการผลิตต่ำที่สุด
5			Per Units Costs
6	Location	Fixed Cost	Material	Labor	Variable Overhead		Units	Total Cost
7	Site 1	200,000	0.20	0.40	0.40		8000	208,000	=Fixed_Cost+(Material+Labor+Variable_Overhead)*Units
8	Site 2	180,000	0.25	0.75	0.75			194,000
9	Site 3	170,000	1.00	1.00	1.00			194,000
10
11			Number of Units as Data Table Input Variable				( Bold Number is the minimum cost )
12	Total Cost Formula		0	2,000	4,000	6,000	8,000	10,000	12,000	14,000	16,000	18,000	20,000	22,000	24,000	26,000	28,000	30,000	32,000	34,000	36,000	38,000	40,000
13	Site 1	208,000	200,000	202,000	204,000	206,000	208,000	210,000	212,000	214,000	216,000	218,000	220,000	222,000	224,000	226,000	228,000	230,000	232,000	234,000	236,000	238,000	240,000
14	Site 2	194,000	180,000	183,500	187,000	190,500	194,000	197,500	201,000	204,500	208,000	211,500	215,000	218,500	222,000	225,500	229,000	232,500	236,000	239,500	243,000	246,500	250,000
15	Site 3	194,000	170,000	176,000	182,000	188,000	194,000	200,000	206,000	212,000	218,000	224,000	230,000	236,000	242,000	248,000	254,000	260,000	266,000	272,000	278,000	284,000	290,000
16
17			Format > Conditional Formatting
18			Formula is : =MIN(E$13:E$15)=E13
19
20		Number of Units :	0	2,000	4,000	6,000	8,000	10,000	12,000	14,000	16,000	18,000	20,000	22,000	24,000	26,000	28,000	30,000	32,000	34,000	36,000	38,000	40,000
21		Minimum Cost :	170,000	176,000	182,000	188,000	194,000	197,500	201,000	204,500	208,000	211,500	215,000	218,500	222,000	225,500	228,000	230,000	232,000	234,000	236,000	238,000	240,000
22		Selected Site :	Site 3	Site 3	Site 3	Site 3	Site 2	Site 2	Site 2	Site 2	Site 2	Site 2	Site 2	Site 2	Site 2	Site 2	Site 1	Site 1	Site 1	Site 1	Site 1	Site 1	Site 1
23			=INDEX($C$13:$C$15, MATCH( E21, E13:E15, 0), 1)
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100