Fingame

Explanation
Use this workbook to determine Project Selection and to assist in Capacity planning
Suggested steps
1 Use FireFox browser to copy data to CurrentQuarterOutput
a. Open FinGame and View desired quarter (i.e., select quarter 1).
b. Right click mouse and click select all If you are using Chrome, CTRL + A will select the entire web page
c. Press both keys Ctrl and C
d. Click CurrentQuarterOutput sheet in this template
e. Select cell A1 and then press both kys Ctrl and V
f. Count number of rows to delete---rows before Decision inputs for Quarter Number … The Macro will ask for this number
g. Press this button to run the Sheet clean up macro
h. CheckCurrentQuarterOutput: Decision Inputs for Quarter Number …. should be in row 1. You must repeat steps a through g if there is a mistake.
2 Run Macro on CapacityPlanning worksheet
3 Run Macro on ProjectAorB work sheet
Qtry Data

CapacityPlanning
color code
black copy from current quarter output
green decision
blue/red output from formula, use for analysis
INPUT PRODUCTION AND PLANT/MACHINERY PURCHASE Warehouse fees
CAPACITY PLANNING 1 2 3 4 5 Units First 2000 Next 5000 Over 7000
forecast: units 102,250 108,193 95,736 89,349 $1.00 $3.00 $8.00 Price elasticity analysis
forecast: price $99.96 $98.81 $102.52 $101.88 Original New Forecast
ExistingUnits of plant capacity 100,000 100,000 100,000 80,000 Production costs per unit next quarter: note lowest average cost at 120,000 units 105,980 112,975
Plant capacity with purchases 100,000 100,000 100,000 100,000 Materials $15.00 machinery $44.00 plant $304.00 $102.30 $ 100.25
Existing Units of machine capacity 100,000 100,000 75,000 60,000 Units First 60,000 Next 40,000 Next 20,000 Over 120,000
Machine capacity with purchases 100,000 100,000 100,000 100,000 60,000 40,000 20,000
Other overhead 200,000 200,000 200,000 200,000 Labor cost $39.00 $29.00 $25.00 $33.00 Elasticity 3.3
Units plant purchased - 0 20,000 Average cost $35.00 $33.33 Percent change -0.02
Units machine purchased 0 25,000 15,000
Plant order cost $ - 0 $ 250,000 $ - 0 $ - 0 Depreciation: Machinery 478,125 478,125 384,375 311,250
Sales 102,250 108,193 95,736 89,349 New $ - 0 $ 137,500 $ 220,000
End of quarter inventory 0 12,426 10,176 1,983 6,247 16,898 Total 478,125 478,125 521,875 531,250
Target Production 110,000 110,000 110,000 110,000
Limit or target production 100,000 100,000 100,000 100,000 Projects 0 0 0 0
inventory costs $ 36,408 $ 1,983 $ 14,741 $ 90,184 Plant 1,300,000 1,300,000 1,300,000 989,000
total labour cost to produce $ 3,500,000 $ 3,500,000 $ 3,500,000 $ 3,500,000 New $ - 0 $ 304,000
labour cost per unit $ 35.00 $ 35.00 $ 35.00 $ 35.00 Total 1,300,000 1,300,000 1,300,000 1,293,000
material cost per unit $ 15.00 $ 15.00 $ 15.00 $ 15.00
indirect cost per unit $ 20.15 $ 22.30 $ 20.37 $ 21.14
TOTAL COST PER UNIT $ 70.15 $ 72.30 $ 70.37 $ 71.14
lost sales $ - 0 $ - 0 $ - 0 $ - 0 TOTAL
Total costs(lost sales + Plant order + inventory)
Madeleine: Madeleine: inventory + lost sales+ order costs $ 36,408 $ 251,983 $ 14,741 $ 90,184 $ 393,316
Gross Profit $3,048,550 $2,868,080 $3,078,280 $2,746,200
Click to Update sheet for current quarter

ProjectAorB
Capital budgeting analysis Quarter 2 NOTE: RUN CB if you have purchased the second forecast.
Data from INFORMATION FOR FUTURE QUARTERS Click button to run Capital Budgeting report
INFORMATION FOR FUTURE QUARTERS: Unmerge data first!
Estimated by average of 4-quarter forecast
2 3 4 5 6 7 8 9 10 11 12 13
Units forecast 102,250 108,193 95,736 89,349 98,882 98,882 98,882 98,882 98,882 98,882 98,882 98,882
Price per unit forecast $99.96 $98.81 $102.52 $101.88 101 101 101 101 101 101 101 101
Units of plant capacity 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000
Units of machine capacity 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000 100,000
Other overhead 200,000 200,000 200,000 200,000
Depreciation: Machinery 478,125 478,125 521,875 531,250
Projects 0 0 0 0
Plant 1,300,000 1,300,000 1,300,000 1,293,000
Capital budgeting analysis for next quarter:
Life Cost Unit Capacity Overhead Saving Unit Labor sav., Qtr.2 Change/Qtr. Labor Sav.
A 2-yr $467,016 100,000 $13,521 $0.58 $0.02
B 3-yr $641,136 120,000 ($9,081) $1.06 ($0.02)
100000
tax rate 40% WACC 2.51%
Project A 2 3 4 5 6 7 8 9
Overhead Saving $13,521 $13,521 $13,521 $13,521 $13,521 $13,521 $13,521 $13,521
Unit Labor sav., Qtr.2 $0.58 $0.60 $0.62 $0.64 $0.66 $0.68 $0.70 $0.72
Total labor savings $58,000 $60,000 $62,000 $64,000 $66,000 $68,000 $70,000 $72,000 Assumes full capacity
Depreciation $58,377 $58,377 $58,377 $58,377 $58,377 $58,377 $58,377 $58,377
Increase EBIT $13,144 $15,144 $17,144 $19,144 $21,144 $23,144 $25,144 $27,144
taxes $5,258 $6,058 $6,858 $7,658 $8,458 $9,258 $10,058 $10,858
NOPAT $7,886 $9,086 $10,286 $11,486 $12,686 $13,886 $15,086 $16,286
Depreciation $58,377 $58,377 $58,377 $58,377 $58,377 $58,377 $58,377 $58,377
Cash Flow $66,263 $67,463 $68,663 $69,863 $71,063 $72,263 $73,463 $74,663
PV cash flows $503,791.63
cost $467,016
NPV $36,775.63
Project B 2 3 4 5 6 7 8 9 10 11 12 13
Overhead Saving ($9,081) ($9,081) ($9,081) ($9,081) ($9,081) ($9,081) ($9,081) ($9,081) ($9,081) ($9,081) ($9,081) ($9,081)
Unit Labor sav., Qtr.2 $1.06 $1.04 $1.02 $1.00 $0.98 $0.96 $0.94 $0.92 $0.90 $0.88 $0.86 $0.84
Total labor savings
nmohan1: nmohan1: although B's capacity is 120,000, forecast is less $106,000 $104,000 $102,000 $100,000 $98,000 $96,000 $94,000 $92,000 $90,000 $88,000 $86,000 $84,000
Depreciation $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Increase EBIT $96,919 $94,919 $92,919 $90,919 $88,919 $86,919 $84,919 $82,919 $80,919 $78,919 $76,919 $74,919
taxes $38,768 $37,968 $37,168 $36,368 $35,568 $34,768 $33,968 $33,168 $32,368 $31,568 $30,768 $29,968
NOPAT $58,151 $56,951 $55,751 $54,551 $53,351 $52,151 $50,951 $49,751 $48,551 $47,351 $46,151 $44,951
Depreciation $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Cash Flow $58,151 $56,951 $55,751 $54,551 $53,351 $52,151 $50,951 $49,751 $48,551 $47,351 $46,151 $44,951
PV cash flows $531,961
cost $641,136
NPV ($109,175)
Run CB Report

WACC
Current Liabilities light blue area must updated each quarter
Accounts Payable $520,000 Common share price $35.98
Short Term Loans Payable 0 Approximate cost 2.72% =PreferredCost +(PreferredCost-BondCost)
Short Term Penalty Loan 0 number of shares 1000000
Intermediate Term Debt Maturing 1,850,000
Bonds Maturing 1,200,000
Total Current Liabilities $3,570,000
Long Term Liabilities
Intermediate Loans: 2 years $937,500
3 years 0
Bonds 1,200,000 % cost
Total Long Term Liabilities 2,137,500 2-year loans $937,500 2% 1.86%
Total Liabilities $5,707,500 3-year loans $ 1,850,000 4% 1.84%
Owners' Equity bonds 2,400,000 6% 1.82%
Preferred Stock ( 0 shares ) $0 Preferred Stock $0 0%
Common Stock ( 1,000,000 shares ) 8,000,000 Common stock $35,980,000 87% 2.72%
Retained Earnings 3,190,368 total $41,167,500 100% 2.51%
Total Equity 11,190,369
Total Liabilities and Equity $16,897,868
Proceeds from new share issue Rates for Next Quarter
New share price $ 30.12 =B23/(1.05+0.5*(B25/B24))-50000/B25
current share price $35.98 Short-term 2-year loan 3-year loan Bond Preferred
existing shares 1000000
new shares 275000 1.89% 1.86% 1.84% 1.82% 2.27%
proceeds $ 8,282,211
CurrentQuarterOutput
Decision Inputs for Quarter Number 1
Company Operating Decisions
Units to be produced 100,000 Per unit price $100.00
Div. per common share $0.10 Advertising cost $0
Demand/price forecast $0 Sales discount 0.00%
Investment Decisions
Short-term investment $200,000 Risk of S-T investment 0
Machine units bought 0 Units of plant bought 0
Project A no Project B no
Financing Decisions
Short-term loans $0 Preferred shares 0
Two-year loans $2,500,000 Common shares 0
Three-year loans $0 Common tender price $0
Ten-year bonds $0
Special Options
Strike settlement (per hr.) $0.00 Dollar penalty $0
Quarterly Performance Report
Quarter Number 1
Sales revenue ( 97,383 units at $100.00 ) $9,738,300
Income from securities 2,232 $9,740,532
Cost of Goods Sold:
Beginning Inventory: ( 9,809 at $74.79 ) $733,567
Materials $1,500,000
Direct Labor 3,500,000
Total Direct Costs $5,000,000
Warehousing Costs $60,408
Depreciation: Mach. and Equip. 478,125
Plant 1,300,000
Other Overhead Costs 200,000
Total Indirect Costs 2,038,533
Production Costs ( 100,000 at $70.39 ) 7,038,533
Goods Available for Sale ( $70.78 per unit ) $7,772,100
Less: Ending Inventory ( 12,426 units ) 879,492
Cost of Goods Sold 6,892,608
Gross Profit $2,847,923
Selling and administrative expenses $1,486,915
Financial Expenses:
Short Term Bank Interest $0
Penalty Loan Interest 0
Intermediate Term Loan Interest 92,749
Bond Interest 33,600
Bond Redemption Costs 0
Total Financial Charges 126,349 1,613,264
Operating Income Before Extraordinary Items $1,234,660
Extraordinary Items 0
Income Before Taxes $1,234,659
Income Tax (rate is 40%) 493,863
Income After Taxes $740,796
Preferred Stock Dividind 0
Earnings to Common Stockholders $740,796
Common Stock Dividends ( $0.10 per share ) 100,000
Net Income Transferred to Retained Earnings $640,796
Position Statement
Quarter Number 1
ASSETS
Current Assets
Cash $120,342
Marketable Securities 200,000
Accounts Receivable 6,524,661
Inventory ( 12,426 units at $70.78 /UNIT ) 879,492
Total Current Assets $7,724,493
Fixed Assets (net of depreciation
Machinery and Equipment $2,008,125
Plant 7,165,250
Total Fixed Assets 9,173,375
Total Assets $16,897,868
LIABILITIES AND OWNER EQUITY
Current Liabilities
Accounts Payable $520,000
Short Term Loans Payable 0
Short Term Penalty Loan 0
Intermediate Term Debt Maturing 1,850,000
Bonds Maturing 1,200,000
Total Current Liabilities $3,570,000
Long Term Liabilities
Intermediate Loans: 2 years $937,500
3 years 0
Bonds 1,200,000
Total Long Term Liabilities 2,137,500
Total Liabilities $5,707,500
Owners' Equity
Preferred Stock ( 0 shares ) $0
Common Stock ( 1,000,000 shares ) 8,000,000
Retained Earnings 3,190,368
Total Equity 11,190,369
Total Liabilities and Equity $16,897,868
Summary Data
Quarter Number 1
HISTORICAL INFORMATION
Common share price $35.98 Accumulated Wealth $36.08
Quarterly EPS $0.74 Dividend Yield 1.09%
Price earnings ratio 12.14 Marketable Security Yield 1.12%
Actual unit price $100.00 Actual unit demand 97,383
Preferred stock price $32.15 Preferred dividend yield 3.11%
Return on investment 17.54% Return on equity 26.48%
Call premium: preferred 8.00% Bond call premium 8.00%
Common tender or sell/sh $0.00 Unpaid preferred dividend/share $0.00
Outstanding debt yields:
Short-term 2-year loan 3-year loan Bonds Penalty loan
2.98% 3.11% 2.50% 1.40% 8.00%
INFORMATION FOR FUTURE QUARTERS:
2 3 4 5
Units forecast 102,250 108,193 95,736 89,349
Price per unit forecast $99.96 $98.81 $102.52 $101.88
Units of plant capacity 100,000 100,000 100,000 80,000
Units of machine capacity 100,000 100,000 75,000 60,000
Other overhead 200,000 200,000 200,000 200,000
Depreciation: Machinery 478,125 478,125 384,375 311,250
Projects 0 0 0 0
Plant 1,300,000 1,300,000 1,300,000 989,000
Principal repayment on debt:
Short-term 0 0 0
2-year 312,500 312,500 312,500 312,500
3-year 300,000 300,000 0 0
Bonds 300,000 300,000 300,000 300,000
Warehouse fees:
Units First 2000 Next 5000 Over 7000
Cost/Unit $1.00 $3.00 $8.00
Production costs per unit next quarter:
Materials $15.00 Machinery $44.00 Plant $304.00
Units First 60,000 Next 40,000 6 Over 120,000
Labor cost $39.00 $29.00 $25.00 $33.00
Rates on funding in quarter 2
Short-term 2-year loan 3-year loan Bond Preferred
1.89% 1.86% 1.84% 1.82% 2.27%
Interest due next quarter:
Short-term $0
Intermediate $83,030
Bonds $33,600
Capital budgeting projects for next quarter:
Life Cost Unit Capacity Overhead Saving Unit Labor sav., Qtr.2 Change/Qtr. Labor Sav.
A 2-yr $467,016 100,000 $13,521 $0.58 $0.02
B 3-yr $641,136 120,000 ($9,081) $1.06 ($0.02)