|
|
|
Refer to problem 7-B1 in the text. I will give you an Acrobat file [download it here] with the model I have developed for the Wallaby Kite case. Your task is to develop an Excel model to reproduce the budget worksheets. Cell referencing is required throughout the model for ALL derived values. We will work on the case in class on May 5th and your work is due at the end of class. In order to finish by that time, you will have to work on this outside of class. If you have not finished by the end of class on May 5th, your grade will be reduced accordingly. Note the following changes and/or modifications in the case. IGNORE the last paragraph on page 289 ["Money can be borrowed..."]. Substitute the following text for that paragraph. Victoria Kite is seeking a line of credit from the bank. a line of credit is different from a regular loan or mortgage. A mortgage is for a specific dollar amount [the principle], and requires fixed periodic payments which reduce the principle and pay the interest due on the principle balance. The balance of principle owed get progressively smaller over time. In contrast, a line of credit permits the borrower to borrow up to a fixed maximum as cash needs dictate. The principle can be repaid as cash flows permit. Interest is paid monthly on whatever balance is outstanding at the beginning of the month. For the purposes of this analysis, assume that all borrowing against the line of credit is at the beginning of a given month and all repayments are at the end of a given month. Borrowing and repayment never occur in the same month. Interest accruing in given month is paid on the first business day of the next month [i.e., January's interest is paid in February, February's interest is paid in March, etc.]. Victoria Kite will borrow as much as is necessary to maintain the minimum cash balance specified on page. 324, and they will pay back principle as soon as cash flows permit. The annual interest rate on the line of credit is 10%. These conditions require that you use a series of =IF( ) formulas in Excel
to determine [1] whether borrowing is necessary, and [2] whether repayment is
possible AND necessary. [We don't want to pay back debt we do not owe!] Refer to
the
PowerPoint file on the =IF( ) formulas for help in modeling this portion of
the budget spreadsheet. Yes, use of these formulas is REQUIRED. |
Copyright © 2010 Gerald M. Myers
|