Requirements: Please use the data from the following table to complete the cash budget in Excel spreadsheets. You can use the Excel file (FINA 3562 Project 2 data.xlsx) that I uploaded on Blackboard. To complete the cash budget, you will have to work on the schedules of expected cash collections and payments first. Please set formulae by referencing to the data in Excel spreadsheets. If you do not meet this requirement, you will be asked to re-work on the project. This project accounts for 6% of your overall grade. Due date: October 28th, 11:59 pm on Blackboard The following table lists the data from the budget of Cornell Inc. 40% the company's sales are for cash on the nail; the other 60% 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 $360,000, and total sales in June were $1,050,000 Cornell Inc. Sales, purchase, and other budgets June July August September Total sales $ 1,050,000 $1,400,800 $1,783,500 $1,396,300 Purchases of material For cash 306,192 306,064 263,112 For credit 360,000 459,288 534,096 394,668 337,150 Selling and admin, expenses 348,600 375,620 Income taxes 115,000 115,000 115,000 50,000 50,000 50,000 Dividends Equipment purchase 200,000 200,000 0 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 $110,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 $110,000 * All borrowings and repayments must be in multiples of $1,000 amounts, and interest is 4 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, repay some portion of the loan at the end of August, and should be able to pay off the remaining balance of the loan at the end of September. 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 or September 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 Please also review pages 8-9 of the class notes for how to back out the borrowing amount or repayment of the loan. Compute the interest payment first, and figure out the repayment of the loan for August or September 1 2 Cornell Inc. Sales, purchase, and other budgets June July August September $ 1,050,000 $1,400,800 $ 1,783,500 $ 1,396,300 360,000 306,192 459,288 348,600 115,000 50,000 200,000 306,064 534,096 375,620 115,000 50,000 200,000 263,112 394,668 337,150 115,000 50,000 0 3 4 Total sales 5 Purchases of material 6 For cash 7 For credit 8 Selling and admin. expenses 9 Income taxes 10 Dividends 11 Equipment purchase 12 13 14 15 16 17 Accounts receivable, 6/30/2019 18 July sales ($1,400,800) 19 August sales ($1,783,500) 20 September sales ($1,396,300) 21 Total cash collections 22 23 24 25 26 Schedule of Expected Cash Collections July August September Schedule of Expected Cash Payments July August September 27 Accounts payable, 6/30/2018 28 July purchases 29 August purchases 30 September purchases 31 Total cash disbursement 32 33 34 Shot Cash Budget For the Month Ended September 30, 2019 Month July August September $180,000 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 Ita)-(b) + (d)] FINA 3562 Project 2 Learning objective: Students will learn how to make a cash budget using Excel spreadsheets through this assignment Requirements: Please use the data from the following table to complete the cash budget in Excel spreadsheets. You can use the Excel file (FINA 3562 Project 2 data.xlsx) that I uploaded on Blackboard. To complete the cash budget, you will have to work on the schedules of expected cash collections and payments first. Please set formulae by referencing to the data in Excel spreadsheets. If you do not meet this requirement, you will be asked to re-work on the project. This project accounts for 6% of your overall grade. Due date: October 28th, 11:59 pm on Blackboard The following table lists the data from the budget of Cornell Inc. 40% the company's sales are for cash on the nail; the other 60% 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 $360,000, and total sales in June were $1,050,000 Cornell Inc. Sales, purchase, and other budrets June July August September $1,396,300 Total sales $ 1,050,000 $1,400,800 $1,783,500 Purchases of material 306,192 306,064 263,112 For cash 459,288 360,000 534,096 For credit 394,668 337,150 348,600 375,620 Selling and admin, expenses 115,000 115.000 Income taxes 115,000 50,000 50,000 50,000 Dividends Equipment purchase 200,000 200,000 D zuelan Boliv....pdf go OOO 000 F4 F3 FS FS of