please answer question 5 only. kindly see question 4 for reference. thank you! will upvote for correct answer.

File Home Insert Draw Page Layout Custom 11 Enable Content Manage Workbook Links C50 Calibri B wa A... 2 SECURITY WARNING External Data Connections have been disabled Xfx=850 E 1 Question 5 - Cash Budget (30 marks) 2 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. F D B G 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 7 53,000 8 9 2. Sales by quarter (as % of total projected sales) Quarter 1 Quarter 2 20% 28% Quarter 3 Quarter 4 24% 28% 10 57% 16 11 3. Type of collections from customers: 12 Cash Sales 43% 13 Credit Sales (accounts receivable) 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 18 Merchandise purchases (cost of goods sold) are all paid in the quarter following purchase. (Quarter 1 purchases are 20 5. Operating expenses 21 All other operating expenses (all expenses except cost of goods sold) are paid on a monthly basis. Case Intro Q1 Q2 Q3 04 05 06 + Calculation Mode: Automatic Workbook Statistics 19 B D F G Calibri 11 BA G A Custom SECURITY WARNING External Data Connections have been disabled Enable Content Manage Workbook Links -50 Xfx =B50 A E 19 20 5. Operating expenses 21 All other operating expenses (all expenses except cost of goods sold) are paid on a monthly basis. 22 23 6. Required investment in equipment paid in cash in the first quarter $ 136,000 24 25 7. Quarterly income tax payments paid in cash $ 8,000 26 27 8. Minimum cash balance $ 24,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 quarter. All borrowing and payments are made in increments of $1,000. Interest on borrowing can be ignored. BO 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. 2 Mountain Sports Cash Budget For the year ended December 31 33 34 Quarter Year Summn Note Number SECURITY WARNING External Data Connections have been disabled Enable Content Manage Workbook Unks FS4 x fe D G H Mountain Sports Cash Budget For the year ended December 31 33 34 35 36 Percent of Sales 37 Estimated Sales Note from instructor 1 20% $105,200 Quarter 2 28% $147,280 3 24% $126,240 4 28% $147,280 Year Summary 100% $526,000 Total sales amount taken from question 4 Remember to refer to question 4 as this cash budget is a continuation of the Canmore expansion introduced in Question 4. This will be important for the cash disbursements as well! $ 53,000 24,706 $ 24,5585 24,6655 53,000 45,236 63,330 83,950 171.986 54,283 71,957 150,798 63,330 83.950 171,945 226,180 239,856 519,036 98,236 38 39 CASH BALANCE, Beginning 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 48 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 57 negative) 58 Net Financing 59 Cash Balance, Ending 60 Nota 1: Financineralulanne 15,780 5,250 2,500 10,250 25,750 136,000 8,000 203,5301 -105,294 Cash Balance, Ending Note 1: Financing Calculations Cash excess (Deficiency) # Minimum cash balance Amount to borrow (repay) -105,294 24,000 81,294 Borrowing (Repayments) Rounded to increment of $1,000 > A Case Intro Q1 Q2 Q3 24 25 26 + lculation Mode: Automatic Workbook Statistics SECURITY WARNING External Data Connections have been disabled 53 ms B13 fx 223000 B D E A Question 4 - Performance Measurement (8 marks) 1 2 Ils 9 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 limited. es 3 4 The investment in assets (cash, inventory, equipment) required for the new location is $ 174,000 5 Minimum required return on investments 16% 6 Actual 2019 return on investment of the original location 17% 7 8 Management has provided the following income statement to the bank manager the expected net income in Static Budget % 9 Amount 10 Sales in Units 4,208 11 Sales 526,000 100% 12 Less: Variable Costs: 13 Cost of Goods Sold 223,000! 42% 14 Sales Commissions 78,900 15% 15 Total Variable Costs 301,900 57% 16 Contribution Margin + 224,100 43% 17 Less: Fixed Costs: 18 Advertising 21,000 19 Property Taxes 10,000 20 Rent 41,000 21 Salaries & Wages 103,000 22 Total Fixed Costs 175,000 23 Net Operating Income 49.100 24 25 Part A: (4 marks) Calculate the following performance measurements for the proposed Canmore expansion: pps B13 Xfx 223000 B C D E 9% 24 25 Part A: (4 marks) Calculate the following performance measurements for the proposed Canmore expansion: 26 Margin (see Chapter 11 notes) 27 28 Turnover (use investment in assets in equation) 3.0 29 30 Return on Investment 28% 31 32 Residual Income $ 21,260 33 34 Part B: Analysis (4 marks) Explain in your own words using case data. Marks will not be awarded for textbook definitions a. If management is evaluated based on ROI, will the project be accepted (expansion into Canmore)? Why or 35 why not? Yes, If management is evaluated based on ROI, the project would be accepted. The company has set its preferences of a minimum return of investment of 16% and the proposed return on investment gave a 28% calculation. A higher ROI percentage indicates that the investment gains of a project are favourable to their costs. In economic terms, it is one way of relating profits to capital invested. + 36 37 b. If management is evaluated based on residual income, will the expansion into Canmore be accepted? Why or why not? = Case Intro Q1 Q2 Q3 Q4 Q5 Q6 + Calculation Mode: Automatic