1-2 Pages plus Excel Calculations wk8 in 30 hours
MissMsw01
WK7AssgnExcel.xlsxCash Conversion Cycle
| Cash Conversion Cycle | ||
| (a) | Enter figures below | |
| Inventory Conversion Period | 64 | days |
| Average Collection Period | 28 | days |
| Payables Deferral Period | 41 | days |
| Cash Conversion Cycle | 51 | days |
| (b) | ||
| Annual Sales | $ 2,578,235.00 | |
| divided into 365 days | 365 | days |
| Average Sales per Day | $ 7,063.6575 | |
| Average Collection Period | 28 | days |
| Investment in Receivables | $ 197,782.41 | |
| (c) | ||
| Step 1: Inventory Balance | ||
| Annual Sales | $ 2,578,235.00 | |
| Cost of Goods Sold | 75% | percent of sales |
| divided into 365 days | 365 | days |
| Inventory Conversion Period | 64 | days |
| $ 5,297.74 | ||
| Inventory | $ 339,055.56 | |
| Step 2: Inventory Turnover Ratio | ||
| Annual Sales | $ 2,578,235.00 | |
| Inventory | $ 339,055.56 | |
| Turnover Ratio | 7.60 | times a year |
| (d) | ||
| Competitor A | 88 | days |
| 54 | days | |
| 30 | days | |
| 112 | days | |
| Competitor B | 90 | days |
| 44 | days | |
| 30 | days | |
| 104 | days |
Additional Funds Needed
| Additional Funds Needed | |
| Last year's Sales | $ 5,000,000 |
| Sales to Increase (in percent) | 20% |
| Total Liabilities and Equity = Assets | $ 3,000,000 |
| Accounts Payable | $ 300,000 |
| Notes Payable | $ 600,000 |
| Accrued Liability | $ 300,000 |
| Profit Margin | 3% |
| Retained | 30% |
| Required increase in Assets | $ 600,000 |
| Spontaneous increase in Payables and Accruals | $ 120,000 |
| Increase in Retained Earnings | $ 54,000 |
| Assets/Sales | 60% |
| Next year's Sales (forecasted) | $ 6,000,000 |
| Change in Sales | $ 1,000,000 |
| Additional Funds Needed | $ 426,000 |
Cross-Exchange Rate
| Cross-Exchange Rate | ||
| $1 to Israeli shekels | 3.58 | |
| $1 to Japanese Yen | 109 | |
| Cross-Exchange Rate | 30.45 | Yen/Shekel |
FV & PV Lump Sum
| Future Value | Present Value | ||||
| Interest Rate | 6.0% | Interest Rate | 5.0% | ||
| # of Periods | 5 | # of Periods | 20 | ||
| Starting Value | $ 2,000,000 | Lump Sum in the Future | $ 29,000 | ||
| Future Lump Sum | $ 2,676,451 | Present Value | $10,930 |
Required Interest Rates
| Required Interest Rates | ||
| Present Value | $350,000 | in savings |
| Future Value | $800,000 | needed at retirement |
| Additional funds | ||
| Number of Periods | 19 | years |
| Required Interest Rate | 4.45% | |
FV & PV Annuity
| a) | Future Value of an Annuity | a) | Present Value of an Annuity | ||
| Interest Rate | 14.0% | Interest Rate | 8.0% | ||
| # of Periods | 8 | # of Periods | 12 | ||
| Payments (per period) | $ 500.00 | Payment per period | $ 600 | ||
| Future Value | $ 6,616 | Present Value | $4,521.65 | ||
| b) | Future Value of an Annuity | b) | Present Value of an Annuity | ||
| Interest Rate | 7.0% | Interest Rate | 4.0% | ||
| # of Periods | 4 | # of Periods | 6 | ||
| Payments (per period) | $ 250.00 | Payment per period | $ 300 | ||
| Future Value | $ 1,110 | Present Value | $1,572.64 | ||
| c) | Future Value of an Annuity | c) | Present Value of an Annuity | ||
| Interest Rate | 0.0% | Interest Rate | 0.0% | ||
| # of Periods | 4 | # of Periods | 6 | ||
| Payments (per period) | $ 700.00 | Payment per period | $ 500 | ||
| Future Value | $ 2,800 | Present Value | $3,000.00 |
Bond Valuation
| Bond Valuation | ||
| Face Value | $1,000 | |
| Yield to Maturity | 8.2% | |
| Coupon Bond C | 11.50% | |
| Coupon Bond Z | 0% | |
| Years to Maturity | Price of Bond C | Price of Bond Z |
| 4 | $1,108.82 | $729.61 |
| 3 | $1,084.74 | $789.44 |
| 2 | $1,058.69 | $854.17 |
| 1 | $1,030.50 | $924.21 |
| 0 | $1,000.00 | $1,000.00 |
YTM & YTC
| Price of each of the three bonds | ||||||||
| Basic Input Data | Bond A | Bond B | Bond C | Type of the Bond | Face Value | Price of the bond | ||
| Years to maturity | 12 | 12 | 12 | Bond A | $1,000 | $856.79 | Discount | |
| Coupon rate | 7% | 9% | 11% | Bond B | $1,000 | $1,000.00 | At par | |
| Par value | $1,000 | $1,000 | $1,000 | Bond C | $1,000 | $1,143.21 | At a premium | |
| Periodic payment | $70 | $90 | $110 | |||||
| Yield to maturity | 9% | 9% | 9% | |||||
| Price | $856.79 | $1,000.00 | $1,143.21 | |||||
| Current Yield | ||||||||
| Bond A | Bond B | Bond C | ||||||
| Current yield | 8.17% | 9.00% | 9.62% | |||||
CAPM and Required Return
| CAPM and Required Return | |
| Market Beta | 1.0 |
| Required Return | |
| Risk-Free Rate | |
| Market Premium | 0.00% |
| Your Company | |
| Risk-Free Rate | 0.00% |
| Market Premium | 0.00% |
| Company Beta | |
| Required Return | 0.00% |
| Closet Competitor | |
| Risk-Free Rate | 0.00% |
| Market Premium | 0.00% |
| Competitor's Beta | |
| Required Return | 0.00% |
| Difference in Required Return | |
| 0.00% |
Constant Growth Value
| Constant Growth Valuation | |
| Expected Dividend | |
| Constant Growth | |
| Required Rate of Return | |
| Current Value per Share | ERROR:#DIV/0! |
Non-Constant Growth Val
| Non-Constant Growth Valuation | ||||
| Paid Dividend | ||||
| Non-Constant Growth x 2 years | ||||
| Constant Growth thereafter | ||||
| Required Rate of Return | ||||
| Cash Flow at Horizon or Continuing Date | ERROR:#DIV/0! | |||
| Horizon Timeline Years | 0 | 1 | 2 | 3 |
| Dividends | $0.0000 | $0.0000 | $0.0000 | $0.0000 |
| ERROR:#DIV/0! | ||||
| Cash Flow | ERROR:#DIV/0! | |||
| Present Value | $0.0000 | ERROR:#DIV/0! | $0.0000 | |
| Intrinsic Stock Value | ERROR:#DIV/0! | |||
WACC
| Weighted Average Cost of Capital | |
| Debt | |
| Common Equity | |
| Cost of Debt | |
| Tax Rate | |
| Current Stock Price | |
| Last Dividend Paid | |
| Expected Constant Growth | |
| Next Dividend | $ - 0 |
| Internal Equity | ERROR:#DIV/0! |
| WACC | ERROR:#DIV/0! |
Capital Budgeting
| Capital Budgeting Criteria | ||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Project A | ||||||||
| Project B | ||||||||
| Difference | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
| WACC | 11% | WACC | 18% | |||||
| NPV @ 11% | NPV @ 18% | |||||||
| Project A | $0.00 | Project A | $0.00 | |||||
| Project B | $0.00 | Project B | $0.00 | |||||
| IRR @ 11% | ||||||||
| Project A | ERROR:#NUM! | |||||||
| Project B | ERROR:#NUM! | |||||||
| MIRR @ 11% | MIRR @ 18% | |||||||
| Project A | ERROR:#DIV/0! | Project A | ERROR:#DIV/0! | |||||
| Project B | ERROR:#DIV/0! | Project B | ERROR:#DIV/0! | |||||
| Discount Rate | NPV-A | NPV-B | ||||||
| 0.0% | $0 | $0 | ||||||
| 10.0% | $0 | $0 | ||||||
| 11.0% | $0 | $0 | ||||||
| 18.1% | $0 | $0 | ||||||
| 20.0% | $0 | $0 | ||||||
| 24.0% | $0 | $0 | ||||||
| 30.0% | $0 | $0 | ||||||
| Crossover Rate | ERROR:#NUM! | |||||||
Comparison Project A vs Project B
0 0 0 0 0 0 0 0 0 0 0 0 0 0
