Solution to Question 4 Part A Cash Budget **Round to nearest dollar. Round percentages to one decimal point. NOTE: Yellow Cells are Protected. You cannot Select or Change the Protected Cells. COMPLETE ALL CELLS IN GREEN Year Qtr. 3 Total Percent of Sales Estimated Sales Qtr. 1 15% STAR AO Otr. 2 5% SA 550 25% $827 750 Qtr. 4 55% $721,050 100% $1,311.000 Cash Balance, Beginning 55,000 $ 25,000 Cash Sales Collections Credit Sales Collections Total Cash Available $ 1.041,528 Less: Cash Disbursements Initial Investment in Assets $ 198,000 Total Disbursements $ 417,125 $ 239 125 Cash Excess Deficiency) $ 253,96a Financing: - 44.912.50 Additional Borrow Repayments (-) Original Line of Credit Borrow/Repayments(-) Net Financing 198.000 it's okay if the total borrowing/repayments for the year is zero. It means you pald back everything you borrowed. Some numbers rounded to one decimal for balancing purposes. - $ 40.635 Cash Balance, Ending S 25,000 $ 87,027.50 VI. Question 4 Cash Budget Part B: (record response below) NOTE: This question relates to the Income Statement in Question 3. Is the company able to pay back the full line of credit within one year? Answer: Assume that the five owners of Mountain Sports Ltd. decide to collectively invest $55,000 of personal funds into the Canmore expansion. Mountain Sports will require an open line of credit to fund the remaining investment in assets of $198,000 (S253,000 investment in operating assets less $55,000 owner investment) and to cover cash deficiencies throughout the year. The maximum line of credit available is $350,000. It will be necessary to convince the bank manager of this new Canmore branch ability to repay its line credit within a year Part A: (answer in the Excel Solution Workbook file on the Question 3 tab) Prepare a cash budget for the first year of operation of 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. Assume sales by quarter are 15%, 5%, 25%, and 55% of annual sales. Assume that 55% of all sales are cash. The rest are credit sales (accounts receivables) which will be collected in the following quarter. Assume there are no uncollectible accounts. Assume the following inventory purchases are all paid in the quarter of the purchase. You can assume the COGS number already accounts for these inventory purchases. No further action is required for COGS in the cash budget. Q1 50,000 Q2 $ 40,000 Q3 80,000 Q4 150,000 Inventory Purchases $ $ $ Assume expenses are paid as follows (use income statement provided in Question Expenses paid on a monthly basis: Sales Commissions, Lease, Salaries, Advertis and Utilities. Expenses paid on a quarterly basis: Property Taxes The owners also purchased equipment for the new venture valued at $80,000. The equipment will be paid for in cash in the first quarter. Assume that the beginning cash balance is $55,000. This is the amount the owners are capable of investing in this new venture. A minimum of $25,000 cash balance is needed at the end of each quarter. Assume the company will borrow against the line of credit as required, and pay off any amount owing on the line of credit when funds are available. V. Question 3 ROI, Residual Income NOTE: This is a new question. Do not use the information from Questions 1 and 2. a. Management is evaluated based on return on investment and is wondering if they should expand into Canmore. If the company's current return on investment is 24%, would management accept the new project (expansion into Canmore)? Why or why not? Explain your answer in your own words. The president feels very strongly that Mountain Sports should expand operations to a secon location. She has even found a prime location in Canmore, Alberta, One of the great things about Canmore is its proximity to the mountains, ski-areas, hiking, biking and only about 10 m this beautiful, vibrant and internationally known Banff tourist town. Research indicates that the Canr market is well suited to both cross-country skis and mountain bikes and that competition is fairly lim V. Question 3 ROI, Residual Income (continued) Performance Measurements: ROI, Residual (RI) b. What is the major weakness of using retum on investment as a performance meas- ure? Explain your answer in your own words using case facts. Management has provided the following income statement to the bank manager the expected net income in the next year, c. If management is evaluated based on residual income, would they be inclined to ac- cept the project. Why or why not? Explain your answer in your own words. S 1,311,000 $ 897,000 115,000 1,012,000 299,000 $ Sales Variable Costs: Cost of Goods Sold Sales Commissions Total Variable Costs Contribution Margin Fixed Costs: Lease Salaries Advertising Property Taxes Utilities Total Fixed Costs Operating Income S 86,250 115,000 23,000 8,050 9,200 $ 241.500 57.500 Part A: (record responses below-show all calculations) Using the above projected income statement, calculate the following performance measures for the proposed Canmore expansion: ROI, return on investment (Note: Average operating assets are $253,000) RI, residual income (14% is considered an appropriate return) Part B: (record responses below) Analysis of Performance