Future Cars Inc. Strategic Investment Decision--Skeleton Spreadsheet
Constants 2012 2013
Direct Labor Cost/Hr, Future Cars Inc. NA $30
Direct Materials Cost per Electric Hybrid Car (3% inflation) NA $11,000
Direct Materials Cost per Fuel Cell Car (3% inflation) NA $14,000
Direct Labor Hours per Electric Hybrid Car (5% learning curve) NA 160.00
Direct Labor Hours per Fuel Cell Car (5% learning curve) NA 130.00
Overhead Allocation Rate per Direct Labor Hour (both technologies) NA $60.00
Capital Investment for Electric Hybrid Car $600,000,000 NA
Capital Investment for Fuel Cell Car $1,100,000,000 NA
Median Alternative Energy Car Sales Price, 2012 $35,000 NA
Corporate Income Tax Rate 25% 25%
Inputs 2012 2013
Current Economy (R=Recession, B=Boom) NA
Cost of Gasoline (L=Low, H=High) NA
Technology Selection (E=Electric Hybrid, F=Fuel Cell) NA
Summary of Key Results 2012 2013
Net Income After Taxes NA
End-of-year Cash on hand NA
Internal Rate of Return for Investment NA NA
Calculations 2012 2013
New Product Capital Investment (internally financed) NA
Total Forecasted U.S. Market Demand for Alternative Energy Cars 1,600,000
Forecasted Sales for Future Cars (units) NA
Total Direct Materials NA
Total Direct Labor NA
Total Manufacturing Overhead NA
Cost of Goods Sold NA
Manufacturing Cost per Unit NA
Unit Sales Price $35,000.00
Period Costs (Sales, Marketing, Distribution, Customer Service) $1,000,000,000
Income and Cash Flow Statements 2012 2013
Beginning-of-year Cash on Hand (deduct Investment for 2013) NA
Total Sales Revenues NA
less: Cost of Goods Sold NA
Gross Profit NA
less: Period Costs NA
Net Profit before Income Tax NA
less: Income Tax Expense NA
Net Income after Taxes (Cash Inflow) NA
End-of-year Cash on Hand (Available to Invest) $1,300,000,000
Internal Rate of Return Calculation
Investment (Cash Outflow)
Cash Inflow 2013
Cash Inflow 2014
Cash Inflow 2015
Internal Rate of Return (IRR)
Spreadsheet
2014 2015
$32 $33
$11,330 $11,670
$14,420 $14,853
152.00 144.40
123.50 117.33
$61.80 $63.65
NA NA
NA NA
NA NA
26% 27%
2014 2015
NA NA
NA NA
NA NA
2014 2015
NA
2014 2015
NA NA
2014 2015