question 5 is continuation of question 4 and the data for question 4 is given
D m B 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 5 6 1 Beginning cash balance invested by owners s 59,000 7 Quarter 1 Quarter 2 Quarter 3 Quarter 4 8 2. Sales by quarter (as % of total projected sales) 9 249 27% 28% 2196 10 11 3. Type of collections from customers 12 Cash Sales 3596 Credit Sales (accounts 13 receivable) 6596 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 S. 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 23 first quarter $ 138,000 24 25 7 Quarterly income tax payments paid in cash S 2.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 Case Intro Group Members 91 Q2 04 195 B 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 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 33 34 35 36 Percent of Sales 37 Estimated Sales 38 Mountain Sports Cash Budget For the year ended Decernber 31st Quarter 2 3 24% 27% 28% $120,960 $136,080 $141 120 4 21% $105,840 Year Summary 10096 $504,000 39 CASH BALANCE, Beginning $ 59.000 40 Collections from customers 41 Cash Sales 42 Credit Sales 43. CASH AVAILABLE 44 Less: Cash Payments 45 Merchandise purchases (COGS) 46 Sales Commissions 47 Advertising 18 Property Taxes 49 Rent 50 Salaries & Wages 51 Equipment Purchase Case Intro Group Members 01 Q2 Q5 Q6 Ready on c A 3 CASH AVAILABLE 4 Less: Cash Payments 45 Merchandise purchases (COGS) 46 Sales Commissions 47 Advertising 18 Property Taxes 49 Rent 50 Salaries & Wages 51 Equipment Purchase 52 Income tax Installment 53 Total Disbursements 54 Cash Excess Deficiency) 55 Financing (Note 1) 56 Borrow Repayment of Principal (show as negative) 57 58 Net Financing 59 Cash Balance, Ending 60 61 Note 1. Financing Calculations (you may use the space below for calculations) 62 cash excess (Deficiency 0 63 Minimum cash balance 64 Amount to borrow (repay) Borrowing (Repayments) Rounded to increment of 65 $1,000 66 67 60 Case Intro Group Members 02 03 Q4 Q5 026 Question 4 - Performance Measurement (10 marks The president feels very strongly that Mountain Sports should expand operations to a second location. She has even found a prime location in Canmore, Alberta, One of the great things about Canmore is its proximity to the mountains, and its only about 10 minutes away from this beautiful, vibrant and internationally known Banff tourist town. Research indicates that the Canmore market is well suited to both cross-country skis and bikes that competition is fairly $ 208,000 4 The investment in assets (cash, inventory, equipment) required for the new 1695 5 Minimum required return on investments 24% 6 Actual 2019 return on investment of the original location 7 8 Management has provided the following income statement to the bank manager the expected Static Budget 9 Amount 10 Sales in Units 4032 11 Sales 504,000 100% 12 Less: Variable Costs 13 Cost of Goods Sold 219,000 43% 14 Sales Commissions 65520 13% 15 Total Variable costs 284 520 56% 16 Contribution Margin 219,480 4496 17 Less: Fixed Costs: 18 Advertising 23 000 Property Taxes 9,000 20 54.000 21 Salaries & Wages 112,000 22 Total Fixed Costs 198.000 23 Net Operating Income 21480 24 25 Part A: (4 marks) Calculate the following performance measurements for the proposed Canmore expansion: 26 Margin 27 19 Rent 28 Turnover (use investment in assets in equation) 29 30 Return on investment 31 32 Residual income 33 Case Intro Group Members 01 02 03 12 05 | 06 + D m B 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 5 6 1 Beginning cash balance invested by owners s 59,000 7 Quarter 1 Quarter 2 Quarter 3 Quarter 4 8 2. Sales by quarter (as % of total projected sales) 9 249 27% 28% 2196 10 11 3. Type of collections from customers 12 Cash Sales 3596 Credit Sales (accounts 13 receivable) 6596 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 S. 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 23 first quarter $ 138,000 24 25 7 Quarterly income tax payments paid in cash S 2.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 Case Intro Group Members 91 Q2 04 195 B 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 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 33 34 35 36 Percent of Sales 37 Estimated Sales 38 Mountain Sports Cash Budget For the year ended Decernber 31st Quarter 2 3 24% 27% 28% $120,960 $136,080 $141 120 4 21% $105,840 Year Summary 10096 $504,000 39 CASH BALANCE, Beginning $ 59.000 40 Collections from customers 41 Cash Sales 42 Credit Sales 43. CASH AVAILABLE 44 Less: Cash Payments 45 Merchandise purchases (COGS) 46 Sales Commissions 47 Advertising 18 Property Taxes 49 Rent 50 Salaries & Wages 51 Equipment Purchase Case Intro Group Members 01 Q2 Q5 Q6 Ready on c A 3 CASH AVAILABLE 4 Less: Cash Payments 45 Merchandise purchases (COGS) 46 Sales Commissions 47 Advertising 18 Property Taxes 49 Rent 50 Salaries & Wages 51 Equipment Purchase 52 Income tax Installment 53 Total Disbursements 54 Cash Excess Deficiency) 55 Financing (Note 1) 56 Borrow Repayment of Principal (show as negative) 57 58 Net Financing 59 Cash Balance, Ending 60 61 Note 1. Financing Calculations (you may use the space below for calculations) 62 cash excess (Deficiency 0 63 Minimum cash balance 64 Amount to borrow (repay) Borrowing (Repayments) Rounded to increment of 65 $1,000 66 67 60 Case Intro Group Members 02 03 Q4 Q5 026 Question 4 - Performance Measurement (10 marks The president feels very strongly that Mountain Sports should expand operations to a second location. She has even found a prime location in Canmore, Alberta, One of the great things about Canmore is its proximity to the mountains, and its only about 10 minutes away from this beautiful, vibrant and internationally known Banff tourist town. Research indicates that the Canmore market is well suited to both cross-country skis and bikes that competition is fairly $ 208,000 4 The investment in assets (cash, inventory, equipment) required for the new 1695 5 Minimum required return on investments 24% 6 Actual 2019 return on investment of the original location 7 8 Management has provided the following income statement to the bank manager the expected Static Budget 9 Amount 10 Sales in Units 4032 11 Sales 504,000 100% 12 Less: Variable Costs 13 Cost of Goods Sold 219,000 43% 14 Sales Commissions 65520 13% 15 Total Variable costs 284 520 56% 16 Contribution Margin 219,480 4496 17 Less: Fixed Costs: 18 Advertising 23 000 Property Taxes 9,000 20 54.000 21 Salaries & Wages 112,000 22 Total Fixed Costs 198.000 23 Net Operating Income 21480 24 25 Part A: (4 marks) Calculate the following performance measurements for the proposed Canmore expansion: 26 Margin 27 19 Rent 28 Turnover (use investment in assets in equation) 29 30 Return on investment 31 32 Residual income 33 Case Intro Group Members 01 02 03 12 05 | 06 +
