Using a computer spreadsheet, set up a cash flow statement to capture the anticipated cash flow in the following case study scenario.

Cash Flow Planning Exercise

This exercise is an opportunity to practice cash flow projection using a cash flow planning worksheet. Use the figures in the case study that follows. Enter them in the appropriate columns (representing months) of the worksheet.

Assume no other external or internal income is available to carry the transaction, and develop a financing scenario that will cover cash flow requirements but will also optimize the amount of income by the close of the transaction.

Case Study

Canuck Pure Water Inc. has obtained a contract from Agua Pura S.A. of Chile to install a new bottling line in its old plant in Santiago. Canuck is to supply the equipment, the engineering, the technical and administrative staff, and the computer equipment and software. It will employ local labourers in Chile for the plant construction. The value of the contract between Agua Pura S.A. of Chile and Canuck Pure Water Inc. is $1 million USD. To execute the contract, Canuck Pure Water Inc. is subcontracting to a supplier in Boston, Massachusetts for the provision of the machinery, equipment, and computer hardware and software that will be needed. These items will be purchased from the supplier in Boston for US $600,000 and produced/assembled over a 3 Month timeframe. All the equipment purchased from the firm in Boston will be shipped from New York, USA to Santiago, Chile by maritime cargo. This will reduce delays and costs; However, Canuck will assume all charges for freight, loading, unloading, packing, and maritime insurance.

Note that all project-related salaries as well as the travel and expenses of the staff and workers involved in the project, will be paid in U.S. dollars.

The following facts will assist you in developing a projected cash flow for this project.

• The $1 Million USD contract with Agua Pura S.A. stipulates that an advance of 10 percent to cover start-up costs will be paid when the contract is signed.
• The $1 Million USD sales contract with Agua Pura S.A. and the $600,000 contract for the equipment with the Boston supplier are both signed and confirmed at the beginning of March, with the project to be completed by the end August (6 months in total).
• If the overall work schedule is complied with, the buyer Agua Pura S.A will pay $300,000 on May 1st, $500,000 on August 30th and $100,000 ninety days after the completion date.
• The $600,000 contract for the equipment with the contract with the Boston supplier calls for a down payment of 25 percent when the order is confirmed at the beginning of March, 60 percent on April 1st, and the remaining portion on May 31st when the equipment is ready for shipment.

Additionally, the following information is provided (all figures are in U.S. dollars):

• Canuck Pure Water Inc. has a bank account balance of $100,000 at the end of February (i.e., before the project begins)
• The freight Costs to ship the equipment from Boston to Chile DDP total $25,000, which is payable on loading estimated to be in first week of June
• Packing costs are $2,000, and these are payable in May
• Loading and unloading will cost $1,000 and these charges will be paid in May
• Shipping Insurance: 1.5 percent of the $600,000 USD contract (half payable in March, the other half in April)
• The bank fees for setting up a line of credit for the firm will be $5,000 USD (payable in the month when the loan agreement with the bank is signed)
• Interest on any funds used will be 12 percent per annum (or simply 1% per month, calculated monthly on the line of credit balance at month end payable in the following month)
• Project-related salary expenses total $30,000 USD per month, i.e., payable monthly in March, April, May, June, July and August
• Travel and expenses are $40,000 USD, which is payable in May, in advance of the travel

In this case, Canuck Pure Water Inc. will require a special bank Line of Credit to support the project, and the company will make use of some of its own funds. Based on the data provided above, answer the following questions:

• How much of a credit limit is Canuck going to need on its line of credit?

• For how many months will the bank line of credit be needed?

• In what currency should the line of credit be in, and why?