Fingame 5.0

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)