| Chapter 18 Q6 | ||||
| Consider the following financial statement for the XX Icers Corp. | ||||
| Item | Beginning | Ending | ||
| Inventory | 20,386 | 22,164 | ||
| Accounts Receivable | 16,783 | 17,216 | ||
| Accounts Payable | 17,099 | 17,561 | ||
| Net Sales | 418,276 | |||
| Cost of Goods Sold | 234,912 | |||
| All sales are received on credit | ||||
| Calculate the operating and cash cylces. | ||||
| Operating Cycle: | ||||
| Inventory Turnover | (Cost of Goods Sold / Average Inventory) | |||
| Inventory Turnover | 11.0416921 | =D10/(AVERAGE(C6,E6)) | ||
| Inventory Period | (365/Inventory Turnover) | |||
| Inventory Period | 33.0565276 | =365/C18 | ||
| Receivables Turnover | (Sales/Average Receivables) | |||
| Receivables Turnover | 24.6051943 | =D9/AVERAGE(C7:E7) | ||
| Receivables Period | (365/Receivables Turnover) | |||
| Receivables Period | 14.8342661 | =365/C23 | ||
| Operating Cylce | (Inventory Period + Receivables Period) | |||
| Operating Cylce | 47.8907937 | =C20+C25 | ||
| Cash Cylce: | ||||
| Payables Turnover | (Cost of Goods Sold / Average Payables) | |||
| Payables Turnover | 13.5552222 | =D10/AVERAGE(C8:E8) | ||
| Payables Period | (365/Payables Turnover) | |||
| Payables Period | 26.9268918 | =365/C32 | ||
| Cash Cycle | Operating Cycle - Payables Turnover | |||
| Cash Cycle | 20.9639019 | =C28-C34 | ||
Sunday, December 30, 2018
Finance Chapter 18 Q6 Icers Corporation Cash Cycle Operating Cycle
Excel File
Finance Chapter 18 - Short Term Finance Policies
Short Term Financial Policies
Key question: how much to invest in CURRENT ASSETS
Flexible policy actions include:
Keeping large cash and securities balances
Keeping large amounts of inventory
Granting liberal credit terms
Restrictive policy actions include:
Keeping low cash and securities balances
Keeping small amounts of inventory
Allowing few or no credit sales
Finance Chap 4 Q41
Excel File
| Chapter 4 | |||
| 41 | EAR vs APR | ||
| Purchase Price | 3900000 | ||
| Nper | 30 | years | |
| Mortgage | 0.8 | ||
| Borrowed | 3120000 | =C7*C5 | |
| PMT | 18250 | ||
| APR | 5.77% | =RATE(C6*12,-C9,C8)*12 | |
| EAR | 5.93% | =EFFECT(C11,12) | |
| Note - 12 is used to annualize the %. | |||
Finance Chapter 4 Question 40
You have just won the lottery...
Excel File
| Chapter 4 | ||||||
| 40 | Calculating Present Values | |||||
| You have just won the lottery. | ||||||
| PMT | 1000000 | |||||
| PMT growth | 165000 | |||||
| i | 0.075 | |||||
| nper | 10 | |||||
| Payment | PV | |||||
| 0 | 1000000 | $1,000,000.00 | =D13 | |||
| 1 | 1165000 | $1,083,720.93 | =PV($D$9,C14,,-D14) | |||
| 2 | 1330000 | $1,150,892.37 | =PV($D$9,C15,,-D15) | |||
| 3 | 1495000 | $1,203,416.05 | =PV($D$9,C16,,-D16) | |||
| 4 | 1660000 | $1,243,008.88 | =PV($D$9,C17,,-D17) | |||
| 5 | 1825000 | $1,271,219.50 | =PV($D$9,C18,,-D18) | |||
| 6 | 1990000 | $1,289,443.42 | =PV($D$9,C19,,-D19) | |||
| 7 | 2155000 | $1,298,936.81 | =PV($D$9,C20,,-D20) | |||
| 8 | 2320000 | $1,300,829.18 | =PV($D$9,C21,,-D21) | |||
| 9 | 2485000 | $1,296,134.93 | =PV($D$9,C22,,-D22) | |||
| 10 | 2650000 | $1,285,763.91 | =PV($D$9,C23,,-D23) | |||
| Sum | $13,423,365.99 | =SUM(E13:E23) | ||||
Saturday, December 29, 2018
Finance OCF - Operating Cash Flow
There are multiple ways to calculate OCF,
including:
OCF = EBIT + Depreciation – Taxes
OCF = Net Income + Depreciation
OCF = (Sales-Expenses-Depreciation(*(1-T)
+ Depreciation
OCF = (Sales – Expenses)*(1-T) +
(Depreciation)*(T)
Note OCF reflects data from the income
statement,.. balance sheet changes like NWC and ATSV are not included in its
calculation.
Finance Chapter 4 - Questions 1 to 39 Discounted Cash Flow Valuation
Excel File
| Chapter 4 | 1 - 39 | |||||
| 1) | Simple vs Compound Interest | |||||
| r | 0.07 | |||||
| c | 5000 | |||||
| t | 10 | |||||
| Simple Interest | 8500 | =D4*D3*D5+D4 | ||||
| Compound Interest | $9,835.76 | =FV(D3,D5,,-D4) | ||||
| Difference | $1,335.76 | =D7-D6 | ||||
| 2) | Future Values | |||||
| a) | C | 3550 | ||||
| t | 10 | |||||
| r | 0.06 | |||||
| FV | $6,357.51 | =FV(D17,D16,,-D15) | ||||
| b) | C | 3550 | ||||
| t | 10 | |||||
| r | 0.08 | |||||
| FV | $7,664.18 | =FV(D22,D21,,-D20) | ||||
| c) | C | 3550 | ||||
| t | 20 | |||||
| r | 0.06 | |||||
| FV | $11,385.33 | =FV(D27,D26,,-D25) | ||||
| 3) | Present Values | |||||
| Years | interest | FV | PV | |||
| 9 | 0.07 | 15451 | $8,404.32 | =PV(D34,C34,,-E34) | ||
| 13 | 0.09 | 51557 | $16,816.79 | =PV(D35,C35,,-E35) | ||
| 16 | 0.14 | 886073 | $108,890.97 | =PV(D36,C36,,-E36) | ||
| 24 | 0.11 | 550164 | $44,951.14 | =PV(D37,C37,,-E37) | ||
| 4) | Interest Rate | |||||
| PV | Years | FV | Rate | |||
| 217 | 3 | 293 | 10.53% | =RATE(D42,,-C42,E42) | ||
| 432 | 10 | 1053 | 9.32% | =RATE(D43,,-C43,E43) | ||
| 41000 | 16 | 162181 | 8.97% | =RATE(D44,,-C44,E44) | ||
| 54382 | 19 | 483500 | 12.19% | =RATE(D45,,-C45,E45) | ||
| 5) | Periods | |||||
| PV | Rate | FV | Years | |||
| 625 | 0.06 | 1284 | 12.36 | =NPER(D49,,-C49,E49) | ||
| 810 | 0.09 | 4341 | 19.48 | =NPER(D50,,-C50,E50) | ||
| 18400 | 0.07 | 234162 | 37.60 | =NPER(D51,,-C51,E51) | ||
| 21500 | 0.1 | 215000 | 24.16 | =NPER(D52,,-C52,E52) | ||
| 6) | Periods | |||||
| Rate | 0.0575 | |||||
| How long to double? | ||||||
| PV | 1 | |||||
| FV | 2 | |||||
| NPER | 12.39807798 | =NPER(D56,,-D58,D59) | ||||
| 7) | Present Value | |||||
| FV | 540000000 | |||||
| NPER | 20 | |||||
| Rate | 0.056 | |||||
| PV | -$181,599,812.54 | =PV(D66,D65,,D64) | ||||
| 8) | Rates of Return | |||||
| PV | 1680000 | |||||
| FV | 1100000 | |||||
| NPER | 3 | |||||
| Rate | -13% | =RATE(D73,,D71,-D72) | ||||
| 9) | Perpetuities | |||||
| PMT | 80 | |||||
| Rate | 0.026 | |||||
| PV | 3076.92 | =D78/D79 | ||||
| 10) | Continuos Compounding | |||||
| FV = PVe^rt (E is euler number, use EXP in Excel) | ||||||
| PV | Years | Rate | FV | |||
| 1625 | 5 | 0.14 | 3272.35 | =C86*EXP(E86*D86) | ||
| 1625 | 3 | 0.06 | 1945.48 | =C87*EXP(E87*D87) | ||
| 1625 | 10 | 0.08 | 3616.50 | =C88*EXP(E88*D88) | ||
| 1625 | 8 | 0.09 | 3338.45 | =C89*EXP(E89*D89) | ||
| 11) | PV Multiple Cash Flows | |||||
| Years | 1 | 2 | 3 | 4 | ||
| Discount Rate | 585 | 815 | 1630 | 2140 | ||
| 0.05 | $557.14 | $739.23 | $1,408.06 | $1,760.58 | ||
| 0.13 | $517.70 | $638.26 | $1,129.67 | $1,312.50 | ||
| 0.18 | $495.76 | $585.32 | $992.07 | $1,103.79 | ||
| =PV($C$100,D96,,-D97) | ||||||
| 12) | PV Mulitple Cash Flows | |||||
| X | y | |||||
| PMT | 4850 | PMT | 6775 | |||
| NPER | 9 | NPER | 5 | |||
| Rate A | 0.05 | Rate A | 0.05 | |||
| Rate B | 0.21 | Rate B | 0.21 | |||
| Rate A PV | -$34,472.94 | Rate A PV | -$29,332.20 | |||
| Rate B PV | -$18,941.36 | Rate B PV | -$19,823.54 | |||
| Investment X for rate a, investment y for rate B (in this example) | ||||||
| 13) | Annuity Present Value | |||||
| PMT | 5500 | |||||
| R | 0.075 | |||||
| Periods | 15 | 40 | 75 | |||
| Value | -$48,549.16 | -$69,269.25 | -$73,009.99 | =PV($D$120,F121,$D$119) | ||
| Perpetual | (C/r) | |||||
| 73,333.33 | =D119/D120 | |||||
| 14) | Calculating Perpetuity Values | |||||
| C | 18000 | |||||
| R | 0.043 | |||||
| PV | 418,604.65 | =D130/D131 | ||||
| PV | 445000 | |||||
| Rate? | 0.040449438 | =D130/D134 | ||||
| PV = C/R | ||||||
| R = C/PV | ||||||
| 15) | Calculating EAR | |||||
| APR | Compound | |||||
| 0.098 | Quarter | 0.101660685 | =EFFECT(C143,4) | |||
| 0.124 | Monthly | 0.131295813 | =EFFECT(C144,12) | |||
| 0.076 | Daily | 0.078954038 | =EFFECT(C145,365) | |||
| 0.084 | Infinite | 0.087628894 | =EXP(C146)-1 | |||
| EAR continuous - eq-1 | ||||||
| 16) | Calculating APR | |||||
| Compound | EAR | APR | ||||
| Semi Annual | 0.104 | 0.101428086 | =NOMINAL(D153,2) | |||
| Monthly | 0.089 | 0.085563448 | =NOMINAL(D154,12) | |||
| Weekly | 0.116 | 0.109866765 | =NOMINAL(D155,52) | |||
| Infinite | 0.154 | 0.143234168 | =LN(1+D156) | |||
| Infinite compounding ( APR = ln(1 + EAR)) | ||||||
| 17) | Calculating EAR | |||||
| Bank A | Bank B | |||||
| Rate | 0.157 | 0.162 | ||||
| Compound | Monthly | Semi Annual | ||||
| EAR | 0.1688 | 0.1686 | =EFFECT(E162,2) | |||
| 18) | Interest Rates | |||||
| PMT | 10 | |||||
| periods | 12 | |||||
| Discount % | 0.1 | |||||
| Discount$ | 12 | |||||
| Price | 108 | |||||
| (Note - need to ensure beginning period selected) | ||||||
| Interest Rate | 1.98% | =RATE(D170,D169,-D174,,1) | ||||
| APR | 102.77% | =D176*52 | ||||
| EAR | 176.68% | =EFFECT(D177,52) | ||||
| 19) | Caluclating number of periods | |||||
| PMT | 450 | |||||
| Rate | 0.013 | month | ||||
| PV | 18700 | |||||
| NPER | 60.16 | =NPER(D183,-D182,D184) | ||||
| 20) | Calculating EAR | |||||
| PV | 3 | FV=PV(1+r) | ||||
| FV | 4 | r=FV/PV - 1 | ||||
| Rate | 0.333333333 | =D191/D190-1 | ||||
| APR | 1733% | =D192*52 | ||||
| EAR | 313916515.69% | =EFFECT(D193,52) | ||||
| 21) | Future Value | |||||
| PV | 1500 | |||||
| Rate | 0.072 | |||||
| NPER | 6 | |||||
| Compounding | FV | |||||
| Annual | 1 | $2,276.46 | =FV($D$201/D205,$D$202*D205,,-$D$200) | |||
| Semi annual | 2 | $2,293.02 | =FV($D$201/D206,$D$202*D206,,-$D$200) | |||
| Monthly | 12 | $2,307.52 | =FV($D$201/D207,$D$202*D207,,-$D$200) | |||
| Continuously | -- | $2,310.50 | =D200*EXP(D201*D202) | |||
| 22 | Simple Vs Compound Interest | |||||
| Simple Rate | 0.074 | |||||
| Nper | 10 | |||||
| simple rate = (1 + r)Nper -1 | ||||||
| Total Simple interest over 10 years | 0.74 | = (1 + r)Nper -1 | ||||
| 1.74 | =(1+r)10 | |||||
| Compound Rate | 5.70% | |||||
| 23 | Calculating Annuities | |||||
| Nper | 30 | |||||
| PMT | 750 | Stock | ||||
| PMT | 325 | Bond | ||||
| Stock Return APR | 0.105 | |||||
| Bond APR | 0.061 | |||||
| Compound | Monthly | |||||
| After 30 NPER | Sum Both | |||||
| APR | 0.069 | |||||
| Nper | 25 | |||||
| PMT | ? | |||||
| Step 1 | Calculate FV for Bond and Stocks | |||||
| Stocks | $1,887,300.74 | =FV(D226/12,D223*12,-D224) | ||||
| Bonds | $332,782.27 | =FV(D227/12,D223*12,-D225) | ||||
| Total | $2,220,083.01 | =SUM(D237:D238) | ||||
| Step 2 | PMT | -$15,549.74 | =PMT(D231/12,D232*12,D239) | |||
| 24 | Calculating rates of return | |||||
| To triple return | ||||||
| PV | 1 | |||||
| FV | 3 | |||||
| Nper | 12 | |||||
| Return per quarter | 31.61% | =RATE(D248/3,,-D246,D247) | ||||
| 25 | Calculating rates of return | |||||
| A | B | |||||
| PV | 55000 | 55000 | ||||
| Nper | 5 | 11 | ||||
| FV | 105000 | 235000 | ||||
| Rate | 13.81% | 14.11% | =RATE(E258,,-E257,E259) | |||
| 26 | Growing Perpetuities | |||||
| FV | 210000 | PV = C/(r - g) | ||||
| Nper | 3 | |||||
| Growth | 0.025 | |||||
| Discount Rate | 0.11 | |||||
| Step 1 - PV of cash at Nper -1 | ||||||
| 2,470,588.24 | =D265/(D268-D267) | |||||
| PV | $2,005,184.84 | =PV(D268,2,,-D271) | ||||
| 27 | Perpetuities | |||||
| Dividend | 1.75 | |||||
| Quarterly | PV = C / r | |||||
| R | 0.055 | |||||
| Quarterly R | 0.014 | =D279/4 | ||||
| PV | 127.27 | =D277/D280 | ||||
| 28 | Annuity PV | |||||
| PMT | 5700 | |||||
| Receve in | 3 | |||||
| For Nper | 25 | |||||
| Discount | 0.068 | |||||
| Payments | 23 | |||||
| PV of 22 years | $65,363.72 | =PV(D289,D290,-D286) | ||||
| PV | 57,305.23 | =D291/(1+D289)^(D287-1) | ||||
| (need to reduce t by one) | ||||||
| 29 | Annuity Present Values | |||||
| NPER | 15 | |||||
| PMT | 825 | |||||
| Start | 6 | |||||
| Rate 1 | 0.09 | |||||
| Rate 2 | 0.12 | |||||
| PV1 | -$6,650.07 | =PV(D301,D298,D299,) | ||||
| PV2 | 3773.42715 | =-D304/((1+D302)^(D300-1)) | ||||
| 30 | Balloon Payments | |||||
| Cost of Purchase | 825000 | |||||
| Down | 0.2 | |||||
| Amount Borrowed | 660000 | |||||
| APR | 0.054 | |||||
| Years | 30 | |||||
| Periods | 360 | |||||
| PMT | -$3,706.10 | =PMT(D314/12,D316,D313,,0) | ||||
| Balloon Period | 8 | |||||
| pv | $571,856.86 | =PV(D314/12,D316-(D320*12),D317) | ||||
| 31 | Calulating Interest Expense | |||||
| Rate 1 | 0.019 | |||||
| Nper | 6 | months | ||||
| Rate2 | 0.16 | |||||
| Nper | 6 | |||||
| PV | 7500 | |||||
| Rate 1 Period | -$7,571.53 | =FV(D326/12,D327,,D330) | ||||
| Rate 2 Period | $8,197.81 | =FV(D328/12,D329,,D332) | ||||
| Interest | $697.81 | =D333-D330 | ||||
| 32 | Perpetuities | |||||
| PV | 1650000 | PV=C/r | ||||
| PMT | 185000 | r=C/PV | ||||
| Rate? | 0.112121212 | =D341/D340 | ||||
| 11.21% | ||||||
| 33 | Growing Annuity | |||||
| Cost | 135000 | |||||
| PMT | 38000 | |||||
| growth | 0.055 | |||||
| nper | 5 | |||||
| reqr return | 0.11 | |||||
| What is the PV of the cash flows? | C {[1/(r – g)] – [1/(r – g)] × [(1 + g)/(1 + r)]t} | |||||
| PV = | 38000 | 18.18181818 | 18.18181818 | 0.775617152 | ||
| PV | 155,028.15 | |||||
| Compare cost to PV | ||||||
| 34 | ||||||
| PV | 75000 | |||||
| deposit /5 salary | 0.1 | |||||
| rate | 0.095 | |||||
| salary g | 0.034 | |||||
| Nper | 35 | |||||
| C | 7500 | |||||
| C {[1/(r – g)] – [1/(r – g)] × [(1 + g)/(1 + r)]t} | ||||||
| Start with next year deposit | 7755 | 16.39344262 | 16.39344262 | 0.134500744 | ||
| PV | 110,031.91 | =D366*(E366-F366*G366) | ||||
| FV | -$2,636,409.31 | =FV(D361,D363,,D367,0) | ||||
| 35 | Present Value and Interest Rates | |||||
| Nper | 15 | |||||
| PMT | 5250 | |||||
| Rate 1 | 0.1 | -$39,931.92 | =PV(D375,$D$373,$D$374) | |||
| Rate 2 | 0.05 | -$54,493.20 | =PV(D376,$D$373,$D$374) | |||
| Rate 3 | 0.15 | -$30,698.69 | =PV(D377,$D$373,$D$374) | |||
| 36 | Calculating the number of payments | |||||
| PMT | 190 | |||||
| Rate | 0.0875 | |||||
| FV | 25000 | |||||
| Nper | 92.58554581 | =NPER(D382/12,-D381,,D383) | ||||
| 37 | Calculating Annuity Present Value | |||||
| PV | 105000 | |||||
| PMT | 2025 | |||||
| nper | 60 | |||||
| Rate? | 0.49% | =RATE(D391,-D390,D389) | ||||
| APR | 5.90% | =D392*12 | ||||
| 38 | Calculating Loan Payments | |||||
| Nper | 30 | Years | ||||
| PV | 225000 | |||||
| APR | 0.051 | |||||
| PMT | -875 | |||||
| PV PMT | $161,156.71 | =PV(D399/12,D397*12,D400) | ||||
| Balance Remaining | $63,843.29 | =D398-D402 | ||||
| FV Balance Remain | -$293,885.25 | =FV(D399/12,D397*12,,D403) | ||||
| 39 | Present and Future Values | |||||
| Total PV | 5800 | |||||
| Rate | 0.08 | |||||
| Year | Cash Flow | PV | ||||
| 1 | 1300 | $1,203.70 | =D411/(1+$D$408)^C411 | |||
| 2 | ||||||
| 3 | 1900 | $1,508.28 | =D413/(1+$D$408)^C413 | |||
| 4 | 2450 | $1,800.82 | =D414/(1+$D$408)^C414 | |||
| Sum: | $4,512.81 | =SUM(E411:E414) | ||||
| PV Cash2 | $1,287.19 | =D407-E415 | ||||
| FV Cash2 | $1,501.38 | =-FV(D408,C412,,E418) | ||||
Subscribe to:
Comments (Atom)