Search (Alt +O) Excel dkaur24 8- Saved Review View Help Data Editing Formulas File Home Insert Draw Page Layout General Merge v Av v av V V ... B & Calibri V11 Links Disabled Links to external workbooks are not supported and have been disabled. Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use several cloud-backed services. You optional cloud-backed services are provided to you under the Microsoft Services Agreement, Leam more A1 fx Question 5 - Cash Budget (30 marks) A D B E F G H 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. 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): 4 5 6 7 1. Beginning cash balance invested by owners $ 53,000 8 Quarter 1 Quarter 2 Quarter 3 Quarter 4 9 2. Sales by quarter (as % of total projected sales) 20% 28% 24% 28% 10 11 3. Type of collections from customers: 12 Cash Sales 43% 13 Credit Sales (accounts receivable) 57% 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 Case Intro 01 Q2 03 Q4 Q5 Q6 + Calculation Mode: Automatic Workbook Statistics Type here to search O TT optional cloud-backed services are provided to you under the Microsoft Services Agreement. Ulyanization's admin allows you to use several cloud Learn more A1 V fx Question 5 - Cash Budget (30 marks) G B D E F 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 18 Merchandise purchases (cost of goods sold) are all paid in the quarter following purchase. (Quarter 1 purchases are 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. 30 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 A Case Intro Q1 02 Calculation Mode: Automatic 03 04 Q5 06 Workbook Statistics + Type here to search O File Home Insert Draw Page Layout Formulas Data Review View Help E Dr V Calibri - 11 B B V Av Merge v Links Disabled Links to external workbooks are not supported and have been disabled. Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use several cloue optional cloud-backed services are provided to you under the Microsoft Services Agreement. Learn more A1 fx Question 5 - Cash Budget (30 marks) A B D E 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. F G 30 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 Mountain Sports Cash Budget For the year ended December 31 33 34 35 36 Percent of Sales 37 Estimated Sales 1 20% $105,200 Quarter 2. 28% $147,280 4 24% $126,240 Year Summary 100% $526,000 28% $147,280 Notef 38 39 CASH BALANCE, Beginning 40 Collections from customers: 41 Cash Sales 42 Credit Sales $ 53,000 Total se Remem cash bu Canmor 4. This disbursel Case Intro Q1 02 Q3 04 Q5 Calculation Mode: Automatic Workbook Statistics Type here to search Q6 + U File Home Insert Draw Page Layout Formulas Data Review View Help Calibri 11 B Ava A V ab Merge Links Disabled Links to external workbooks are not supported and have been disabled. Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use several optional cloud-backed services are provided to you under the Microsoft Services Agreement. Learn more A1 fx Question 5 - Cash Budget (30 marks) A D B Cash Budget For the year ended December 31 E F 33 34 35 36 Percent of Sales 37 Estimated Sales 1 20% $105,200 Quarter 2 28% $147,280 3 24% 4 Year Summary 100% $526,000 28% $147,280 $126,240 $ 53,000 C 4. di 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 = Case Intro Q1 Calculation Mode: Automatic 02 Q3 Q4 05 Workbook Statistics 06 Type here to search O Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use several clou optional cloud-backed services are provided to you under the Microsoft Services Agreement. Learn more A1 V fx Question 5 - Cash Budget (30 marks) B D E F G 47 Tash 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 61 Note 1: Financing Calculations 62 Cash excess (Deficiency) 63 Minimum cash balance Case Intro 01 02 Calculation Mode: Automatic Q3 04 Q5 Workbook Statistics Q6 + O Type here to search OBI Calibri 11 V B V av Av. V Links Disabled Links to external workbooks are not supported and have been disabled. Me Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use se optional cloud-backed services are provided to you under the Microsoft Services Agreement. Learn more A1 fx Question 5 - Cash Budget (30 marks) V B D E F 56 A 52 Income tax Installment 53 Total Disbursements 54 Cash Excess (Deficiency) 55 Financing (Note 1) Borrow Repayment of Principal (show as 57 negative) 58 Net Financing 59 Cash Balance, Ending 60 61 Note 1: Financing Calculations 62 Cash excess (Deficiency) 63 Minimum cash balance 64 Amount to borrow (repay) Borrowing (Repayments) Rounded to 65 increment of $1,000 66 67 68 69 70 71 Case Intro Calculation Mode: Automatic 01 02 Q3 Q4 Q5 Workbook Statistics Q6 + 1 O Type here to search Search (Alt +O) Excel dkaur24 8- Saved Review View Help Data Editing Formulas File Home Insert Draw Page Layout General Merge v Av v av V V ... B & Calibri V11 Links Disabled Links to external workbooks are not supported and have been disabled. Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use several cloud-backed services. You optional cloud-backed services are provided to you under the Microsoft Services Agreement, Leam more A1 fx Question 5 - Cash Budget (30 marks) A D B E F G H 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. 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): 4 5 6 7 1. Beginning cash balance invested by owners $ 53,000 8 Quarter 1 Quarter 2 Quarter 3 Quarter 4 9 2. Sales by quarter (as % of total projected sales) 20% 28% 24% 28% 10 11 3. Type of collections from customers: 12 Cash Sales 43% 13 Credit Sales (accounts receivable) 57% 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 Case Intro 01 Q2 03 Q4 Q5 Q6 + Calculation Mode: Automatic Workbook Statistics Type here to search O TT optional cloud-backed services are provided to you under the Microsoft Services Agreement. Ulyanization's admin allows you to use several cloud Learn more A1 V fx Question 5 - Cash Budget (30 marks) G B D E F 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 18 Merchandise purchases (cost of goods sold) are all paid in the quarter following purchase. (Quarter 1 purchases are 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. 30 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 A Case Intro Q1 02 Calculation Mode: Automatic 03 04 Q5 06 Workbook Statistics + Type here to search O File Home Insert Draw Page Layout Formulas Data Review View Help E Dr V Calibri - 11 B B V Av Merge v Links Disabled Links to external workbooks are not supported and have been disabled. Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use several cloue optional cloud-backed services are provided to you under the Microsoft Services Agreement. Learn more A1 fx Question 5 - Cash Budget (30 marks) A B D E 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. F G 30 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 Mountain Sports Cash Budget For the year ended December 31 33 34 35 36 Percent of Sales 37 Estimated Sales 1 20% $105,200 Quarter 2. 28% $147,280 4 24% $126,240 Year Summary 100% $526,000 28% $147,280 Notef 38 39 CASH BALANCE, Beginning 40 Collections from customers: 41 Cash Sales 42 Credit Sales $ 53,000 Total se Remem cash bu Canmor 4. This disbursel Case Intro Q1 02 Q3 04 Q5 Calculation Mode: Automatic Workbook Statistics Type here to search Q6 + U File Home Insert Draw Page Layout Formulas Data Review View Help Calibri 11 B Ava A V ab Merge Links Disabled Links to external workbooks are not supported and have been disabled. Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use several optional cloud-backed services are provided to you under the Microsoft Services Agreement. Learn more A1 fx Question 5 - Cash Budget (30 marks) A D B Cash Budget For the year ended December 31 E F 33 34 35 36 Percent of Sales 37 Estimated Sales 1 20% $105,200 Quarter 2 28% $147,280 3 24% 4 Year Summary 100% $526,000 28% $147,280 $126,240 $ 53,000 C 4. di 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 = Case Intro Q1 Calculation Mode: Automatic 02 Q3 Q4 05 Workbook Statistics 06 Type here to search O Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use several clou optional cloud-backed services are provided to you under the Microsoft Services Agreement. Learn more A1 V fx Question 5 - Cash Budget (30 marks) B D E F G 47 Tash 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 61 Note 1: Financing Calculations 62 Cash excess (Deficiency) 63 Minimum cash balance Case Intro 01 02 Calculation Mode: Automatic Q3 04 Q5 Workbook Statistics Q6 + O Type here to search OBI Calibri 11 V B V av Av. V Links Disabled Links to external workbooks are not supported and have been disabled. Me Thanks for using Office! We've made some updates to the privacy settings to give you more control. Your organization's admin allows you to use se optional cloud-backed services are provided to you under the Microsoft Services Agreement. Learn more A1 fx Question 5 - Cash Budget (30 marks) V B D E F 56 A 52 Income tax Installment 53 Total Disbursements 54 Cash Excess (Deficiency) 55 Financing (Note 1) Borrow Repayment of Principal (show as 57 negative) 58 Net Financing 59 Cash Balance, Ending 60 61 Note 1: Financing Calculations 62 Cash excess (Deficiency) 63 Minimum cash balance 64 Amount to borrow (repay) Borrowing (Repayments) Rounded to 65 increment of $1,000 66 67 68 69 70 71 Case Intro Calculation Mode: Automatic 01 02 Q3 Q4 Q5 Workbook Statistics Q6 + 1 O Type here to search