4 5 6 7 8 9 28% 2196 Assume that the five owners of Mountain Sports Lid. 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 IS 59.000 Quarter 1 Quarter 2 Quarter 3 Quarter 4 2. Sales by quarter (as % of total projected sales) 24% 27% 10 11 3. Type of collections from customers 12 Cash Sales 35% Credit Sales (accounts 13 receivable) 65% 14 15 Cash sales are 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) are all paid in the quarter following purchase. (Quarter I purchases are bought in Quarter 1 but paid for in quarter 2). 19 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 $ 138,000 23 quarter 18 22 B C D 25 7. Quarterly income tax payments paid in cash 26 7.000 27 8. Minimum cash balance $ 28 21,000 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. 30 All borrowing and payments are made in increments of $1,000. Interest on borrowing can be ignored. 31 Required: Prepare a cash budget 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. 32 Mountain Sports Cash Budget 33 For the year ended December 31st 34 Quarter Year 35 3 41 Summary 24% 36 Percent of Sales 27% 28% 21% 10096 37 Estimated Sales $120,960 $136,080 $141,120 $105,840 $504,000 38 2 39 CASH BALANCE, Beginnings 59,000 40 Collections from customers: 41 Cash Sales 42 Credit Sales 43 CASH AVAILABLE File Home Insert Formulas Data Review View Automa V Calibri Light 9 B A1 G : ns 33 34 35 36 Percent of Sales ments 37 Estimated Sales 38 fx Question 5 - Cash Budget (30 marks) B C D E Mountain Sports Cash Budget For the year ended December 31st Quarter 1 249 279 28% 21% $120,960 $135,080 $141,120 S105.840 Year Summary 10096 $504,000 39 CASH BALANCE, Beginnings 59.000 40 Collections from customers! Cash Sales 42 Credit Sales 43 CASH AVAILABLE 44 Less Cash Payments Merchandise purchase (COS) 46 Sales Commissions Advertising 48 Property Thes 49 Rent 50 Salaries & Wage Equipment Purchase Income tax installment Total Disbursement 34 Cash Deficiency 56 Bome payment of Principal show a ne 57 B 58 Netlinaming 39 Cash Balance inding 50 Aus Case Intro Group Members Q1 Q2 Q3 Q4 Q5 Calculation Model Automatic Workbook Statistics 4 5 6 7 8 9 28% 2196 Assume that the five owners of Mountain Sports Lid. 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 IS 59.000 Quarter 1 Quarter 2 Quarter 3 Quarter 4 2. Sales by quarter (as % of total projected sales) 24% 27% 10 11 3. Type of collections from customers 12 Cash Sales 35% Credit Sales (accounts 13 receivable) 65% 14 15 Cash sales are 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) are all paid in the quarter following purchase. (Quarter I purchases are bought in Quarter 1 but paid for in quarter 2). 19 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 $ 138,000 23 quarter 18 22 B C D 25 7. Quarterly income tax payments paid in cash 26 7.000 27 8. Minimum cash balance $ 28 21,000 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. 30 All borrowing and payments are made in increments of $1,000. Interest on borrowing can be ignored. 31 Required: Prepare a cash budget 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. 32 Mountain Sports Cash Budget 33 For the year ended December 31st 34 Quarter Year 35 3 41 Summary 24% 36 Percent of Sales 27% 28% 21% 10096 37 Estimated Sales $120,960 $136,080 $141,120 $105,840 $504,000 38 2 39 CASH BALANCE, Beginnings 59,000 40 Collections from customers: 41 Cash Sales 42 Credit Sales 43 CASH AVAILABLE File Home Insert Formulas Data Review View Automa V Calibri Light 9 B A1 G : ns 33 34 35 36 Percent of Sales ments 37 Estimated Sales 38 fx Question 5 - Cash Budget (30 marks) B C D E Mountain Sports Cash Budget For the year ended December 31st Quarter 1 249 279 28% 21% $120,960 $135,080 $141,120 S105.840 Year Summary 10096 $504,000 39 CASH BALANCE, Beginnings 59.000 40 Collections from customers! Cash Sales 42 Credit Sales 43 CASH AVAILABLE 44 Less Cash Payments Merchandise purchase (COS) 46 Sales Commissions Advertising 48 Property Thes 49 Rent 50 Salaries & Wage Equipment Purchase Income tax installment Total Disbursement 34 Cash Deficiency 56 Bome payment of Principal show a ne 57 B 58 Netlinaming 39 Cash Balance inding 50 Aus Case Intro Group Members Q1 Q2 Q3 Q4 Q5 Calculation Model Automatic Workbook Statistics