please help me with cell number 65

D Question 5 -Cash Budget (30 marks) G N Mountain Sports has aquired an open line of credit up to a maximum of $350,000. It will be necessary to convince the bank manager of this new Canmore branch ability to repay its line of credit including any interest Management has provided the following list of assumptions to help in the preparation of the cash budget (note: you will need to use the projected income statement provided in Question to complete the cash budget 4 5 6 1. Beginning cash balance invested by owners s 60.000 Quarter 1 Quarter 2 Quarter 3 Quarter 4 2. Sales by quarter (as of total projected sales) No 29% 30% 29 12% 10 11 3. Type of collections from customers! 12 Car Sales 44% 11 Credit Sales (accounts receivable) 56 14 15 Cash sales we collected in the quarter of the sale, all credit sales are collected in the quarter after the sale. 16 17 4. Merchandise purchases Merchandise purchases (cost of goods sold) me all paid in the quarter following purchase. (Quarter 1 purchases are 15 bought in Quarter 1 but paid for in quarter 2) 2 15 H 0 19 20 5. Operating expenses 21 All other operating expenes tall expenses except cost of goods sold) are paid on a monthly basi 6. Required investment in equipment puid in cash in the first quarter 23 5 136,000 14 25 7. Quarterly income tax payments paid in canh s 7,000 26 27 8. Minimum cash balance IS 30,000 20 29 9. Borrowing and Repayments: Any borrowing will take place on the first day of the quarter and any repayments are paid at the end of the quarter All borrowindments are made in increments1,000. Interest on borrowing can be ignored 30 Required. Prepare a cash budget for the first year of operation in Canmore by quarter and in total. Show clearly on your budget the quarters) in which borrowing will be needed and the quarter() in which repayments can be made, as requested by the company's bank Mountain Sports Cash Budget For the year ended December 31 33 34 Year Quarter 2 30 Note from instructor 10 Percent of Sales 293 29% 4 12N Summary 100% D F G H 3 Percent of Sales 17 Fitimated Sales 38 29% 5151 380 C 30% $156,600 29 $151,380 12% 562,640 100N $522.000 Total sales amount taken from Question Remember to refer to question 4 as the cash budget is a continuation of the Carmore expansion introduced in 4. This will be important for the cach disbursements as well 29 CASH BALANCE, Benning S 60,000 300005 30,7975 30,8435 0000 40 Collections from customers 41 Cash Sales 66,607 68,904 66,607 27,562 229,680 2 Credia Sales 14,722 87.696 4293 252242 43 CASH AVAILABLE 126,607 183,677 185,100 $ 141,177 546,922 44 Le Cash Paynents 45 Merchandise purchases (Cois 62.600 64,800 62.640 190,00 Sales Commissions 22,702 23,400 22,707 9.196 18,300 41 Advertising 5.000 5.000 5.000 5,000 20,000 50 2,500 2.500 2.500 2,500 Property 10,000 10.250 42 Ilent 10,250 10250 10.250 41,000 Salaries Wat 26,000 26.000 26.000 26.000 104.00 136,000 51 Equipment Purchase 136,000 7,000 7.000 2,000 28.000 Income tax installment F G G H 53 Total Disbursements 209,457 136,880 138,257 122,786 607,380 54 Cash Excess (Deficiency 82,850 46,797 46,843 20,391 60.458 55 Financing (Note 1) 56 112,850 0 0 9,609 122,459 Borrow Repayment of Principal (show as negative) 57 16,000 16,000 32,000 58 Net Financing 112,850 - 16,000 16,000 9,609 90,459 S 30,000 $ 30,797 S 30,843S 30,000 30,000 59 Cash Balance, Ending 60 61 Note 1: Financing Calculations 62 Cash excess (Deficiency) 63 Minimum cash balance 64 Amount to borrow (repay) 82,850 30,000 112,850 46,797 30,000 46,843 30,000 20,931 30,000 9,609 Borrowing (Repayments) Rounded to 65 increment of $1,000 6 66 69