FINA 3562

Project 2

Learning objective: Students will learn how to make a cash budget using Excel spreadsheets through this assignment.

Due date: March 11th, 11:59 pm on Blackboard

The following table lists the data from the budget of Cornell Inc. 35% the companys sales are on cash; the other 65% are paid for with a one-month delay. The company pays all its credit purchase with a one-month delay. Credit purchases in June were $374,557, and total sales in June were $1,135,020.

We make these assumptions to simplify the analysis:

* Cornell Inc. has an open line of credit with its bank, which can be used as needed to bolster the cash position.

* The company desires to maintain a $90,000 minimum cash balance at the end of each month. Therefore, borrowing must be sufficient to cover the cash shortfall and to provide for the minimum cash balance of $90,000.

* All borrowings and repayments must be in multiples of $1,000 amounts, and interest is 6 percent per annum.

* Interest is computed and paid on the principal during the borrowing period.

* All borrowings take place at the beginning of a month, and all repayments are made at the end of a month. The company will take a loan at the beginning of July, and should be able to pay off the loan at the end of September with its cash surplus. You have to figure out the amount of loan taken at the beginning of July.

* The company pays the selling and administrative expenses, income taxes, dividends, and pays for equipment purchase at the end of a month.

Please refer to the example of cash budget in chapter 6 Excel file posted on Blackboard. The interest payment made at the end of August and September, respectively, is computed as: Principal of the loan x Interest rate on the loan x borrowing period .

Because the interest rate on the loan is an annual rate, the borrowing period will be adjusted to a fraction of 12 months.

Cornell Inc. Sales, purchase, and other budgets June July August September $1,096,300 Total sales $ 1,135,020 | $1,275,800 $1,483,500 Purchases of material For cash 344,466 400,572 296,001 For credit 374,557 421,014 489,588 361,779 Selling and admin. expenses 360,600 395,620 338,450 Income taxes 80,000 80,000 80,000 Dividends 20,000 20,000 20,000 Equipment purchase 158,900 Cornell Inc. Sales, purchase, and other budgets June July $ 1,135,020 $ 1,275,800 $ August 1,483,500 September $ 1,096,300 Cornell Inc. Cash Budget For the Month Ended September 30, 2019 Month August September $130,000 July 374,557 4 Total sales 5 Purchases of material For cash 7 For credit 8 Selling and admin. expenses 9 Income taxes 10 Dividends 11 Equipment purchase 344,466 421,014 360,600 80,000 20,000 158,900 400,572 489,588 395,620 80,000 20,000 296,001 361,779 338,450 80,000 20,000 12 15 Schedule of Expected Cash Collections August September 737,763 July 17 Accounts receivable, 6/30/2019 18 July sales ($1,275,800) 19 August sales ($1,483,500) 20 September sales ($1,096,300) 21 Total cash collections Cash balance, beginning Add: Receipts: Collections from customers Total cash available (a) Less: Disbursements: Direct materials Selling and admin. Equipment purchase Dividends Income tax Total disbursements (b) Minimum cash balance Total cash needed [c] Cash surplus (deficit) (a)-[c] Finance: Borrowing Repayment Interest Total effect of financing (d) Cash balance, ending (la)-(b) + (d)] L26 25 26 Schedule of Expected Cash Payments July August September 374,557 27 Accounts payable, 6/30/2019 28 July purchases 29 August purchases 30 September purchases 31 Total cash disbursement Sheet1 Sheet2 Sheet3 +