In order to fund its short-term operations, the Chief Financial Officer (CFO) of Best Company has decided use short-term money market instruments. The CFO has asked you and your team to advise the company of the best course of action. After a lengthy discussion with the CFO, it was decided to issue bank-accepted bills of exchange (bank bills). In order to obtain board approval, the CFO has asked you and your team to create a simple spreadsheet to illustrate the immediate factors (such as interest rates, fees and charges) that affect the amount of funds raised, and ultimate effective cost to Best Company of issuing the bank bills. THE TASK
You and your team are to create a simple spreadsheet for calculating bank bill prices and the effective cost of raising funds through the issue of bank bills allowing for fees and charges.
Users of the spreadsheet should be able to input the following:
Spreadsheet Inputs (i.e. what the user will type into the relevant cells of the spreadsheet). The face value of the bank bill (the dollar amount to be paid to the holder at maturity); The term to maturity at issue (in days);
The market yield on the purchase date expressed at a simple rate of interest (% p.a.).; Fees and charges (the dollar amount to be paid by the issuer of the bank bills); When the fees and charges are to be paid (either at issue or maturity).
Once you have defined your inputs, your spreadsheet should output the following:
Spreadsheet Outputs (i.e. what the user of the spreadsheet will see after entering all their inputs). The ‘gross’ amount of funds raised (i.e. the issue price of the bank bill). This does not allow for fees and charges. The ‘net’ amount of funds raised (which means the amount of funds raised after deduction of fees and charges if appropriate). The gross outgo at maturity (that is the amount that has to be paid at maturity including fees and...