1-2 pages plus Excel Calculations in 24 hours Finance wk7
MissMsw01
WK6AssginmentFinance.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 | Interest Rate | ||||
| # of Periods | # of Periods | ||||
| Starting Value | Lump Sum in the Future | ||||
| Future Lump Sum | $ - 0 | Present Value | $0 |
Required Interest Rates
| Required Interest Rates | ||
| Present Value | in savings | |
| Future Value | needed at retirement | |
| Additional funds | ||
| Number of Periods | years | |
| Required Interest Rate | ERROR:#NUM! | |
FV & PV Annuity
| a) | Future Value of an Annuity | a) | Present Value of an Annuity | ||
| Interest Rate | Interest Rate | ||||
| # of Periods | # of Periods | ||||
| Payments (per period) | Payment per period | ||||
| Future Value | $ - 0 | Present Value | $0.00 | ||
| b) | Future Value of an Annuity | b) | Present Value of an Annuity | ||
| Interest Rate | Interest Rate | ||||
| # of Periods | # of Periods | ||||
| Payments (per period) | Payment per period | ||||
| Future Value | $ - 0 | Present Value | $0.00 | ||
| c) | Future Value of an Annuity | c) | Present Value of an Annuity | ||
| Interest Rate | Interest Rate | ||||
| # of Periods | # of Periods | ||||
| Payments (per period) | Payment per period | ||||
| Future Value | $ - 0 | Present Value | $0.00 |
Bond Valuation
| Bond Valuation | ||
| Face Value | ||
| Yield to Maturity | ||
| Coupon Bond C | ||
| Coupon Bond Z | ||
| Years to Maturity | Price of Bond C | Price of Bond Z |
| 4 | $0.00 | $0.00 |
| 3 | $0.00 | $0.00 |
| 2 | $0.00 | $0.00 |
| 1 | $0.00 | $0.00 |
| 0 | $0.00 | $0.00 |
YTM & YTC
| Price of each of the three bonds | |||
| Basic Input Data | Bond A | Bond B | Bond C |
| Years to maturity | |||
| Coupon rate | |||
| Par value | |||
| Periodic payment | $0 | $0 | $0 |
| Yield to maturity | 9% | 9% | 9% |
| Price | $0.00 | $0.00 | $0.00 |
| Current Yield | |||
| Bond A | Bond B | Bond C | |
| Current yield | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! |
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
