please help me with formula

help please answer this question with excel formulas and i have put question number 4 pictures because it's related

Question 5. Cash Budget (30 marks) 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. 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): 1. Beginning cash balance invested by owners $ 60,000 Quarter 1 Quarter 2 Quarter 3 Quarter 4 2. Sales by quarter (as of total projected sales) 29% 30% 29% 12% . 1 Type of collections from customers: 2 Cash Sales 3 Gedit Sales (accounts receivable 44% 56% S 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 oldalaidinulor following purchase Onarter Lue 4. Merchandise purchases Merchandise purchases (cost of poods sold) are all paid in the quarter following purchase. (Quarter 1 purchases are bought in Quarter 1 but paid for in quarter 2). S. Operating expenses All other operating expenses (all expenses except cost of goods sold) are paid on a monthly basis. 6. Required investment in equipment paid in cash in the first quarter $ 136,000 7. Quarterly income tax payments paid in cash $ 7,000 8. Minimum cash balance $ 30,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. 1 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 D G H B C Mountain Sports Cash Budget For the year ended December 31 Quarter 2 Note from instructor 4 1 29% $151,380 Year Summary 100% 5522,000 30 3 29% $151,380 Percent of Sales Estimated Sales 125 $156,600 $62,640 Total sales amount taken from question 4. Remember to refer to questions 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 CASH BALANCE, Beginning 5 60,000 Collections from customers Cash Sales 2 Credit Sales 3 CASH AVAILABLE Cash Payments 3 Merchandise purchases (CGS) Sales Commissions 17 Atvertising Property Tas B D G H Property Taxes Rent 50 Salaries & Wages 51 Equipment Purchase Income tax installment 53 Total Disbursements 54 Cash Erces (Deficiency 55 Financing (Note 11 56 Borrow Repayment of Principal show as negative) 58 Net Financing 39 Cash Balance, Ending 61 Note 1: Financing Calculation Cash excess (Deficiency Minimum cash balance 94 Amount to borrow trepal G N Questions - Cash Budget (30 marks) Mountain Sports has equired an open line of credit up to a maximum of $350,000, it will be necessary to convince the tank manager of this new Canmore branch ability to repay its line of credit including any interest 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 to complete the cash budget); 1. Beginning cash balance invested by owners $ 60,000 Quarter 1 Quarter 2 Quarter 3 Quarter 4 2. Sales by quarter las oftal projected sales 29% 30% 29% 12% 3. Type of collections from customers: Cash Sales Credit Sales (accounts receivable) 44% 56% Cash sales are collected in the quarter of the sale, all credit sales are collected in the quarter after the sale 4. Merchandise purchases Merchandise purchases (cost of goods sold) are all paid in the quarter following purchase. (Cuarter 1 purchases are bought in Quarter 1 hut paid for in quarter 2) D 29% 306 20% 12 15 10 11 3. Type of collections from customers: 12 Cash Sales 1 Credit Sales (accounts receivable) 44 56% 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 T8 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. 2 6. Required investment in equipment paid in cash in the first quarter 3 s 136,000 4 57. Quarterly income tax payments paid in cash $ 7,000 7 B. Minimum cash balance $ 30,000 0 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 home and payments are made in increments of $1.000. Interest on borrowing can be ignored. H + Required. Prepare a cash budget for the fint year of operation in Canmore by quarter and in total Show clearly on your budget the quarters) in which borrowing will be needed and the quarters in which repayments can be made, as requested by the company's bank Mountain Sports Cash Budget For the year ended December 31 Yea Note from instructor Summary Quarter 21 ON $156, 600 1 294 S151,180 Percent of Sales Estimated Glos a 29% 3151,380 4 12% $62,640 100% 5522.000 Total sales amount taken from Question Remember to refer to question as the Cashbudget is a contation of the Carmore expansion introduced in resto 4. This will be important for the cash disbursements as well! CASH BALANCE, leginning 60.000 Collections from customers Cash Sales Credit Sales CASH AVAILABLE tess Cash Payments Merchandise purchases (CGS) Sales Commissions Advertising Property Tax Rent Salaries Wars Equipment Purchase Income tax retallment Tatal Dicturements Cash Excess (Deficiency Financing (Note 1) Borrow Repayment of Principal (show as negative Net Financing Casti Galance, Ending Note 1. Financing Calculations Cash excess (Deficiency Minimum cash balance Amount to borrow (repay) . 56 Borrow Repayment of Principal (show as negative) 57 58 Net Financing 59 Cash Balance, Ending 60 61 Note 1: Financing Calculations 62 Cash excess (Deficiency) 63 Minimum cash balance 54 Amount to borrow (repay) Borrowing (Repayments) Rounded to 55 increment of $1,000 56 57 58 59 10 21 22 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 The investment in assets (cash, inventory, equipment required for the new Minimum required return on investments Actual 2019 return on investment of the original location $ 168,000 16% 17% Management has provided the following income statement to the bank manager the expected Static Budget % Amount Sales in Units 4,176 Sales 522,000 100% Less: Variable Costs: Cost of Goods Sold 216,000 419 Sales Commissions 78,300 15% Total Variable Costs 294 300 56% Contribution Marrin 227,700 44% Less: Foxed Costs: Advertising 20,000 Management has provided the following income statement to the bank manage the expected Static Budget % Amount Sales in Unit 4,176 Sales 522,000 100% Les Variable costs Cost of Goods Sold 216,000 41% Sales Commissions 78 300 15% Total Variable costs 2942300 56% Contribution Marin 222,700 44% Less: Fised Costs Advertising 20,000 Property Tones 10,000 Rent 41,000 Salaries & Wages 104,000 Total Fixed Costs 125,000 Net Operating Income 12,200 Part A: 14 martes Calculate the following performance measurements for the proposed Canmore expansion: Martin (see Chapter 11 notes) 10.10% 3 Turnover use Investment in assets in equations 5 Part A: (4 marks) Calculate the following performance measurements for the proposed Canmore expansion Marine Chapter 11 notes 10.10% 3 Turnover investment in assets in equations 31.379 Return on investment Residual income -997.300 Part B: Analysis (4 marks) Explain in your own words using case data. Marks will not be awarded for textbook definitional a. If management is evaluated based on ROI will the project be accepted (expansion into Canmore)? Why or why not? ROK 2015. ongimat caten was 17 but it change location or will be 31.30% which is more better b. I management is evaluated based on residual income, will the expansion into Canmore be accepted? Why or why not? Therefore, we should accept the project