B C D 1 Question 5 - Cash Budget (30 marks) F F H 2 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. 3 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): 4 5 6 1. Beginning cash balance invested by owners 59,000 7 27% Quarter 1 Quarter 2 Quarter 3 Quarter 4 2. Sales by quarter (as % of total projected sales) 9 24% 28% 21% 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 1 purchases 18 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. ams 3 alls les 18 are bought in Quarter I 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. 22 6. Required investment in equipment paid in cash in the first 23 quarter $ 138,000 24 25 7. Quarterly income tax payments paid in cash S 7,000 26 27 8. Minimum cash balance 21,000 28 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 30 quarter. 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 1 2 3 Summary 36 Percent of Sales 24% 27% 28% 21% 100% 37 Estimated Sales $120,960 $ 136,080 $141,120 $105,840 $504,000 4 38 ... ab Calibri Light B Ivar A V 11 E F B D Mountain Sports Cash Budget For the year ended December 31st Quarter 1 2 3 24% 27% 28% $120,960 $136,080 $141,120 Year 4 Summary 100% Percent of Sales Estimated Sales 21% $ 105,840 $504,000 CASH BALANCE, Beginning $ 59,000 Collections from customers: Cash Sales Credit Sales CASH AVAILABLE Less: Cash Payments Merchandise purchases (COGS) 5 Sales Commissions 7 Advertising 8 Property Taxes 9 Rent 0 Salaries & Wages borr 1 2 2 5. 6. Group Member Evaluation fr E F G D G B Rent Salaries & Wages Equipment Purchase Income tax Installment 3 Total Disbursements -4 Cash Excess (Deficiency) 55 Financing (Note 1) 56 Borrow Repayment of Principal (show as negative) 57 58 Net Financing 59 Cash Balance, Ending 2 8 8 98% 0 61 Note 1: Financing Calculations (you may use the space below for calculations) Cash excess (Deficiency) 0 Minimum cash balance Amount to borrow (repay) Borrowing (Repayments) 65 Rounded to increment of $1,000 66 B C D 1 Question 5 - Cash Budget (30 marks) F F H 2 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. 3 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): 4 5 6 1. Beginning cash balance invested by owners 59,000 7 27% Quarter 1 Quarter 2 Quarter 3 Quarter 4 2. Sales by quarter (as % of total projected sales) 9 24% 28% 21% 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 1 purchases 18 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. ams 3 alls les 18 are bought in Quarter I 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. 22 6. Required investment in equipment paid in cash in the first 23 quarter $ 138,000 24 25 7. Quarterly income tax payments paid in cash S 7,000 26 27 8. Minimum cash balance 21,000 28 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 30 quarter. 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 1 2 3 Summary 36 Percent of Sales 24% 27% 28% 21% 100% 37 Estimated Sales $120,960 $ 136,080 $141,120 $105,840 $504,000 4 38 ... ab Calibri Light B Ivar A V 11 E F B D Mountain Sports Cash Budget For the year ended December 31st Quarter 1 2 3 24% 27% 28% $120,960 $136,080 $141,120 Year 4 Summary 100% Percent of Sales Estimated Sales 21% $ 105,840 $504,000 CASH BALANCE, Beginning $ 59,000 Collections from customers: Cash Sales Credit Sales CASH AVAILABLE Less: Cash Payments Merchandise purchases (COGS) 5 Sales Commissions 7 Advertising 8 Property Taxes 9 Rent 0 Salaries & Wages borr 1 2 2 5. 6. Group Member Evaluation fr E F G D G B Rent Salaries & Wages Equipment Purchase Income tax Installment 3 Total Disbursements -4 Cash Excess (Deficiency) 55 Financing (Note 1) 56 Borrow Repayment of Principal (show as negative) 57 58 Net Financing 59 Cash Balance, Ending 2 8 8 98% 0 61 Note 1: Financing Calculations (you may use the space below for calculations) Cash excess (Deficiency) 0 Minimum cash balance Amount to borrow (repay) Borrowing (Repayments) 65 Rounded to increment of $1,000 66