Cash Flow Planning

complete the cash flow forecast/ budget to capture the anticipated cash flow in the following case study scenario.

Use the figures in the case study that follows. Enter them in the appropriate columns (representing months) of your 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 U$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% 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 U$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 of 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 30^th, and $100,000 ninety days after the completion date.
• The $600,000 contract for the equipment in the contract with the Boston supplier calls for a down payment of 25% when the order is confirmed at the beginning of March, 60% 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 the 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% 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% 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, payable in May, in advance of the travel