Question

PLEASE SHOW EXCEL FORMULA WORK

Lockheed Tri Star and Capital Budgeting1 In 1971, the American aerospace company, Lockheed, found itself in Congressional hearings seeking a $250 million federal guarantee to secure bank credit required for the completion of the L-1011 Tri Star program. The L-1011 Tri Star Airbus was a wide-bodied commercial jet aircraft with a capacity of up to 400 passengers, competing with the DC-10 triject and the A- 300B airbus. Spokesmen for Lockheed claimed that the Tri Star program was economically sound and that their problem was merely a liquidity crisis caused by some unrelated military contracts. Opposing the guarantee, other parties argued that the Tri Star program had been economically unsound and doomed to financial failure from the very beginning. Facts and situations concerning the Lockheed Tri Star program are taken from U. E. Reinhardt, "Break-Even Analysis for Lockheed's Tri Star: An Application of Financial Theory," Journal of Finance 27 (1972), 821-838, and from House and Senate testimony.

PLEASE SHOW EXCEL FORMULA WORK

Answer 1

A.Estimated Value of Tri Star Project as of end of 1967:

End of year	Pre-Production Cost	Production Cost	Total Cost	Revenue	Net Cash Flow	Discounting @10 %	Present Value
1967	-100	0	-100	0	-100	1.00	-100.00
1968	-200	0	-200	0	-200	0.91	-181.82
1969	-200	0	-200	0	-200	0.83	-165.29
1970	-200	0	-200	140	-60	0.75	-45.08
1971	-200	-490	-690	140	-550	0.68	-375.66
1972	0	-490	-490	560	70	0.62	43.46
1973	0	-490	-490	560	70	0.56	39.51
1974	0	-490	-490	560	70	0.51	35.92
1975	0	-490	-490	560	70	0.47	32.66
1976	0	-490	-490	420	-70	0.42	-29.69
1977	0	0	0	420	420	0.39	161.93
							-584.05

A. At Production of 300 Units, Lockheed can not break even in terms of Net Present Value.

End of year	Pre-Production Cost (A)	Units	Cost Per Unit	Production Cost (B)	Total Cost (A+B)	Cash Inflow	Units	Sales Price	Revenue	Net Cash Flow	Discounting @10 %	Present Value
1967	-100	0	0	0	-100	0	0	0	0	-100	1.00	-100.00
1968	-200	0	0	0	-200	0	0	0	0	-200	0.91	-181.82
1969	-200	0	0	0	-200	0	0	0	0	-200	0.83	-165.29
1970	-200	0	0	0	-200	200	0	0	0	0	0.75	0.00
1971	-200	50	-12.5	-625	-825	200	0	0	0	-625	0.68	-426.88
1972	0	50	-12.5	-625	-625	800	50	16	800	175	0.62	108.66
1973	0	50	-12.5	-625	-625	800	50	16	800	175	0.56	98.78
1974	0	50	-12.5	-625	-625	800	50	16	800	175	0.51	89.80
1975	0	50	-12.5	-625	-625	800	50	16	800	175	0.47	81.64
1976	0	50	-12.5	-625	-625	600	50	16	800	-25	0.42	-10.60
1977	0	0	0	0	0	600	50	16	800	600	0.39	231.33
											Net Present Value	-274.38

B. True Breakeven Sales = Fixed cost / Sales price – Variable cost

Fixed Cost = Pre-production cost = $900 Million

                 = 900/ (16-12.5) = 257.14 = 257 Units

C. The decision to pursue Tri Star programme is reasonable one only if Lockheed can produce and sell units of aircrafts.