Baixe o app para aproveitar ainda mais
Leia os materiais offline, sem usar a internet. Além de vários outros recursos!
Planilhas Editáveis do Excel - 700 planilhas
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/01.xlt Hoja1 comissões data funcionario 45% 13-May jose 10% Mar-03 joao 15% Oct-21 maria 5% 10-Nov romario 10% 3-Mar zezinho 15% 14-Apr adalberto 100% Hoja2 Hoja3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/02.xlt Hoja1 LC Tecnocar - Veículos Carros Valor Fiesta * 5,830.00 Mazda * 4,893.00 Fusca * 5,678.00 Corolla * 78,921.00 Fiat 147 * 4,897.00 Gol * 16,544.00 Valor Máximo * 78,921.00 Valor Minimo * 4,893.00 Hoja2 Hoja3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/1.xlt Munka1 jan. febr márc ápr máj jún össz átlag max min Audi 22 34 15 45 38 87 241 40 87 15 Suzuki 19 22 52 34 29 45 201 34 52 19 Skoda 18 22 37 64 99 12 252 42 99 12 össz 59 78 104 143 166 144 1388 - - - átlag 20 26 35 48 55 48 - 39 - - max 22 34 52 64 99 87 - - 99 - min 18 22 15 34 29 12 - - - 12 Munka2 5 6 a1+b1 11 a1-b1 -1 b1-a1 1 a1*b1 30 a1/b1 0.8333333333 b1/a1 1.2 Munka3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/10.xls Plan1 Manutenção de hidrante 47 Custos Gerais 654 Valores 156 Outros Valores 2000 2857 Total MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/10.xlt Munka1 Valutaárfolyamok január február március április május június átlag maximum GBP 320 330 325 340 350 370 FRF 33 34 35 40 39 40 CHF 135 140 141 145 460 150 Százalékos változás GBP FRF CHF Munka2 Munka3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/11.xls Plan1 Tabela de Natal Parente Valor do Presente Rafael * (20.00) Dennys * 8,000.00 Danilo * 70.00 José * 800.00 Joker * (1,000.00) * 7,850.00 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/11.xlt Munka1 Arucikk ITJ szám ÁFA kategória Mennyiség Beszerzéis ár Nettó eladási ár ÁFA Törzsvásárlói ár Viszinteladói ár Bruttó eladási ár kenyér 15-55 1 53.0 75.0 tej 15-56 2 47.0 69.0 vaj 15-57 1 240.0 10.0 pűrizsi 15-58 3 60.0 485.0 Arengedmények: ÁFA kategóriák Törzsvásárlói 8% 1 25% Viszonteladói 17% 2 18% 3 13% Haszonkulcs 23% Munka2 Munka3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/12.xlt Munka1 Kölcsönvevő neve Felvét ideje Felvett összeg Mai dátum Visszafizetett összeg Tartozás Tartozás összege Sós Elek 10/1/97 50,000 Ft 9/21/97 0 Ft nincs Karvay Antal 12/14/96 650,000 Ft 9/21/97 72,000 Ft van 578,000 Ft Linka Edit 1/2/97 750,000 Ft 9/21/97 750,000 Ft nincs Sávos Olga 5/2/97 80,000 Ft 9/21/97 60,000 Ft van 20,000 Ft Kender Ilona 8/4/97 750,000 Ft 9/21/97 52,000 Ft nincs Csizmadia Zsolt 4/1/88 1,000,000 Ft 4/1/03 1 Ft van 999,999 Ft Pető Géza 7/30/97 95,000 Ft 9/21/97 0 Ft nincs Munka1 Munka2 Munka3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/13.xlt Munka1 Név Betegség Kezelés kezdete Kezelés vége Külön szoba Fizetendő Tatai Ákos lépfene 8/5/97 8/15/97 kér 31,250 Ft Belviz Kinga vándorvese 10/1/97 10/10/97 kér 28,125 Ft Hanta Pál vízrekedés 11/27/97 12/5/97 nem kér 20,000 Ft Habarcs Oszkár fandliiszony 1/23/98 1/30/98 nem kér 17,500 Ft Gong Olga szemellenzisz 3/21/98 3/27/98 kér 18,750 Ft Tang Orsolya ostobisztisz 5/22/98 6/22/98 nem kér 77,500 Ft Napi ár: 2,500 Ft Egyágyas felár 25% Munka2 Munka3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/14.xlt Munka1 Alkalmazott neve Végzettsége Munkába állás ideje Mai dátum Besorolás betűje Besorolás száma Fizetés Tarant Ella Nyolc általános 4/5/75 10/28/97 A 6 29,000 Ft Budipa Piroska Főiskola 10/2/78 10/28/97 D 6 46,400 Ft Bőrönd Ödön Érettségi 11/8/85 10/28/97 C 4 32,480 Ft Whinc Eszter Egyetem 1/4/77 10/28/97 E 6 52,200 Ft Trab Antal Szakmunkás 10/1/89 10/28/97 B 3 24,360 Ft Bekő Tóni Egyetem 1/4/96 10/28/97 E 1 26,100 Ft Benkő Hanni Főiskola 4/5/92 10/28/97 D 2 27,840 Ft Holkő Hanni Akadémia 1/1/97 10/28/97 F 1 29,000 Ft Szorzók: Alap: 14,500 Ft A 1 1 1 B 1.2 2 1.2 C 1.4 3 1.4 D 1.6 4 1.6 E 1.8 5 1.8 F 2 6 2 Munka2 Munka3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/2.xlt Munka1 összeg átlag legnagyob legkisebb darab üres 3-as legfeljebb 3-as legalább 3-as nem 3-as 3 8 3 53 4.0769230769 8 1 13 3 5 7 11 8 2 5 3 4 7 3 7 4 1 3 Munka2 Munka3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/283wp.xlt Sheet1 Debit Date CapIT Corporation Accounts (Credit) Balance Case: CapIT Corporation Case on Applying SFAS 133 to a Euro- dollar Option Used to Cap a Forecasted Loan Transaction 1999 June 17 Cash (53,125) (53,125) Author: Bob Jensen at Trinity University http://www.trinity.edu/rjensen Options contracts 53,125 53,125 Interest expense (revenue) Abstract: Other comprehensive income (OCI) --To record purchase of 50 put option contracts June 30 Cash (53,125) Options contracts (18,750) 34,375 Interest expense (revenue) (6,250) (6,250) Other comprehensive income (OCI) 25,000 25,000 On June 17, 1999 CapIT Corporation had a forecasted transaction to borrow $25 million in three --To record value adjustment of option contracts months at a variable rate. The CFO of Capit, Todd Evert, commenced to worry about rising interest rates following the cessation of NATO bombing in Kosovo. Signs pointed to rising prices that may July 31 Cash (53,125) lead to world wide rises in borrowing costs. Options contracts 26,250 60,625 Interest expense (revenue) (8,750) (15,000) Todd decided to purchase 25 September Eurodollar interest rate options in order to place a cap Other comprehensive income (OCI) (17,500) 7,500 --To record value adjustment of option contracts Aug. 31 Cash (53,125) LIBOR Premium % Premium $ Loan Rate Options contracts (5,000) 55,625 4.90% 0.85% $53,125 6.20% Interest expense (revenue) 51,875 36,875 5.00% 0.85% $53,125 6.30% Other comprehensive income (OCI) (46,875) (39,375) 5.10% 0.85% $53,125 6.40% --To record value adjustment of option contracts 5.20% 0.85% $53,125 6.50% 5.30% 0.85% $53,125 6.60% Sept. 17 Cash 62,500 9,375 5.40% 0.85% $53,125 6.70% Options contracts (55,625) 0 5.50% 0.85% $53,125 6.80% Interest expense (revenue) (36,875) 0 5.60% 0.85% $53,125 6.80% Other comprehensive income (OCI) 30,000 (9,375) 5.70% 0.85% $53,125 6.80% --To record option contract settlement 5.80% 0.85% $53,125 6.80% 5.90% 0.85% $53,125 6.80% Sept. 17 Cash 25,000,000 25,009,375 6.00% 0.85% $53,125 6.80% Options contracts 0 6.10% 0.85% $53,125 6.80% Notes payable (25,000,000) (25,000,000) 6.20% 0.85% $53,125 6.80% Interest expense (revenue) 0 6.30% 0.85% $53,125 6.80% Other comprehensive income (OCI) (9,375) 6.40% 0.85% $53,125 6.80% --To record loan transaction for $25 million LIBOR 9425 Strike 9450 Strike 9475 Strike Dec. 17 Cash (25,434,375) (425,000) 3.00% 3.45% 3.45% 3.45% Options contracts 0 0 3.50% 3.95% 3.95% 3.95% Notes payable 25,000,000 0 4.00% 4.45% 4.45% 4.45% Interest expense (revenue) 425,000 425,000 4.50% 4.95% 4.95% 4.95% Other comprehensive income (OCI) 9,375 0 5.00% 5.45% 5.45% 5.45% --To record payoff of $25 million loan 5.50% 5.95% 5.95% 5.95% 6.00% 6.45% 6.45% 6.45% 6.50% 6.45% 6.80% 6.95% 7.00% 6.45% 6.80% 7.45% CapIT Corporation Summary 7.50% 6.45% 6.80% 7.95% Interest expense without hedging $434,375 8.00% 6.45% 6.80% 8.00% Net income from put option hedges (9,375) 8.50% 6.45% 6.80% 8.00% Interest expense with hedging $425,000 9.00% 6.45% 6.80% 8.00% 9.50% 6.45% 6.80% 8.00% 10.00% 6.45% 6.80% 8.00% 5.75% 0.25% $15,625 6.45% 5.50% 0.85% $53,125 6.80% 5.25% 2.30% $143,750 8.00% 5.00% 4.45% $278,125 9.90% 4.75% 6.90% $431,250 12.10% 4.50% 9.40% $587,500 14.35% Sheet1 5.50% strike rate LIBOR Exhibit 2: Capped Loan Rate on a 9,450 June 17 Put Option Strike Price Sheet2 LIBOR% 5.75 6.80 8.00 9425 Strike 9450 Strike 9475 Strike Exhibit 3: Comparison of Capped Loan Rates of Three Put Option Strike Prices Sheet3 Teaching Note With Answers to Questions Case: CapIT Corporation Case on Applying SFAS 133 to a Euro- dollar Option Used to Cap a Forecasted Loan Transaction Author: Bob Jensen at Trinity University http://www.trinity.edu/rjensen MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/284wp.xlt Sheet1 Debit Date Accounts Debited or (Credited) (Credit) Balance Case: CapIT Corporation Case on Applying SFAS 133 to a Euro- dollar Option Used to Cap a Forecasted Loan Transaction June 17 Cash (90,625) (90,625) Author: Bob Jensen at Trinity University http://www.trinity.edu/rjensen Options contracts 90,625 90,625 Interest expense (revenue) Absract: Other comprehensive income (OCI) --To record purchase of 50 call option contracts June 30 Cash (90,625) Options contracts 8,125 98,750 Interest expense (revenue) 16,875 16,875 Other comprehensive income (OCI) (25,000) (25,000) On June 17, 1999 CapIT Corporation had a forecasted transaction to borrow $25 million in three --To record value adjustment of option contracts months at a variable rate. The CFO of Capit, Todd Evert, commenced to worry about rising interest rates following the cessation of NATO bombing in Kosovo. Signs pointed to rising prices that may July 31 Cash (90,625) lead to world wide rises in borrowing costs. Options contracts (30,000) 68,750 Interest expense (revenue) 12,500 29,375 Todd decided to purchase 25 September Eurodollar interest rate options in order to place a cap Other comprehensive income (OCI) 17,500 (7,500) --To record value adjustment of option contracts Aug. 31 Cash (90,625) LIBOR Premium % Premium $ Loan Rate Options contracts (12,500) 56,250 2.55% 1.45% $90,625 4.50% Interest expense (revenue) 55,000 84,375 3.05% 1.45% $90,625 4.50% Other comprehensive income (OCI) (42,500) (50,000) 3.55% 1.45% $90,625 4.50% --To record value adjustment of option contracts 4.05% 1.45% $90,625 4.50% 4.55% 1.45% $90,625 5.00% Sept. 17 Cash 93,750 3,125 5.05% 1.45% $90,625 5.50% Options contracts (56,250) 0 5.55% 1.45% $90,625 6.00% Interest expense (revenue) (84,375) 0 6.05% 1.45% $90,625 6.50% Other comprehensive income (OCI) 46,875 (3,125) 6.55% 1.45% $90,625 7.00% --To record option contract settlement 7.05% 1.45% $90,625 7.50% 7.55% 1.45% $90,625 8.00% Sept. 17 Cash (25,000,000) (24,996,875) 8.05% 1.45% $90,625 8.50% Options contracts 8.55% 1.45% $90,625 9.00% Notes receivable 25,000,000 25,000,000 9.05% 1.45% $90,625 9.50% Interest expense (revenue) 0 0 9.55% 1.45% $90,625 10.00% Other comprehensive income (OCI) 0 (3,125) 10.05% 1.45% $90,625 10.50% --To record loan transaction for $25 million LIBOR 9425 Strike 9450 Strike 9475 Strike Dec. 17 Cash 25,278,125 281,250 2.00% 2.90% 4.50% 5.30% Options contracts 2.50% 2.90% 4.50% 5.30% Notes receivable (25,000,000) 3.00% 3.45% 4.50% 5.30% Interest expense (revenue) (281,250) (281,250) 3.50% 3.95% 4.50% 5.30% Other comprehensive income (OCI) 3,125 0 4.00% 4.45% 4.50% 5.30% --To record payoff of $25 million loan 4.50% 4.95% 4.95% 5.30% 5.00% 5.45% 5.45% 5.45% 5.50% 5.95% 5.95% 5.95% 6.00% 6.45% 6.45% 6.45% 6.50% 6.95% 6.95% 6.95% FloorIT Bank Summary 7.00% 7.45% 7.45% 7.45% Interest revenue without hedging $278,125 7.50% 7.95% 7.95% 7.95% Net income from call option hedges 3,125 8.00% 8.45% 8.45% 8.45% Interest revenue with hedging $281,250 8.50% 8.95% 8.95% 8.95% 9.00% 9.45% 9.45% 9.45% 5.75% 0.25% $15,625 6.45% 5.50% 0.85% $53,125 6.80% 5.25% 2.30% $143,750 8.00% 5.00% 4.45% $278,125 9.90% 4.75% 6.90% $431,250 12.10% 4.50% 9.40% $587,500 14.35% Sheet1 0.045 0.045 0.045 0.045 0.05 0.055 0.06 0.065 0.07 0.075 0.08 0.085 0.09 0.095 0.1 0.105 4.50% LIBOR Rate Exhibit 2: Floored Loan Rate on a 9,450 June 17 Call Option Strike Price Sheet2 0.029 0.045 0.053 0.029 0.045 0.053 0.0345 0.045 0.053 0.0395 0.045 0.053 0.0445 0.045 0.053 0.0495 0.0495 0.053 0.0545 0.0545 0.0545 0.0595 0.0595 0.0595 0.0645 0.0645 0.0645 0.0695 0.0695 0.0695 0.0745 0.0745 0.0745 0.0795 0.0795 0.0795 0.0845 0.0845 0.0845 0.0895 0.0895 0.0895 0.0945 0.0945 0.0945 LIBOR% 2.90 4.50 5.30 9425 Strike 9450 Strike 9475 Strike Exhibit 3: Comparison of Floor Loan Rates of Three Call Option Strike Prices Sheet3 Teaching Note With Answers to Questions Case: CapIT Corporation Case on Applying SFAS 133 to a Euro- dollar Option Used to Cap a Forecasted Loan Transaction Author: Bob Jensen at Trinity University http://www.trinity.edu/rjensen MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/284wp2.xlt Sheet1 Case: CapIT Corporation Case on Applying SFAS 133 to a Euro- dollar Option Used to Cap a Forecasted Loan Transaction Author: Bob Jensen at Trinity University http://www.trinity.edu/rjensen Absract: On June 17, 1999 CapIT Corporation had a forecasted transaction to borrow $25 million in three months at a variable rate. The CFO of Capit, Todd Evert, commenced to worry about rising interest rates following the cessation of NATO bombing in Kosovo. Signs pointed to rising prices that may lead to world wide rises in borrowing costs. Todd decided to purchase 25 September Eurodollar interest rate options in order to place a cap LIBOR Premium % Premium $ Loan Rate 4.25% 1.45% $90,625 4.50% 4.50% 1.45% $90,625 4.50% 4.75% 1.45% $90,625 4.50% 5.00% 1.45% $90,625 4.50% 5.25% 1.45% $90,625 4.50% 5.50% 1.45% $90,625 4.50% 5.75% 1.45% $90,625 4.75% 6.00% 1.45% $90,625 5.00% 6.25% 1.45% $90,625 5.25% 6.50% 1.45% $90,625 5.50% 6.75% 1.45% $90,625 5.75% 7.00% 1.45% $90,625 6.00% 7.25% 1.45% $90,625 6.25% 7.50% 1.45% $90,625 6.50% 7.75% 1.45% $90,625 6.75% 8.00% 1.45% $90,625 7.00% LIBOR 9425 Strike 9450 Strike 9475 Strike 3.00% 2.90% 4.50% 5.30% 3.50% 2.90% 4.50% 5.30% 4.00% 2.90% 4.50% 5.30% 4.50% 2.90% 4.50% 5.30% 5.00% 2.90% 4.50% 5.30% 5.50% 4.50% 4.50% 5.30% 6.00% 5.00% 5.00% 5.30% 6.50% 5.50% 5.50% 5.50% 7.00% 6.00% 6.00% 6.00% 7.50% 6.50% 6.50% 6.50% 8.00% 7.00% 7.00% 7.00% 8.50% 7.50% 7.50% 7.50% 9.00% 8.00% 8.00% 8.00% 9.50% 8.50% 8.50% 8.50% 10.00% 9.00% 9.00% 9.00% 5.75% 0.25% $15,625 6.45% 5.50% 0.85% $53,125 6.80% 5.25% 2.30% $143,750 8.00% 5.00% 4.45% $278,125 9.90% 4.75% 6.90% $431,250 12.10% 4.50% 9.40% $587,500 14.35% Sheet1 5.50% LIBOR Rate Exhibit 2: Floored Loan Rate on a 9,450 June 17 Call Option Strike Price Sheet2 LIBOR% 5.25 5.50 5.75% 9425 Strike 9450 Strike 9475 Strike Exhibit 3: Comparison of Floor Loan Rates of Three Call Option Strike Prices Sheet3 Teaching Note With Answers to Questions Case: CapIT Corporation Case on Applying SFAS 133 to a Euro- dollar Option Used to Cap a Forecasted Loan Transaction Author: Bob Jensen at Trinity University http://www.trinity.edu/rjensen MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/285adj.xlt Sheet1 Periodic interest rate (a) 0.01644% daily rate 0.01644% alternate daily rate WP 285 MarginWHEW Units in contract (b) 2500 Contracts (c) 10 Total quantity (d) 25000 December 17, 1999 03/17/00 06/17/00 09/17/00 Future Contract Price 5.88 6.05 6.20 Original Spot Price 5.75 5.75 5.75 Days to Expiration (e) 91.00 183.00 275.00 December 31, 1999 Future price 5.90 6.09 6.23 December 31, 1999 December 31, 1999 Spot price 5.79 5.79 5.79 Add Adjusted MarginWHEW days from 12/17/99 (f) 14 14 14 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 494 * 973 * 719 Account Account Unadjusted Time Value FVd(spot) (h) * 987 * 973 * 958 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (493) 0.0 * (239) dFVd(fut) (j) * 494 * 973 * 719 $2,186 (FC Asset/liab effect) $500 $2,686 =Margin Account $2,750 $64 dFVd(spot) (j) (987) (973) (958) ($2,918) (OCI effect) ($2,918) =OCI ($2,250) dFVd(fut-spot) (j) 493 0 239 $732 (Net Income effect) $732 =Retained earnings $0 January 31, 2000 Future price 6.10 6.18 6.26 January 31, 2000 January 31, 2000 Spot price 5.89 5.89 5.89 Add Adjusted MarginWHEW days from 12/17/99 (f) 45 45 45 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 5,459 * 3,177 * 1,444 Account Account Unadjusted Time Value FVd(spot) (h) * 3,474 * 3,422 * 3,370 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * 1,985 * (245) * (1,926) dFVd(fut) (j) * 5,459 * 3,177 * 1,444 $10,080 (FC Asset/liab effect) $500 $10,580 =Margin Account $10,750 $170 dFVd(spot) (j) (3,474) (3,422) (3,370) ($10,266) (OCI effect) ($10,266) =OCI ($10,250) dFVd(fut-spot) (j) (1,985) 245 1,926 $186 (Net Income effect) $186 =Retained earnings $0 February 29, 2000 Future price 6.50 6.59 6.69 February 29, 2000 February 29, 2000 Spot price 6.45 6.45 6.45 Add Adjusted MarginWHEW days from 12/17/99 (f) 74 74 74 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 15,457 * 13,260 * 11,852 Account Account Unadjusted Time Value FVd(spot) (h) * 17,451 * 17,189 * 16,931 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (1,994) * (3,929) * (5,079) dFVd(fut) (j) * 15,457 * 13,260 * 11,852 $40,569 (FC Asset/liab effect) $500 $41,069 =Margin Account $41,750 $681 dFVd(spot) (j) (17,451) (17,189) (16,931) ($51,571) (OCI effect) ($51,571) =OCI ($41,250) dFVd(fut-spot) (j) 1,994 3,929 5,079 $11,002 (Net Income effect) $11,002 =Retained earnings $0 March 17, 2000 Future price 7.08 7.15 March 17, 2000 March 17, 2000 Spot price 7.02 7.02 Add Adjusted MarginWHEW days from 12/17/99 (f) 91 91 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 25,364 * 23,042 Account Account Unadjusted Time Value FVd(spot) (h) * 31,273 * 30,804 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (5,909) * (7,762) dFVd(fut) (j) * 25,364 * 23,042 $48,406 (FC Asset/liab effect) $500 $48,906 =Margin Account $50,000 $1,094 dFVd(spot) (j) (31,273) (30,804) ($62,077) (OCI effect) ($62,077) =OCI ($49,500) dFVd(fut-spot) (j) 5,909 7,762 $13,671 (Net Income effect) $13,671 =Retained earnings $0 March 31, 2000 Future price 7.10 7.17 March 31, 2000 March 31, 2000 Spot price 7.06 7.06 Add Adjusted MarginWHEW days from 12/17/99 (f) 105 105 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 25,916 * 23,582 Account Account Unadjusted Time Value FVd(spot) (h) * 32,333 * 31,848 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (6,417) * (8,266) dFVd(fut) (j) * 25,916 * 23,582 $49,498 (FC Asset/liab effect) $500 $49,998 =Margin Account $51,000 $1,002 dFVd(spot) (j) (32,333) (31,848) ($64,181) (OCI effect) ($64,181) =OCI ($50,500) dFVd(fut-spot) (j) 6,417 8,266 $14,683 (Net Income effect) $14,683 =Retained earnings $0 April 30, 2000 Future price 7.12 7.20 April 30, 2000 April 30, 2000 Spot price 7.09 7.09 Add Adjusted MarginWHEW days from 12/17/99 (f) 135 135 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 26,540 * 24,431 Account Account Unadjusted Time Value FVd(spot) (h) * 33,237 * 32,738 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (6,697) * (8,307) dFVd(fut) (j) * 26,540 * 24,431 $50,971 (FC Asset/liab effect) $500 $51,471 =Margin Account $52,250 $779 dFVd(spot) (j) (33,237) (32,738) ($65,975) (OCI effect) ($65,975) =OCI ($51,750) dFVd(fut-spot) (j) 6,697 8,307 $15,004 (Net Income effect) $15,004 =Retained earnings $0 May 31, 2000 Future price 7.14 7.23 May 31, 2000 May 31, 2000 Spot price 7.12 7.12 Add Adjusted MarginWHEW days from 12/17/99 (f) 166 166 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 27,174 * 25,293 Account Account Unadjusted Time Value FVd(spot) (h) * 34,154 * 33,642 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (6,980) * (8,349) dFVd(fut) (j) * 27,174 * 25,293 $52,467 (FC Asset/liab effect) $500 $52,967 =Margin Account $53,500 $533 dFVd(spot) (j) (34,154) (33,642) ($67,796) (OCI effect) ($67,796) =OCI ($53,000) dFVd(fut-spot) (j) 6,980 8,349 $15,329 (Net Income effect) $15,329 =Retained earnings $0 June 17, 2000 Future price 7.25 June 17, 2000 June 17, 2000 Spot price 7.20 Add Adjusted MarginWHEW days from 12/17/99 (f) 183 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 25,856 Account Account Unadjusted Time Value FVd(spot) (h) * 35,706 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (9,850) dFVd(fut) (j) * 25,856 $25,856 (FC Asset/liab effect) $500 $26,356 =Margin Account $26,750 $394 dFVd(spot) (j) (35,706) ($35,706) (OCI effect) ($35,706) =OCI ($26,250) dFVd(fut-spot) (j) 9,850 $9,850 (Net Income effect) $9,850 =Retained earnings $0 June 30, 2000 Future price 7.28 June 30, 2000 June 30, 2000 Spot price 7.26 Add Adjusted MarginWHEW days from 12/17/99 (f) 196 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 26,652 Account Account Unadjusted Time Value FVd(spot) (h) * 37,263 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (10,611) dFVd(fut) (j) * 26,652 $26,652 (FC Asset/liab effect) $500 $27,152 =Margin Account $27,500 $348 dFVd(spot) (j) (37,263) ($37,263) (OCI effect) ($37,263) =OCI ($27,000) dFVd(fut-spot) (j) 10,611 $10,611 (Net Income effect) $10,611 =Retained earnings $0 July 31, 2000 Future price 7.30 July 31, 2000 July 31, 2000 Spot price 7.27 Add Adjusted MarginWHEW days from 12/17/99 (f) 227 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 27,284 Account Account Unadjusted Time Value FVd(spot) (h) * 37,701 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (10,417) dFVd(fut) (j) * 27,284 $27,284 (FC Asset/liab effect) $500 $27,784 =Margin Account $28,000 $216 dFVd(spot) (j) (37,701) ($37,701) (OCI effect) ($37,701) =OCI ($27,500) dFVd(fut-spot) (j) 10,417 $10,417 (Net Income effect) $10,417 =Retained earnings $0 August 31, 2000 Future price 7.33 August 31, 2000 August 31, 2000 Spot price 7.31 Add Adjusted MarginWHEW days from 12/17/99 (f) 258 Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) * 28,171 Account Account Unadjusted Time Value FVd(spot) (h) * 38,891 Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) * (10,720) dFVd(fut) (j) * 28,171 $28,171 (FC Asset/liab effect) $500 $28,671 =Margin Account $28,750 $79 dFVd(spot) (j) (38,891) ($38,891) (OCI effect) ($38,891) =OCI ($28,250) dFVd(fut-spot) (j) 10,720 $10,720 (Net Income effect) $10,720 =Retained earnings $0 September 17, 2000 Future price September 17, 2000 September 17, 2000 Spot price Add Adjusted MarginWHEW days from 12/17/99 (f) Margin Cumulative Exhibit 4 Exhibit 4 FVd(fut) (g) Account Account Unadjusted Time Value FVd(spot) (h) Dr/(Cr) Deposit Balance Balance Difference FVd(fut-spot) (i) dFVd(fut) (j) $0 (FC Asset/liab effect) $500 $0 =Margin Account $0 $0 dFVd(spot) (j) $0 (OCI effect) $0 =OCI $0 dFVd(fut-spot) (j) $0 (Net Income effect) $0 =Retained earnings $0 Cash Flows on Hedged Transaction Increase (Decrease) Lesser of the Two Change Cumulative Balance to Cumulative During Cumulative Changes OCI Contract and Period End Change Period Change (AOCI Bal.) Dr (Cr) July Soybeans June 30 * 987 0.0 0.0 0.0 0.0 August Soybeans June 30 * 973 0.0 0.0 0.0 0.0 July 31 * 3,422 0.0 0.0 0.0 0.0 September Soybeans June 30 * 958 0.0 0.0 0.0 0.0 July 31 * 3,370 0.0 0.0 0.0 0.0 August 31 * 16,931 0.0 0.0 0.0 0.0 August Soy Oil June 30 0.0 0.0 0.0 0.0 0.0 July 31 0.0 0.0 0.0 0.0 0.0 September Soy Oil June 30 * 7 0.0 0.0 0.0 0.0 July 31 * 7 0.0 0.0 0.0 0.0 August 31 * 7 0.0 0.0 0.0 0.0 October Soy Oil June 30 * 7 0.0 0.0 0.0 0.0 July 31 * 7 0.0 0.0 0.0 0.0 August 31 * 7 0.0 0.0 0.0 0.0 September 30 * 7 0.0 0.0 0.0 0.0 August Soy Meal June 30 0.0 0.0 0.0 0.0 0.0 July 31 0.0 0.0 0.0 0.0 0.0 September Soy Meal June 30 0.0 0.0 0.0 0.0 0.0 July 31 0.0 0.0 0.0 0.0 0.0 August 31 0.0 0.0 0.0 0.0 0.0 October Soy Meal June 30 0.0 0.0 0.0 0.0 0.0 July 31 0.0 0.0 0.0 0.0 0.0 August 31 0.0 0.0 0.0 0.0 0.0 September 30 0.0 0.0 0.0 0.0 0.0 This spreadsheet accompanies "Introductory Cases on Accounting for Derivative Instruments and Hedging Activities " by Walter Teets and Robert Uhl. It provides futures values and changes in values needed for Account entries in the case "C.L. Smith and Sons: Accounting for Futures Hedging Commodity Purchases and Sales." For an explanation of particular items in this spreadsheet, please refer to the case text. Named areas of the spreadsheet (accessible through F5 key) are "Instructions," "Futures_Pricing," "Atlanta_Spot_Prices," and "OCI_Table." Pricing of futures involves many factors not included in this spreadsheet. Therefore, this spreadsheet should not be used as a substitute for consulting with a valuation expert. Both Walter Teets and Robert Uhl were associated with the U.S. Securities and Exchange Commission during the time the project "Introductory Cases on Accounting for Derivative Instruments and Hedging Activities" was developed. The Securities and Exchange Commission, as a matter of policy, disclaims responsibility for any private publication or statement by any of its employees. The views expressed herein are those of the authors and do not necessarily reflect the views of the Commission or of the authors' colleagues upon the staff of the Commission. MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/285wp.xlt Sheet1 285wp $10,000,000 = Notional 10 =contracts $2,500 = scale factor 8.00% = loan rate (APR) 4 = quarterly 12/17/99 5.75% 0.2527777778 1.0145347222 1.0143750 6.00% = accounting discount rate (APR) 3/17/00 5.88% 0.2527777778 1.0148633333 1.0147000 365 = number of compoundings 6/17/00 6.05% 0.2527777778 1.0152930556 1.0151250 0.016438% = accounting discount rate 9/17/00 6.20% 0.2527777778 1.0156722222 1.0155000 0 = zero discount rate cc rate = 6.117871% 6.021321% 3 = number of settlements 0.0000% = Note payable increment above spot rates The number of days to futures contract expiration = 91 183 275 September Contracts December Contracts March Contracts Spot Rates Futures Exp. ------> Dec. 1999 March 2000 Yield June 2000 Yield Sept. 2000 Yield Futures Exp. ------> Options Contract Value Change in Value New Margin Balance Margin Deposit Margin Deposit Margin Deposit Note Receivable Rate Note Payable Rate (91/360) Adjustment Factor (364/365) cc Rate Factor (90/360) Adjustment Factor (360/365) cc Rate Factor Note Payable Rate Spot Yield Yield Spot Yield Yield Spot Yield Yield 5.75 December 17, 1999 5.75 5.88 6.05 6.20 December 17, 1999 $0 $0 $0 500 500 0.08 0.0575 0.2527778 0.997260274 0.2500 0.9863 5.75 5.88 5.75 6.05 5.75 6.20 5.79 December 31, 1999 5.75 5.90 6.09 6.23 December 31, 1999 $2,250 $2,250 $2,750 0 $2,750 0.2527778 0.2500 5.79 5.90 5.79 6.09 5.79 6.23 5.89 January 31, 2000 5.75 6.10 6.18 6.26 January 31, 2000 $10,250 $8,000 $10,750 0 10750 0.2527778 0.2500 5.89 6.10 5.89 6.18 5.89 6.26 6.45 February 29, 2000 5.75 6.50 6.59 6.69 February 29, 2000 $41,250 $31,000 $41,750 0 41750 0.2527778 0.2500 6.45 6.50 6.45 6.59 6.45 6.69 7.02 March 17, 2000 5.75 7.02 7.08 7.15 March 17, 2000 $78,000 $36,750 $78,500 -28500 50000 $50,000 0.08 0.0575 0.2527778 0.2500 0.0145347222 7.02 7.02 7.02 7.08 7.02 7.15 7.06 March 31, 2000 5.75 5.88 7.10 7.17 March 31, 2000 $50,500 -$27,500 $51,000 0 $51,000 0.2527778 0.2500 7.06 7.10 7.06 7.17 7.09 April 30, 2000 5.75 5.88 7.12 7.20 April 30, 2000 $51,750 $1,250 $52,250 0 $52,250 0.2527778 0.2500 7.09 7.12 7.09 7.20 7.12 May 31, 2000 5.75 5.88 7.14 7.23 May 31, 2000 $53,000 $1,250 $53,500 0 $53,500 0.0651 0.2527778 0.2500 7.12 7.14 7.12 7.23 7.20 June 17, 2000 5.75 5.88 7.20 7.25 June 17, 2000 $55,000 $2,000 $55,500 -28750 $26,750 $26,750 0.08 0.0702 0.2527778 0.2500 0.017745 7.20 7.20 7.20 7.25 7.22 June 30, 2000 5.75 5.88 6.05 7.28 June 30, 2000 $27,000 -$28,000 $27,500 $27,500 0 $27,500 0.2527778 0.2500 7.22 7.28 7.25 July 31, 2000 5.75 5.88 6.05 7.30 July 31, 2000 $27,500 $500 $28,000 $28,000 0 $28,000 0.2527778 0.2500 7.25 7.30 7.27 August 31, 2000 5.75 5.88 6.05 7.33 August 31, 2000 $28,250 $750 $28,750 $28,750 0 $28,750 0.0720 0.2527778 0.2500 7.27 7.33 7.31 September 17, 2000 5.75 5.88 6.05 7.34 September 17, 2000 $28,500 $250 $29,000 $29,000 -$29,000 $0 0.08 0.0720 0.2527778 0.2500 0.0182 7.31 7.34 7.34 September 30, 2000 5.75 5.88 6.05 6.20 September 30, 2000 $0 -$29,000 -$29,000 $0 0.2527778 0.2500 October 31, 2000 5.75 5.88 6.05 6.20 October 31, 2000 $0 $0 $0 $0 0.2527778 0.2500 November 30, 2000 5.75 5.88 6.05 6.20 November 30, 2000 $0 $0 $0 $0 0.0734 0.2527778 0.2500 December 17, 2000 5.75 5.88 6.05 6.20 December 17, 2000 $0 $0 $0 $0 0.08 0.0731 0.2527778 0.2500 0.0184780556 Debit Debit -$500 -$28,000 -$28,750 -$29,000 Date MarginWHEW Bank Accounts (Credit) Balance (Credit) Balance $85,750 1999 Assumed FAS 133 discount rate = 0.000000% 0.000000% 0.016438% 0.016438% 17-Dec Cash 10,000,000 10,000,000 10,000,000 10,000,000 Notes payable (10,000,000) (10,000,000) (10,000,000) (10,000,000) -To record borrowing for one year 17-Dec Cash (10,000,000) 0 (10,000,000) 0 Notes receivable 10,000,000 0 10,000,000 0 -To record lending for one year 17-Dec Cash (500) (500) (500) (500) Futures margin account 500 500 500 500 -To record margin account Adjustment to Adjustment to Interest 12/31/99 12/31/99 Margin OCI Expense 31-Dec Cash 0 (500) 0 (500) Balance Balance (Revenue) Futures margin account 2,250 2,750 2,186 2,686 494 (987) 493 Other comprehensive income (OCI) (3,000) (3,000) (2,918) (2,918) 973 (973) 0 Interest expense (revenue) 750 750 732 732 719 (958) 239 - To adjust futures contracts to fair value. 14 = Number of days since purchase of futures contracts 01/31/00 1/31/00 Adj. To Margin Adj. To OCI Int. Exp. 31-Jan Cash 0 (500) 0 (500) Balance Balance (Revenue) Futures margin account 8,000 10,750 7,894 10,580 5459 (3,474) (1,985) Other comprehensive income (OCI) (7,500) (10,500) (7,348) (10,266) 3177 (3,422) 245 Interest expense (revenue) (500) 250 (546) 186 1444 (3,370) 1,926 - To adjust futures contracts to fair value. 45 = Number of days since purchase of futures contracts 02/29/00 2/29/00 Adj. To Margin Adj. To OCI Int. Exp. 29-Feb Cash 0 (500) 0 (500) Balance Balance (Revenue) Futures margin account 31,000 41,750 30,489 41,069 15457 (17,451) 1,994 Other comprehensive income (OCI) (42,000) (52,500) (41,305) (51,571) 13260 (17,189) 3,929 Interest expense (revenue) 11,000 11,250 10,816 11,002 11852 (16,931) 5,079 - To adjust futures contracts to fair value. 74 = Number of days since purchase of futures contracts 03/17/00 3/17/00 Adj. To Margin Adj. To OCI Int. Exp. 17-Mar Cash 28,500 28,000 28,500 28,000 Balance Balance (Revenue) Futures margin account 8,250 50,000 7,837 48,906 0 0 0 Other comprehensive income (OCI) (11,000) (63,500) (10,506) (62,077) 25364 (31,273) 5,909 Interest expense (revenue) (25,750) (14,500) (25,831) (14,829) 23042 (30,804) 7,762 - To adjust futures contracts to fair value. 91 = Number of days since purchase of futures contracts 17-Mar Cash 200,000 228,000 200,000 228,000 Interest expense (revenue) (200,000) (214,500) (200,000) (214,500) -To record interest on note receivable 17-Mar Cash (145,347) 82,653 (145,347) 82,653 Interest expense (revenue) 145,347 (69,153) 145,347 (69,153) -To record interest on note payable 3/31/00 3/31/00 Adj. To Margin Adj. To OCI Int. Exp. 31-Mar Cash 0 82,653 0 82,653 Balance Balance (Revenue) Futures margin account 1,000 51,000 1,092 49,998 0 0 0 Other comprehensive income (OCI) (2,000) (65,500) (2,104) (64,181) 25916 (32,333) 6,417 Interest expense (revenue) (53,653) (68,153) (53,641) (68,470) 23582 (31,848) 8,266 - To adjust futures contracts to fair value. 105 = Number of days since purchase of futures contracts 4/30/00 4/30/00 Adj. To Margin Adj. To OCI Int. Exp. 30-Apr Cash 0 82,653 0 82,653 Balance Balance (Revenue) Futures margin account 1,250 52,250 1,473 51,471 0 0 0 Other comprehensive income (OCI) (1,500) (67,000) (1,794) (65,975) 26540 (33,237) 6,697 Interest expense (revenue) 250 (67,903) 321 (68,149) 24431 (32,738) 8,307 - To adjust futures contracts to fair value. 135 = Number of days since purchase of futures contracts 5/31/00 5/31/00 31-May Cash 0 82,653 0 82,653 Balance Balance (Revenue) Futures margin account 1,250 53,500 1,496 52,967 0 0 0 Other comprehensive income (OCI) (1,500) (68,500) (1,821) (67,796) 27174 (34,154) 6,980 Interest expense (revenue) 250 (67,653) 325 (67,824) 25293 (33,642) 8,349 - To adjust futures contracts to fair value. 166 = Number of days since purchase of futures contracts 6/17/00 ` 6/17/00 17-Jun Cash 28,750 111,403 28,750 111,403 Balance Balance (Revenue) Futures margin account (26,750) 26,750 (26,611) 26,356 0 0 0 Other comprehensive income (OCI) 32,250 (36,250) 32,090 (35,706) 0 0 0 Interest expense (revenue) (34,250) (101,903) (34,229) (102,053) 25856 (35,706) 9,850 - To adjust futures contracts to fair value. 183 = Number of days since purchase of futures contracts 17-Jun Cash 200,000 311,403 200,000 311,403 Interest expense (revenue) (200,000) (301,903) (200,000) (301,903) -To record interest on note receivable 17-Jun Cash (177,450) 133,953 (177,450) 133,953 Interest expense (revenue) 177,450 (124,453) 177,450 (124,453) -To record interest on note payable 6/30/00 ` 6/30/00 30-Jun Cash 0 133,953 0 133,953 Balance Balance (Revenue) Futures margin account 750 27,500 796 27,152 0 0 0 Other comprehensive income (OCI) (500) (36,750) (570) (36,276) 0 0 0 Interest expense (revenue) (250) (124,703) (376) (124,829) 26652 (36,276) 9,624 - To adjust futures contracts to fair value. 196 = Number of days since purchase of futures contracts 7/31/00 ` 7/31/00 31-Jul Cash 0 133,953 0 133,953 Balance Balance (Revenue) Futures margin account 500 28,000 632 27,784 0 0 0 Other comprehensive income (OCI) (750) (37,500) (929) (37,205) 0 0 0 Interest expense (revenue) 250 (124,453) 297 (124,532) 27284 (37,205) 9,921 - To adjust futures contracts to fair value. 227 = Number of days since purchase of futures contracts 8/31/00 ` 8/31/00 31-Aug Cash 0 133,953 0 133,953 Balance Balance (Revenue) Futures margin account 750 28,750 887 28,671 0 0 0 Other comprehensive income (OCI) (500) (38,000) (689) (37,894) 0 0 0 Interest expense (revenue) (250) (124,703) (198) (124,730) 28171 (37,894) 9,723 - To adjust futures contracts to fair value. 258 = Number of days since purchase of futures contracts 9/17/00 ` 9/17/00 17-Sep Cash 29,000 162,953 29,000 162,953 Futures margin account (28,750) 0 (28,750) 0 Other comprehensive income (OCI) 38,000 0 38,000 0 Interest expense (revenue) (38,250) (162,953) (38,250) (162,953) -To adjust futures contracts to fair value 9/17/00 ` 9/17/00 17-Sep Cash 200,000 362,953 200,000 362,953 Interest expense (revenue) (200,000) (362,953) (200,000) (362,953) -To record interest on note receivable 9/17/00 ` 9/17/00 17-Sep Cash (182,000) 180,953 (182,000) 180,953 Interest expense (revenue) 182,000 (180,953) 182,000 (180,953) -To record interest on note payable 9/30/00 ` 9/30/00 30-Sep Cash 0 180,953 0 180,953 Futures margin account 0 0 0 0 Other comprehensive income (OCI) 0 0 0 0 Interest expense (revenue) 0 (180,953) 0 (180,953) -To adjust futures contracts to fair value 10/31/00 ` 10/31/00 31-Oct Cash 0 180,953 0 180,953 Futures margin account 0 0 0 0 Other comprehensive income (OCI) 0 0 0 0 Interest expense (revenue) 0 (180,953) 0 (180,953) -To adjust futures contracts to fair value 11/30/00 ` 11/30/00 30-Nov Cash 0 180,953 0 180,953 Futures margin account 0 0 0 0 Other comprehensive income (OCI) 0 0 0 0 Interest expense (revenue) 0 (180,953) 0 (180,953) -To adjust futures contracts to fair value. 12/17/00 ` 12/17/00 17-Dec Cash 0 180,953 0 180,953 Futures margin account 0 0 0 0 Other comprehensive income (OCI) 0 0 0 0 Interest expense (revenue) 0 (180,953) 0 (180,953) -To adjust futures contracts to fair value. 12/17/00 ` 12/17/00 17-Dec Cash 200,000 380,953 200,000 380,953 Interest expense (revenue) (200,000) (380,953) (200,000) (380,953) -To record interest on note receivable 0 0 12/17/00 ` 12/17/00 17-Dec Cash (184,781) 196,172 (184,781) 196,172 Interest expense (revenue) 184,781 (196,172) 184,781 (196,172) -To record interest on note payable 12/17/00 ` 12/17/00 17-Dec Cash 10,000,000 10,196,172 10,000,000 10,196,172 Notes receivable (10,000,000) 0 (10,000,000) 0 -To record the payoff of the note receivable 12/17/00 ` 12/17/00 17-Dec Cash (10,000,000) 196,172 (10,000,000) 196,172 Notes payable 10,000,000 0 10,000,000 0 -To record the payoff of the note payable 31-Dec Interest expense (revenue) 196,172 0 196,172 0 Retained earnings (196,172) (196,172) (196,172) (196,172) -To close the books SUMMARY SUMMARY SUMMARY SUMMARY SUMMARY Interest revenue for the year $800,000 8.0000% Less interest expense if not hedged: 3/17/00 $145,347 6/17/00 $177,450 9/17/00 $182,000 12/17/00 $184,781 $689,578 -6.8958% 6.8958% actual unhedged cost of capital rate Net income if interest expense is not hedged $110,422 1.1042% Add futures hedging gains and losses 3/17/00 $28,500 6/17/00 $28,750 9/17/00 $28,500 6.1179% = expected hedged cost of capital rate 12/17/00 $0 $85,750 0.8575% 6.0383% = actual hedged cost of capital rate Net income if interest expense is hedged $196,172 1.9617% Actual Outcomes Error Rate Expected revenue = IRR of $200,000 quarterly = 8.00000% $800,000 8.00000% $800,000 $0 0.0000% Expected cost of funds 6.1179% $611,787 6.0213% $603,828 ($7,959) -0.0796% Expected profit 1.88213% $188,213 1.97868% $196,172 $7,959 0.0796% Sheet1 Spot Yield Yield September 2000 Futures Contracts Sheet2 Periodic interest rate (a) 0.01644% = daily rate Periodic interest rate (a) 0.00000% = daily rate 6.00000% = annual APR rate 0.00000% = annual APR rate WP 285 MarginWHEW WP 285 MarginWHEW Units in contract (b) 2500 Units in contract (b) 2500 Contracts (c) 10 Contracts (c) 10 Total quantity (d) 25000 Total quantity (d) 25000 December 17, 1999 03/17/00 06/17/00 09/17/00 December 17, 1999 03/17/00 06/17/00 09/17/00 Future Contract Price 5.88 6.05 6.20 Future Contract Price 5.88 6.05 6.20 Original Spot Price 5.75 5.75 5.75 Original Spot Price 5.75 5.75 5.75 Days to Expiration (e) 91.00 183.00 275.00 Days to Expiration (e) 91.00 183.00 275.00 December 31, 1999 APR APR December 31, 1999 Future price 5.90 6.09 6.23 Discount Discount Future price 5.90 6.09 6.23 Spot price 5.79 5.79 5.79 Rate Rate Spot price 5.79 5.79 5.79 days from 12/17/99 (f) 14 14 14 6.0000% 0.0000% days from 12/17/99 (f) 14 14 14 FVd(fut) (g) * 494 * 973 * 719 FVd(fut) (g) * 500 * 1,000 * 750 FVd(spot) (h) * 987 * 973 * 958 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 1,000 * 1,000 * 1,000 Dr/(Cr) FVd(fut-spot) (i) * (493) 0.0 * (239) FVd(fut-spot) (i) * (500) 0.0 * (250) dFVd(fut) (j) * 494 * 973 * 719 $2,186 (FC Asset/liab effect) $2,250 dFVd(fut) (j) * 500 * 1,000 * 750 $2,250 (FC Asset/liab effect) dFVd(spot) (j) (987) (973) (958) ($2,918) (OCI effect) ($3,000) dFVd(spot) (j) (1,000) (1,000) (1,000) ($3,000) (OCI effect) dFVd(fut-spot) (j) 493 0 239 $732 (Net Income effect) $750 dFVd(fut-spot) (j) 500 0 250 $750 (Net Income effect) January 31, 2000 January 31, 2000 Future price 6.10 6.18 6.26 Future price 6.10 6.18 6.26 Spot price 5.89 5.89 5.89 Spot price 5.89 5.89 5.89 days from 12/17/99 (f) 45 45 45 days from 12/17/99 (f) 45 45 45 FVd(fut) (g) * 5,459 * 3,177 * 1,444 FVd(fut) (g) * 5,500 * 3,250 * 1,500 FVd(spot) (h) * 3,474 * 3,422 * 3,370 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 3,500 * 3,500 * 3,500 Dr/(Cr) FVd(fut-spot) (i) * 1,985 * (245) * (1,926) $0 FVd(fut-spot) (i) * 2,000 * (250) * (2,000) dFVd(fut) (j) * 5,459 * 3,177 * 1,444 $10,080 (FC Asset/liab effect) $10,250 dFVd(fut) (j) * 5,500 * 3,250 * 1,500 $10,250 (FC Asset/liab effect) dFVd(spot) (j) (3,474) (3,422) (3,370) ($10,266) (OCI effect) ($10,500) dFVd(spot) (j) (3,500) (3,500) (3,500) ($10,500) (OCI effect) dFVd(fut-spot) (j) (1,985) 245 1,926 $186 (Net Income effect) $250 dFVd(fut-spot) (j) (2,000) 250 2,000 $250 (Net Income effect) February 29, 2000 February 29, 2000 Future price 6.50 6.59 6.69 Future price 6.50 6.59 6.69 Spot price 6.45 6.45 6.45 Spot price 6.45 6.45 6.45 days from 12/17/99 (f) 74 74 74 days from 12/17/99 (f) 74 74 74 FVd(fut) (g) * 15,457 * 13,260 * 11,852 FVd(fut) (g) * 15,500 * 13,500 * 12,250 FVd(spot) (h) * 17,451 * 17,189 * 16,931 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 17,500 * 17,500 * 17,500 Dr/(Cr) FVd(fut-spot) (i) * (1,994) * (3,929) * (5,079) $0 FVd(fut-spot) (i) * (2,000) * (4,000) * (5,250) dFVd(fut) (j) * 15,457 * 13,260 * 11,852 $40,569 (FC Asset/liab effect) $41,250 dFVd(fut) (j) * 15,500 * 13,500 * 12,250 $41,250 (FC Asset/liab effect) dFVd(spot) (j) (17,451) (17,189) (16,931) ($51,571) (OCI effect) ($52,500) dFVd(spot) (j) (17,500) (17,500) (17,500) ($52,500) (OCI effect) dFVd(fut-spot) (j) 1,994 3,929 5,079 $11,002 (Net Income effect) $11,250 dFVd(fut-spot) (j) 2,000 4,000 5,250 $11,250 (Net Income effect) March 17, 2000 March 17, 2000 Future price 7.08 7.15 Future price 7.08 7.15 Spot price 7.02 7.02 Spot price 7.02 7.02 days from 12/17/99 (f) 91 91 days from 12/17/99 (f) 91 91 FVd(fut) (g) * 25,364 * 23,042 FVd(fut) (g) * 25,750 * 23,750 FVd(spot) (h) * 31,273 * 30,804 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 31,750 * 31,750 Dr/(Cr) FVd(fut-spot) (i) * (5,909) * (7,762) $0 FVd(fut-spot) (i) * (6,000) * (8,000) dFVd(fut) (j) * 25,364 * 23,042 $48,406 (FC Asset/liab effect) $49,500 dFVd(fut) (j) * 25,750 * 23,750 $49,500 (FC Asset/liab effect) dFVd(spot) (j) (31,273) (30,804) ($62,077) (OCI effect) ($63,500) dFVd(spot) (j) (31,750) (31,750) ($63,500) (OCI effect) dFVd(fut-spot) (j) 5,909 7,762 $13,671 (Net Income effect) $14,000 dFVd(fut-spot) (j) 6,000 8,000 $14,000 (Net Income effect) March 31, 2000 March 31, 2000 Future price 7.10 7.17 Future price 7.10 7.17 Spot price 7.06 7.06 Spot price 7.06 7.06 days from 12/17/99 (f) 105 105 days from 12/17/99 (f) 105 105 FVd(fut) (g) * 25,916 * 23,582 FVd(fut) (g) * 26,250 * 24,250 FVd(spot) (h) * 32,333 * 31,848 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 32,750 * 32,750 Dr/(Cr) FVd(fut-spot) (i) * (6,417) * (8,266) $0 FVd(fut-spot) (i) * (6,500) * (8,500) dFVd(fut) (j) * 25,916 * 23,582 $49,498 (FC Asset/liab effect) $50,500 dFVd(fut) (j) * 26,250 * 24,250 $50,500 (FC Asset/liab effect) dFVd(spot) (j) (32,333) (31,848) ($64,181) (OCI effect) ($65,500) dFVd(spot) (j) (32,750) (32,750) ($65,500) (OCI effect) dFVd(fut-spot) (j) 6,417 8,266 $14,683 (Net Income effect) $15,000 dFVd(fut-spot) (j) 6,500 8,500 $15,000 (Net Income effect) April 30, 2000 April 30, 2000 Future price 7.12 7.20 Future price 7.12 7.20 Spot price 7.09 7.09 Spot price 7.09 7.09 days from 12/17/99 (f) 135 135 days from 12/17/99 (f) 135 135 FVd(fut) (g) * 26,540 * 24,431 FVd(fut) (g) * 26,750 * 25,000 FVd(spot) (h) * 33,237 * 32,738 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 33,500 * 33,500 Dr/(Cr) FVd(fut-spot) (i) * (6,697) * (8,307) $0 FVd(fut-spot) (i) * (6,750) * (8,500) dFVd(fut) (j) * 26,540 * 24,431 $50,971 (FC Asset/liab effect) $51,750 dFVd(fut) (j) * 26,750 * 25,000 $51,750 (FC Asset/liab effect) dFVd(spot) (j) (33,237) (32,738) ($65,975) (OCI effect) ($67,000) dFVd(spot) (j) (33,500) (33,500) ($67,000) (OCI effect) dFVd(fut-spot) (j) 6,697 8,307 $15,004 (Net Income effect) $15,250 dFVd(fut-spot) (j) 6,750 8,500 $15,250 (Net Income effect) May 31, 2000 May 31, 2000 Future price 7.14 7.23 Future price 7.14 7.23 Spot price 7.12 7.12 Spot price 7.12 7.12 days from 12/17/99 (f) 166 166 days from 12/17/99 (f) 166 166 FVd(fut) (g) * 27,174 * 25,293 FVd(fut) (g) * 34,750 * 37,000 FVd(spot) (h) * 34,154 * 33,642 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 34,154 * 34,250 Dr/(Cr) FVd(fut-spot) (i) * (6,980) * (8,349) $0 FVd(fut-spot) (i) * 596 * 2,750 dFVd(fut) (j) * 27,174 * 25,293 $52,467 (FC Asset/liab effect) $71,750 dFVd(fut) (j) * 34,750 * 37,000 $71,750 (FC Asset/liab effect) dFVd(spot) (j) (34,154) (33,642) ($67,796) (OCI effect) ($68,404) dFVd(spot) (j) (34,154) (34,250) ($68,404) (OCI effect) dFVd(fut-spot) (j) 6,980 8,349 $15,329 (Net Income effect) ($3,346) dFVd(fut-spot) (j) (596) (2,750) ($3,346) (Net Income effect) June 17, 2000 June 17, 2000 Future price 7.25 Future price 7.25 Spot price 7.20 Spot price 7.20 days from 12/17/99 (f) 183 days from 12/17/99 (f) 183 FVd(fut) (g) * 25,856 FVd(fut) (g) * 26,250 FVd(spot) (h) * 35,706 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 36,250 Dr/(Cr) FVd(fut-spot) (i) * (9,850) $0 FVd(fut-spot) (i) * (10,000) dFVd(fut) (j) * 25,856 $25,856 (FC Asset/liab effect) $26,250 dFVd(fut) (j) * 26,250 $26,250 (FC Asset/liab effect) dFVd(spot) (j) (35,706) ($35,706) (OCI effect) ($36,250) dFVd(spot) (j) (36,250) ($36,250) (OCI effect) dFVd(fut-spot) (j) 9,850 $9,850 (Net Income effect) $10,000 dFVd(fut-spot) (j) 10,000 $10,000 (Net Income effect) June 30, 2000 June 30, 2000 Future price 7.28 Future price 7.28 Spot price 7.26 Spot price 7.26 days from 12/17/99 (f) 196 days from 12/17/99 (f) 196 FVd(fut) (g) * 26,652 FVd(fut) (g) * 27,000 FVd(spot) (h) * 37,263 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 37,750 Dr/(Cr) FVd(fut-spot) (i) * (10,611) $0 FVd(fut-spot) (i) * (10,750) dFVd(fut) (j) * 26,652 $26,652 (FC Asset/liab effect) $27,000 dFVd(fut) (j) * 27,000 $27,000 (FC Asset/liab effect) dFVd(spot) (j) (37,263) ($37,263) (OCI effect) ($37,750) dFVd(spot) (j) (37,750) ($37,750) (OCI effect) dFVd(fut-spot) (j) 10,611 $10,611 (Net Income effect) $10,750 dFVd(fut-spot) (j) 10,750 $10,750 (Net Income effect) July 31, 2000 July 31, 2000 Future price 7.30 Future price 7.30 Spot price 7.27 Spot price 7.27 days from 12/17/99 (f) 227 days from 12/17/99 (f) 227 FVd(fut) (g) * 27,284 FVd(fut) (g) * 27,500 FVd(spot) (h) * 37,701 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 38,000 Dr/(Cr) FVd(fut-spot) (i) * (10,417) $0 FVd(fut-spot) (i) * (10,500) dFVd(fut) (j) * 27,284 $27,284 (FC Asset/liab effect) $27,500 dFVd(fut) (j) * 27,500 $27,500 (FC Asset/liab effect) dFVd(spot) (j) (37,701) ($37,701) (OCI effect) ($38,000) dFVd(spot) (j) (38,000) ($38,000) (OCI effect) dFVd(fut-spot) (j) 10,417 $10,417 (Net Income effect) $10,500 dFVd(fut-spot) (j) 10,500 $10,500 (Net Income effect) August 31, 2000 August 31, 2000 Future price 7.33 Future price 7.33 Spot price 7.31 Spot price 7.31 days from 12/17/99 (f) 258 days from 12/17/99 (f) 258 FVd(fut) (g) * 28,171 FVd(fut) (g) * 28,250 FVd(spot) (h) * 38,891 Dr/(Cr) Dr/(Cr) FVd(spot) (h) * 39,000 Dr/(Cr) FVd(fut-spot) (i) * (10,720) $0 FVd(fut-spot) (i) * (10,750) dFVd(fut) (j) * 28,171 $28,171 (FC Asset/liab effect) $28,250 dFVd(fut) (j) * 28,250 $28,250 (FC Asset/liab effect) dFVd(spot) (j) (38,891) ($38,891) (OCI effect) ($39,000) dFVd(spot) (j) (39,000) ($39,000) (OCI effect) dFVd(fut-spot) (j) 10,720 $10,720 (Net Income effect) $10,750 dFVd(fut-spot) (j) 10,750 $10,750 (Net Income effect) September 17, 2000 September 17, 2000 Future price Future price Spot price Spot price days from 12/17/99 (f) days from 12/17/99 (f) FVd(fut) (g) FVd(fut) (g) FVd(spot) (h) Dr/(Cr) Dr/(Cr) FVd(spot) (h) Dr/(Cr) FVd(fut-spot) (i) $0 FVd(fut-spot) (i) dFVd(fut) (j) $0 (FC Asset/liab effect) $0 dFVd(fut) (j) $0 (FC Asset/liab effect) dFVd(spot) (j) $0 (OCI effect) $0 dFVd(spot) (j) $0 (OCI effect) dFVd(fut-spot) (j) $0 (Net Income effect) $0 dFVd(fut-spot) (j) $0 (Net Income effect) Sheet3 Spot Rates Futures Exp. ------> Dec. 1999 March 2000 Yield June 2000 Yield Sept. 2000 Yield 5.75 December 17, 1999 0.057500 0.058800 0.060500 0.062000 Quarterly factors 90 360 0.014375 0.014700 0.015125 0.015500 0.0610495091 6.104951% See See http://www.cme.com/market/interest/howto/hedging.html Quarterly factors 91 360 0.014535 0.014863 0.015293 0.015672 0.0610646541 6.106465% See See http://www.cme.com/market/interest/howto/hedging.html 0.0189353459 = locked in spread after hedge MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/285wp-1.xlt Sheet1 285wp $10,000,000 = Notional 10 =contracts $2,500 = scale factor 8.00% = int. rate 4 = quarterly 12/17/99 5.75% 0.2527777778 1.0145347222 1.014375 3/17/00 5.88% 0.2527777778 1.0148633333 1.0147 6/17/00 6.05% 0.2527777778 1.0152930556 1.015125 9/17/00 6.20% 0.2527777778 1.0156722222 1.0155 cc rate = 0.0606726441 0.0606576888 Futures Exp. ------> Dec. 1999 March 2000 Yield June 2000 Yield Sept. 2000 Yield Futures Exp. ------> Options Contract Value Change in Value Margin Deposit Margin Deposit Margin Deposit Note Receivable Rate Note Payable Rate (91/360) Adjustment Factor (91/360) cc Rate Factor December 17, 1999 5.75 5.88 6.05 6.20 December 17, 1999 $0 $0 0 0 0 0 0 0 0 8.0000% 5.7500% 0.2527777778 0.989010989 December 31, 1999 5.75 5.90 6.09 6.23 December 31, 1999 $2,250 $2,250 $2,250 0 $2,250 0 $2,250 0 $2,250 0.2527777778 January 31, 2000 5.75 6.10 6.18 6.26 January 31, 2000 $10,250 $8,000 $10,250 0 $10,250 0 $10,250 0 $10,250 0.2527777778 February 29, 2000 5.75 6.50 6.59 6.69 February 29, 2000 $41,250 $31,000 $41,250 0 $41,250 0 $41,250 0 $41,250 0.2527777778 March 17, 2000 5.75 7.02 7.08 7.15 March 17, 2000 $78,000 $36,750 $78,000 -28500 $49,500 0 $49,500 0 $49,500 8.0000% 5.7500% 0.2527777778 March 31, 2000 5.75 5.88 7.10 7.17 March 31, 2000 $50,500 -$27,500 $50,500 0 $50,500 0 $50,500 0 $50,500 0.2527777778 April 30, 2000 5.75 5.88 7.12 7.20 April 30, 2000 $51,750 $1,250 $51,750 0 $51,750 0 $51,750 0 $51,750 0.2527777778 May 31, 2000 5.75 5.88 7.14 7.23 May 31, 2000 $53,000 $1,250 $53,000 0 $53,000 0 $53,000 0 $53,000 0.2527777778 June 17, 2000 5.75 5.88 7.20 7.25 June 17, 2000 $55,000 $2,000 $55,000 0 $55,000 -28750 $26,250 0 $26,250 8.0000% 7.0000% 0.2527777778 June 30, 2000 5.75 5.88 6.05 7.28 June 30, 2000 $27,000 -$28,000 $27,000 0 $27,000 0 $27,000 0 $27,000 0.2527777778 July 31, 2000 5.75 5.88 6.05 7.30 July 31, 2000 $27,500 $500 $27,500 0 $27,500 0 $27,500 0 $27,500 0.2527777778 August 31, 2000 5.75 5.88 6.05 7.33 August 31, 2000 $28,250 $750 $28,250 0 $28,250 0 $28,250 0 $28,250 0.2527777778 September 17, 2000 5.75 5.88 6.05 7.34 September 17, 2000 $28,500 $250 $28,500 0 $28,500 0 $28,500 -28500 $0 8.0000% 7.1500% 0.2527777778 September 30, 2000 5.75 5.88 6.05 6.20 September 30, 2000 $0 -$28,500 $0 0 $0 0 $0 0 $0 0.2527777778 October 31, 2000 5.75 5.88 6.05 6.20 October 31, 2000 $0 $0 $0 0 $0 0 $0 0 $0 0.2527777778 November 30, 2000 5.75 5.88 6.05 6.20 November 30, 2000 $0 $0 $0 0 $0 0 $0 0 $0 0.2527777778 December 17, 2000 5.75 5.88 6.05 6.20 December 17, 2000 $0 $0 $0 0 $0 0 $0 $0 $0 8.0000% 7.3500% 0.2527777778 $0 -$28,500 -$28,750 -$28,500 $85,750 17-Dec Cash 10,000,000 10,000,000 Notes payable (10,000,000) (10,000,000) -To record borrowing for one year 17-Dec Cash (10,000,000) 0 Notes receivable 10,000,000 0 -To record lending for one year 17-Dec Cash 0 0 Futures margin account 0 0 -To record acquisition of 75 Futures margin account 31-Dec Cash 0 0 Futures margin account 2,250 2,250 $2,250 Other comprehensive income (OCI) (2,250) (2,250) Interest expense (revenue) 0 0 -To record Futures margin account mark-to-market 31-Jan Cash 0 0 Futures margin account 8,000 10,250 $10,250 Other comprehensive income (OCI) (8,000) (10,250) Interest expense (revenue) 0 0 -To record Futures margin account mark-to-market 29-Feb Cash 0 0 Futures margin account 31,000 41,250 $41,250 Other comprehensive income (OCI) (31,000) (41,250) Interest expense (revenue) 0 0 -To record Futures margin account mark-to-market 17-Mar Cash 28,500 28,500 Futures margin account 8,250 49,500 $49,500 Other comprehensive income (OCI) (36,750) (78,000) Interest expense (revenue) 0 0 -To record Futures margin account mark-to-market 17-Mar Cash 200,000 228,500 Interest expense (revenue) (200,000) (200,000) -To record interest on note receivable 17-Mar Cash (145,347) 83,153 -143750 (1,597) Interest expense (revenue) 145,347 (54,653) -To record interest on note payable 31-Mar Cash 0 83,153 Futures margin account 1,000 50,500 $50,500 Other comprehensive income (OCI) 27,500 (50,500) Interest expense (revenue) (28,500) (83,153) -To record Futures margin account mark-to-market 30-Apr Cash 0 83,153 Futures margin account 1,250 51,750 $51,750 Other comprehensive income (OCI) (1,250) (51,750) Interest expense (revenue) 0 (83,153) -To record Futures margin account mark-to-market 31-May Cash 0 83,153 Futures margin account 1,250 53,000 $53,000 Other comprehensive income (OCI) (1,250) (53,000) Interest expense (revenue) 0 (83,153) -To record Futures margin account mark-to-market 17-Jun Cash 28,750 111,903 Futures margin account (26,750) 26,250 $26,250 Other comprehensive income (OCI) (2,000) (55,000) Interest expense (revenue) 0 (83,153) -To record Futures margin account mark-to-market 17-Jun Cash 200,000 311,903 Interest expense (revenue) (200,000) (283,153) -To record interest on note receivable 17-Jun Cash (176,944) 134,958 -147000 (29,944) Interest expense (revenue) 176,944 (106,208) -To record interest on note payable 30-Jun Cash 0 134,958 Futures margin account 750 27,000 $27,000 Other comprehensive income (OCI) 28,000 (27,000) Interest expense (revenue) (28,750) (134,958) -To record Futures margin account mark-to-market 31-Jul Cash 0 134,958 Futures margin account 500 27,500 $27,500 Other comprehensive income (OCI) (500) (27,500) Interest expense (revenue) 0 (134,958) -To record Futures margin account mark-to-market 31-Aug Cash 0 134,958 Futures margin account 750 28,250 $28,250 Other comprehensive income (OCI) (750) (28,250) Interest expense (revenue) 0 (134,958) -To record Futures margin account mark-to-market 17-Sep Cash 28,500 163,458 Futures margin account (28,250) 0 $0 Other comprehensive income (OCI) 28,250 0 Interest expense (revenue) (28,500) (163,458) -To record Futures margin account mark-to-market 17-Sep Cash 200,000 363,458 Interest expense (revenue) (200,000) (363,458) -To record interest on note receivable -151250 151,250 17-Sep Cash (180,736) 182,722 Interest expense (revenue) 180,736 (182,722) -To record interest on note payable 30-Sep Cash 0 182,722 Futures margin account 0 0 $0 Other comprehensive income (OCI) 0 0 Interest expense (revenue) 0 (182,722) -To record Futures margin account mark-to-market 31-Oct Cash 0 182,722 Futures margin account 0 0 $0 Other comprehensive income (OCI) 0 0 Interest expense (revenue) 0 (182,722) -To record Futures margin account mark-to-market 30-Nov Cash 0 182,722 Futures margin account 0 0 $0 Other comprehensive income (OCI) 0 0 Interest expense (revenue) 0 (182,722) -To record Futures margin account mark-to-market 17-Dec Cash 0 182,722 Futures margin account 0 0 $0 Other comprehensive income (OCI) 0 0 Interest expense (revenue) 0 (182,722) -To record Futures margin account mark-to-market -To record Futures margin account mark-to-market 17-Dec Cash 200,000 382,722 Interest expense (revenue) (200,000) (382,722) -To record interest on note receivable 17-Dec Cash (185,792) 196,931 Interest expense (revenue) 185,792 (196,931) -To record interest on note payable 31-Dec Interest expense (revenue) 196,931 0 Retained earnings (196,931) (196,931) SUMMARY SUMMARY SUMMARY SUMMARY SUMMARY Interest revenue for the year $800,000 8.0000% Less interest expense if not hedged: 3/17/00 $145,347 6/17/00 $176,944 9/17/00 $180,736 12/17/00 $185,792 $688,819 -6.8882% 6.8882% actual unhedged cost of capital rate Net income if interest expense is not hedged $111,181 1.1118% Add futures hedging gains and losses 3/17/00 $28,500 6/17/00 $28,750 9/17/00 $28,500 6.0673% = expected hedged cost of capital rate 12/17/00 $0 $85,750 0.8575% 6.0307% = actual hedged cost of capital rate Net income if interest expense is hedged $196,931 1.9693% Actual Outcomes Error Rate Expected revenue = IRR of $200,000 quarterly = 8.36454% $836,454 8.00000% $800,000 $800,000 $0 0.0000% Expected cost of funds 6.0673% $606,726 6.0673% $606,726 $603,069 ($3,657) -0.6027% Expected profit 2.29728% $229,728 1.93274% $193,274 $196,931 $3,657 0.6027% Sheet2 Sheet3 MAIS DE 700 PLANILHAS editaveis EXCEL/MAIS DE 700 PLANILHAS editaveis EXCEL/285wp2.xlt Sheet1 285wp $10,000,000 = Notional 10 =contracts $2,500 = scale factor 8.00% = int. rate 4 = quarterly 12/17/99 5.75% 0.25 1.014375 1.014375 3/17/00 5.88% 0.25 1.0147 1.0147 6/17/00 6.05% 0.25 1.015125 1.015125 9/17/00 6.20% 0.25 1.0155 1.0155 cc rate = 0.0604915033 0.0606576888 Futures Exp. ------> Dec. 1999 March 2000 Yield June 2000 Yield Sept. 2000 Yield Futures Exp. ------> Options Contract Value Change in Value New Margin Balance Margin Deposit Margin Deposit Margin Deposit Note Receivable Rate Note Payable Rate (91/364) Adjustment Factor (91/364) cc Rate Factor December 17, 1999 5.75 5.88 6.05 6.20 December 17, 1999 $0 $0 $0 500 500 0.08 0.0575 0.25 0.997260274 December 31, 1999 5.75 5.90 6.09 6.23 December 31, 1999 $2,250 $2,250 $2,750 0 $2,750 0.25 January 31, 2000 5.75 6.10 6.18 6.26 January 31, 2000 $10,250 $8,000 $10,750 0 10750 0.25 February 29, 2000 5.75 6.50 6.59 6.69 February 29, 2000 $41,250 $31,000 $41,750 0 41750 0.25 March 17, 2000 5.75 7.02 7.08 7.15 March 17, 2000 $78,000 $36,750 $78,500 -28500 50000 $50,000 0.08 0.0575 0.25 March 31, 2000 5.75 5.88 7.10 7.17 March 31, 2000 $50,500 -$27,500 $51,000 0 $51,000 0.25 April 30, 2000 5.75 5.88 7.12 7.20 April 30, 2000 $51,750 $1,250 $52,250 0 $52,250 0.25 May 31, 2000 5.75 5.88 7.14 7.23 May 31, 2000 $53,000 $1,250 $53,500 0 $53,500 0.25 June 17, 2000 5.75 5.88 7.20 7.25 June 17, 2000 $55,000 $2,000 $55,500 -28750 $26,750 $26,750 0.08 0.07 0.25 June 30, 2000 5.75 5.88 6.05 7.28 June 30, 2000 $27,000 -$28,000 $27,500 $27,500 0 $27,500 0.25 July 31, 2000 5.75 5.88 6.05 7.30 July 31, 2000 $27,500 $500 $28,000 $28,000 0 $28,000 0.25 August 31, 2000 5.75 5.88 6.05 7.33 August 31, 2000 $28,250 $750 $28,750 $28,750 0 $28,750 0.25 September 17, 2000 5.75 5.88 6.05 7.34 September 17, 2000 $28,500 $250 $29,000 $29,000 -$29,000 $0 0.08 0.0715 0.25 September 30, 2000 5.75 5.88 6.05 6.20 September 30, 2000 $0 -$29,000 -$29,000 $0 0.25 October 31, 2000 5.75 5.88 6.05 6.20 October 31, 2000 $0 $0 $0 $0 0.25 November 30, 2000 5.75 5.88 6.05 6.20 November 30, 2000 $0 $0 $0 $0 0.25 December 17, 2000 5.75 5.88 6.05 6.20 December 17, 2000 $0 $0 $0 $0 0.08 0.0735 0.25 -$500 -$28,000 -$28,750 -$29,000 $85,750 17-Dec Cash 10,000,000 10,000,000 Notes payable (10,000,000) (10,000,000) -To record borrowing for one year 17-Dec Cash (10,000,000) 0 Notes receivable 10,000,000 0 -To record lending for one year 17-Dec Cash 0 0 Futures margin account 0 0 -To record acquisition of 75 Futures margin account 31-Dec Cash 0 0 Futures margin account 2,750 2,750 $2,750 Other comprehensive income (OCI) (2,250) (2,250) Interest expense (revenue) (500) (500) -To record Futures margin account mark-to-market 31-Jan Cash 0 0 Futures margin account 8,000 10,750 $10,750 Other comprehensive income (OCI) (8,000) (10,250) Interest expense (revenue) 0 (500) -To record Futures margin account mark-to-market 29-Feb Cash 0 0 Futures margin account 31,000 41,750 $41,750 Other comprehensive income (OCI) (31,000) (41,250) Interest expense (revenue) 0 (500) -To record Futures margin account mark-to-market 17-Mar Cash 28,500 28,500 Futures margin account 8,250 50,000 $50,000 Other comprehensive income (OCI) (36,750) (78,000) Interest expense (revenue) 0 (500) -To record Futures margin account mark-to-market 17-Mar Cash 200,000 228,500 Interest expense (revenue) (200,000) (200,500) -To record interest on note receivable 17-Mar Cash (143,750) 84,750 -143750 0 Interest expense (revenue) 143,750 (56,750) -To record interest on note payable 31-Mar Cash 0 84,750 Futures margin account (50,000) 0 $0 Other comprehensive income (OCI) 27,500 (50,500) Interest expense (revenue) 22,500 (34,250) -To record Futures margin account mark-to-market 30-Apr Cash 0 84,750 Futures margin account 0 0 $0 Other comprehensive income (OCI) (1,250) (51,750) Interest expense (revenue) 1,250 (33,000) -To record Futures margin account mark-to-market 31-May Cash 0 84,750 Futures margin account 0 0 $0 Other comprehensive income (OCI) (1,250) (53,000) Interest expense (revenue) 1,250
Compartilhar
Continue navegando
Perguntas relacionadas
Compartilhar