1 Question 5 - Cash Budget (30 marks) G H N 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. 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): 45678 1. Beginning cash balance invested by owners $ 56,000 Quarter 1 Quarter 2 Quarter 3 Quarter 4 30% 23% 19% 2. Sales by quarter (as % of total projected sales) 9 10 11 3. Type of collections from customers: 12 Cash Sales 28% + 40% 13 Credit Sales (accounts receivable) 60% 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 are 8 bought in Quarter 1 but paid for in quarter 2). 9 05. Operating expenses D G H E 16 27 4. Merchandise purchases Merchandise purchases (cost of goods sold) are all paid in the quarter following purchase. (Quarter 1 purchases are 8 bought in Quarter 1 but paid for in quarter 2). 9 05. Operating expenses 1 All other operating expenses (all expenses except cost of goods sold) are paid on a monthly basis. 2 6. Required investment in equipment paid in cash in the first quarter 8 $ 132,000 7. Quarterly income tax payments paid in cash 7,000 8. Minimum cash balance $ 25,000 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. 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. Mountain Sports Cash Budget For the year ended December 31 G H 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 For the year ended December 31 33 34 35 36 Percent of Sales 37 Estimated Sales 38 28% Quarter 2 30% $157,200 Note from instructor 23% $120,520 $146,720 Year Summary 100% $524,000 4 19% $99,560 39 CASH BALANCE, Beginning $ 56,000 Total sales amount taken from Question 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! 40 Collections from customers: 41 Cash Sales 42 Credit Sales 43 CASH AVAILABLE 44 Less: Cash Payments 45 Merchandise purchases (COGS) 46 Sales Commissions JUUUU 40 Collections from customers: G H 4. This will be imp disbursements as 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 19 Rent 0 Salaries & Wages Equipment Purchase Income tax Installment Total Disbursements Cash Excess (Deficiency) Financing (Note 1) Int D E F G H 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 9 Cash Balance, Ending 0 1 Note 1: Financing Calculations Cash excess (Deficiency) B Minimum cash balance Amount to borrow (repay) Borrowing (Repayments) Rounded to increment of $1,000 Meresuual income, the result will be positive, so the investment in new project B 1 Question 4 - Performance Measurement (8 marks) E F 2 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 3 4 The investment in assets (cash, inventory, equipment) required for the new $ 177,000 5 Minimum required return on investments 6 16% Actual 2019 return on investment of the original location 18% 7 8 Management has provided the following income statement to the bank manager the expected Static Budget % 9 Amount 10 Sales in Units 4,192 11 Sales 524,000 100% 12 Less: Variable Costs: 13 Cost of Goods Sold 223,000 43% 14 Sales Commissions 78,600 15% 15 Total Variable Costs 301,600 58% 116 Contribution Margin 222,400 42% 17 Less: Fixed Costs: 18 Advertising 21,000 19 Property Taxes + 9,000 20 Rent 46,000 21 Salaries & Wages 102,000 27 2 on D E F Total Fixed Costs Net Operating Income G 178,000 44,400 Part A: (4 marks) Calculate the following performance measurements for the proposed Canmore expansion: Margin (see Chapter 11 notes) 8% Turnover (use investment in assets in equation 3.0 Return on Investment 25% e Residual Income 16,080 # 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 5 Canmore)? Why or why not? Accept because the return on investment of the company from the project is higher than minimum required return on investment 36 37 b. If management is evaluated based on residual income, will the expansion into Canmore be Case Intro 2 07 01 or B D E F Ourworowy w we Accept because the return on investment of the company from the project is higher than minimum required return on investment 36 37 b. If management is evaluated based on residual income, will the expansion into Canmore be 38 accepted? Why or why not? Accept because based on the residual income, the result will be positive, so the investment in new project would be accepted 39 40 41 42 Question 6 - Variance Analysis (15 marks) G H Assume that owners decided to go ahead with the Canmore expansion (first introduced in question 4). The junior accountant has prepared the following report to compare the static budget (from question 4) to the actual results. The owners have asked you to complete a variance analysis. 4 Required: Part A (4 marks)- Create a Static Budget Report Variance Analysis, indicating whether variances are favorable (F) or unfavorable (U). All variance amounts should be shown as positive numbers, 5 7 Sales in Units 8 Sales Static Budget Actual Amount Results 4,192 4,520 $ 524,000 S542.400 Variance Favorable or Amount Unfavorable 328 Favorable 18,400 Favorable 223,000 216,000 7,000 Favorable + 65,088 13,512 Favorable 78,600 301,600 20,512 Favorable 9 Less: Variable Costs: 10 Cost of Goods Sold 11 Sales Commissions 12 Total Variable Costs 13 Contribution Margin 14 Less: Fixed Costs: 15 Total Fixed Costs 16 Net Operating Income 17 281.088 261,312 222,400 38,912 Favorable 198,000 20,000 Unfavorable 178,000 44,400 63,312 18.912 Favorable F G H 1 3 X What is the weakness of using a static budget report to evaluate performance? (2 marks) 18 A static budget does not compare the cost incurred at same level activity is considered and the results are not meaningful for evaluation and to take corrective action 19 20 Required: Part B (6 marks) - The owners can see that the company sold a different amount of units than 21 budgeted. They have asked you to create a flexible budget report. 22 23 Flexible Budget Amount Favorable (F) or Unfavorable (U) Actual Variance Results Amount 4,520 542,400 12 24 Sales in Units 25 Sales 26 Less: Variable Costs: 27 Cost of Goods Sold 28 Sales Commissions 29 Total Variable Costs 216,000 65.08 281,088 30 Contribution Margin 261,312 31 Less: Fixed Costs 198.000 32 Net Operating Income 63,312 B D Less: Fixed Costs F Net Operating Income 198,000 63,312 Management was pleased on the results based on the static budget report. Should they be pleased? What does the flexible budget tell you? What are your recommendations to management based on the flexible budget report? (3 marks) & 3