Sorry, its a long question. its from ACCT1116 course and about cash budget. Its due tommorrow so if you could do it than i will be thankfull

I couldn't understand it as well. This is the segmented income statement from question 2of the case study. Will this help by any chance if they are connected

He Home v Insert Data Calibri Light A Review 11 View BO Help C Open in Desktop App A ... 2 B Tell me what you want to do General 126 B C D E F G H I 1 Question 5- Cash Budget (30 marks) Assume that the five owners of Mountain Sports Ltd. decide to collectively invest personal funds into the Canmore expansion (this is a continuation of question 4). Mountain Sports will require 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 credit plus interest within a year. 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 4 to complete the cash budget): 1. Beginning cash balance invested by owners $ 59,000 Quarter 1 Quarter 4 con Quarter 2 Quarter 3 27% 29% 27% 9 2. Sales by quarter as % of total projected sales) 17% 11 12 13 3. Type of collections from customers Cash Sales Credit Sales (accounts receivable) 37% 63% 15 Cash sales are collected in the quarter of the sale all credit sales are collected in the quarter after the sale. Sheet2 Q5 Cash Budget Q3 Product Line Analysis Case Intro & Q4 Performance Measurement 'ex N] Search for or type a command File Home Insert Data Review D Calibri Light11 J26fo View Help Open in Desktop App BIOA ... 9 Tell me what you want to do 2 General ams 12 13 Cash Sales Credit Sales (accounts receivable) 37% 63% UW 14 15 Cash sales are collected in the quarter of the sale, all credit sales are collected in the quarter after the sale. 17 4. Merchandise purchases Merchandise purchases (cost of goods sold) are all paid in the quarter following purchase. (Quarter 1 purchases are 18 bought in Quarter 1 but paid for in quarter 2). 20 5. Operating expenses 21 All other operating expenses (all expenses except cost of goods sold) are paid on a monthly basis. 6. Required investment in equipment paid in cash in the first quarter 23 $ 134.000 25 7. Quarterly income tax payments paid in cash $ 8.000 27 8. Minimum cash balance S 25000 28 29 9. Borrowing and Repayments Sheet2 Case Intro 6 Q5 Cash Budget Q4 Performance Measurement Q3 Product Line Analysis NET Dvor Calibri Light vu v B Iva Av... vab E General J26 G 29 esms o E F 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 borrowing and payments are made in increments of $1,000. Interest on borrowing can be ignored. 31 Required: Prepare a cash budget (Chapter 10) for the first year of operation in Canmore by quarter and in total. Show clearly on your budget the quarter(s) in which borrowing will be needed and the quarter(s) in which repayments can be made, as requested by the company's bank. Quarter Percent of Sales Estimated Sales Year Summary 100% $519,000 27% $140,130 3 27% $140 130 29% $150,510 4 17% $88,230 CASH BALANCE Beginning 59,000 39 Collections from customers 40 Cash Sales 41 Credit Sales Sheet2 Case Intro a Q5 Cash Budget Q4 Performance Measurement Q3 Product e x NE OPO W Insert Data Review View Help Open in Desktop App Tell me w B Hvar v Av... BET J26 co F 39 Collections from customers: Teams 40 Cash Sales ments 41 Credit Sales 42 CASH AVAILABLE Calls 43 Less: Cash Payments 44 Merchandise purchases (COGS) 45 Sales Commissions 46 Advertising 47 Property Taxes 48 Rent 49 Salaries & Wages 50 Equioment Purchase Sheet2 Case Intro Q5 Cash Budget Q3 Proda Q4 Performance Measurement Home Insert Data View Review B Help A Open in Desktop App ... Tell me what you C D Teams Equipment Purchase Income tax Installment Total Disbursements 208,020 607,280 53 Cash Excess (Deficiency) -97171 54 Financing (Note 1) Borrow 128,000 Repayment of Principal (show as negative -18000 57 Net Financing 110,000 58 Cash Balance, Ending 25.135 60 Note 1: Financing Calculations 61 Cash excess (Deficiency) 62 Minimum cash balance 63 Amount to horrow Irenavi 34553 20135 Sheet2 Case Intro Q5 Cash Budget Q4 Performance Measurement Q3 Product Line J26 B co For Teams 53 Cash Excess (Deficiency) -97,171 54 Financing (Note 1) 55 Borrow 128,000 Repayment of Principal (show as negative) 56 -18.000 57 Net Financing 110,000 58 Cash Balance, Ending 25,135 -97171 3 4 553 34,113 20,135 60 Note 1. Financing Calculations 61 Cash excess (Deficiency} 62 Minimum cash balance 63 Amount to borrow (repay) Borrowing (Repayments) Rounded to increment of $1,000 Sheet2 Q3 Product Line Q5 Cash Budget Case Intro Q4 Performance Measurement Search for or type a command ACCT1116 Case Study DATA-... Home Insert Tell me what you want to do Data Review View Help Open in Desktop App Av. 3 11 Calibri Light B 22 o n General A25 for Interest expense Mountain Sports Ltd Segmented Income Statement For the Year Ended Dec 31, 2019 Cross Country Ski Packages Mountain Bikes 686 178 758,359 4696 318 387 359 598 367291 398821 TOTAL 1 918578 Parts & Service 114 808 Notes Accessories 359 228 2796 47% 11 Sales 12 Cost of goods sold % 13 Cost of goods sold 14 Gross Profit Operating expenses 31 438 6057 8482 46.115 6057 8.482 37186 35525 Deprecation Property taxes en Partservice expense Salate commissions orlonlawlNH 95 911 182565 SH 55.488 20 137.522 137522 25550 Corting Epenses S5 072 2250 3028 9 752 2250 25 let sense Sheets Sheets Quot Group Case Intro Sheet2 Q2 Seg Rome Stmt NT DOL 1918573 Cost of oods sold 686,178 46% 318,387 367,791 758,359 47% 359538 398,821_ 359,228 2756 95591 253637 114 808 1996 22,365 92 443 1122693 14 5 15 Cost of goods sold Gross Prot Operating expenses Advertising Depreciation Property taxes 8077 6057 8.482 37186 96000 24227 33.926 148 743 95 911 482 565 Parts & service expense Salaries & commissions 31,438 6,057 8,482 37,186 36,486 137.522 25.550 282.719 85,072 2.250 82,822 13517 46,115 6,057 8,482 37,186 40,324 137 522 25550 301 234 97,587 2 250 95337 15049 $80,287 10,370 6,057 8,482 37186 19.101 137 522 25550 244 257 19370 2250 17120 RN 9039 Total Operating Expenses Operating income loss 25nterest expense Income Loss before taxes ncome tax let income Loss -55 150 2129 $69,205 59992 Notes to the financial statements: Sheet2 Q2 Seg ome Stmt Case Intro E Group Sheet1 Sheet3 Q1 Cost Ciastications NO DELL