A B D G H K M N S Check figures In Budger and Cash components March budgeted merchandise purchases March budgeted cash receipts from customers March cash payments for merchandise in Cash budgets January ending cash balance March ending cash balance 206.000 494,831 105,500 214315 189.258 Ope S 1 Name 2 Purpose 3. To use Excel, a common tool used for budgeting, to prepare components of the master budget. 4 To build a cash budget from its components 5 Instructions 6 Using the prior year ending balance sheet and budget information below create a three month budget in the worksheets provided. 7 Review the balance sheet and thoroughly read the budget information before you proceed. 8 The letters in the budget information and assumptions correspond to the required budgets, some budgets use the same information. In the budget data assumptions some items refer you to the balance sheet. Use cell referencing to replace "Refer to the balance sheet" with the amount. 10 Use cell referencing and formulas for all data input and calculations. 11 Required: 12 in the worksheet Budget and Cash components complete the following 13 1. Monthly sales budgets. 14 2. Monthly merchandise purchases budgets. 15 3. Monthly selling expense budgets. 16 4. Cash receipts budget 17 5. Cash payments for merchandise 18 Optional, but highly recommended: 19 in the worksheet Cash budget 20 6. Monthly cash budgets. 21 22 TARS Computer Corporation Estimated Balance Sheet 12/31/19 23 Assets Liabilities and Equity 24 Cash 35.000 Accounts payable $ 345.000 25 Accounts receivable 520.000 Bank loan payable 11.000 26 Inventory 95.000 Taxes payable (due 3/15/2020) 88.000 27 Total current assets 650.000 Total liabilities 28 Equipment 612.000 Common Stock 472.000 29 Less: accumulated depr 76.500 Retained earnings 269.500 30 Equipment, net 535,500 Total stockholders' equity 741.500 31 Total assets $ 1.185.500 Total liabilities and equity 1,185,500 32 Budget information a. The company's single product is purchased for $20 per unit and resold for 557 per unit. The expected inventory level of 4,750 units on December 31, 2019, is more than management's desired level, which is 20% of the next month's expected sales (in units). Expected sales are January, 6,500 units; February, 9,000 units; 33 March, 10,500 units; and April, 9.500 units. b. Cash sales and credit sales represent 25% and 75%, respectively, of total sales. Of the credit sales, 63% is collected in the first month after the month of sale and 37% in the second month after the month of sale. For the December 31, 2019, accounts receivable balance, 63% is collected in January 2020 37% in February 2020. 34 c. Merchandise purchases are paid for as follows: 20% in the first month after the month of purchase and 80% in the second month after the month of purchase. 35 For the December 31, 2019 the accounts payable balance 20% is paid in January and the remaining 80% is paid in February 36 d. Sales commissions equal to 20% of sales are paid each month. Sales salaries (excluding commissions) are 584.000 per year. 37 e. General and administrative salaries are $12.000 per month. Maintenance expense equals 52.000 per month and is paid in cash. f. he following amounts for new equipment purchases are planned in the coming quarter: January, 531.200; February, 598.400; and March, 521,600. g. The company plans to buy land at the end of March at a cost of $175,000, which will be paid with cash on the last day of the month. h. The company has a working arrangement with its bank to obtain additional loans as needed. The interest rate is 12% per year, and interest is paid at each month-end based on the beginning balance. Partial or full payments on these loans can be made on the last day of the month. The company has agreed to maintain a minimum ending cash balance of $50,000 at the end of each month. 1. The income tax rate for the company is 21%. Income taxes on the first quarter's income will not be paid until April 15. The taxes payable from the prior year is 41 88.000 and will be paid 3/15/2020 $ S 444,000 S 38 39 40 - o Q 5 March . B D E F G H K M N 1 Budget information a. The company's single product is purchased for $20 per unit and resold for $57 per unit. The expected inventory level of 4.750 units on December 31, 2019, is more than management's desired level, which is 20% of the next month's 2 expected sales lin units) Expected sales are lanuary 6500 units: February 9000 units: March 10500 units and April 2500 units b. Cash sales and credit sales represent 25% and 75%, respectively, of total sales. Of the credit sales, 63% is collected in the first month after the month of sale and 37% in the second month after the month of sale. For the December 31, 3 2019, accounts receivable balance, 63% is collected in January 2020 37% in February 2020. Merchandise purchases are paid for as follows: 20% in the first month after the month of purchase and 80% in the second month after the month of purchase. For the December 31, 2019 the accounts payable balance 20% is paid in 4 January and the remaining 80% is paid in February d. Sales commissions equal to 20% of sales are paid each month. Sales salaries (excluding commissions) are $84,000 per year. 6 7 TARS Computer Corporation Estimated Balance Sheet 12/31/19 8 Assets Liabilities and Equity 9 Cash $ 35.000 Accounts payable $ 345,000 10 Accounts receivable 520,000 Bank loan payable 11.000 11 Inventory 95.000 Taxes payable (due 3/15/2020) 88.000 12 Total current assets $ 650.000 Total liabilities $ 444,000 13 Equipment 612.000 Common Stock 472.000 14 Less: accumulated depr 76.500 Retained earnings 269,500 15 Equipment, net 535,500 Total stockholders' equity 741.500 16 Total assets $ 1.185.500 Total liabilities and equity $ 1.185.500 17 18 19 Budget Assumptions from above General January February March April Sales Budgets Cash Receipts from Customers 20 a. Selling price per unit $57.00 January February, and March 2020 January, February, and March 2020 21 Cost per unit $20.00 a. January February March b January February 22 Budget Sales Units 6500 9,000 10.500 9.500 Budgeted Units Total sales 23 Units in inventory 12/31/19 4,750 Budgeted Unit Price Cash sales 24 Ratio of inventory to future sales 20% Budaeted Total Dollars Credit sales 25 b. Cash sales 25% Cash collections 26 Credit sales 75% Merchandise Purchases Budgets Receivables at 12/31/2019 27 Credit sales collections January, February, and March 2020 January credit sales 28 Collections in first month after 63% a January February March February credit sales 29 Collections in second month after 37% Next month's budgeted sales units Total cash from credit customers 30 Accounts Receivable 12/31/19 Refer to balance sheet Ratio of inventory to future sales Cash Sales 31 C Merchandise purchase payments Budgeted ending inventory units Total cash received 32 In first month after the purchase 30% Add budgeted sales units 33 In second month after the purchase 70% Required available merchandise units Cash Payments for Merchandise 34 Accounts Payable 12/31/19 Refer to balance sheet Deduct beginning inventory units January February, and March 2020 35 d. Sales commissions 20% Units to be purchased C. January February 36 Monthly sales salary $7,000 Budgeted cost per unit Credit purchases 37 Budaeted merchandise purchases Accts payable at 12/31/19 38 January purchases 39 February purchases 40 Selling Expense Budgets Total paid on purchases 41 January February, and March 2020 42 d. January February March 43 Budgeted sales 44 Sales commission percent 45 Sales commissions expense 46 Sales salaries 47 ITotal budaeted selling expenses March E B C D G H K 1 TARS Computer Corporation Estimated Balance Sheet 12/31/19 2 Assets Liabilities and Equity 3 Cash 35000 Accounts payable 345000 4 Accounts receivable 520000 Bank loan payable 11000 5 Inventory 95000 Taxes payable (due 3/15 88000 6 Total current assets =SUM(D3:D5) Total liabilities =SUM(13:15) 7 Equipment 612000 Common Stock 472000 8 Less: accumulated depr 76500 Retained earnings 269500 9 Equipment, net =+D7-08 Total stockholders' eq =+18+)7 10 Total assets =+E6+E9 Total liabilities and equ =+K6+K9 11 Budget information 12 e. General and administrative salaries are $12,000 per month. Maintenance expense equals $2,000 per month and is paid in cash. f. he following amounts for new equipment purchases are planned in the coming quarter: January, $31,200; February, $98,400; and March, $21,600. 13 14 9. The company plans to buy land at the end of March at a cost of $175,000, which will be paid with cash on the last day of the month. h. The company has a working arrangement with its bank to obtain additional loans as needed. The interest rate is 12% per year, and interest is paid at each month-end based on the beginning balance. Partial or full payments on these loans can be made on the last day of the month. The company has agreed to maintain a minimum ending cash balance of $50,000 at the end of each month. 15 i. The income tax rate for the company is 21%. Income taxes on the first quarter's income will not be paid until April 15. The taxes payable from the prior year is88,000 and will be paid 3/15/2020 General January February March 12000 2000 TARS Computer Corporation Cash Budgets January, February, and March 2020 January February 31200 98400 March 21600 175000 Refer to balance shee 50000 0.01 16 17 18 Budget Assumptions from above 19 General and Admin 20 Salaries per month 21 Maintenance expense per month 22 Equipment purchases 23 Land purchase 24 Loan balance 12/31/19 25 Required ending cash balance 26 Interest rate per month 27 Income taxes payable 28 29 30 31 32 33 34 35 36 37 38 88000 previous plus e. to i. Beginning cash balance Cash receipts from customers Total cash available Cash payments for: Merchandise Selling expenses General & admin salaries Maintenance expense Interest expense Taxes payable Purchases of equipment Purchase of land Total cash payments Preliminary cash balance Repayment of loan to bank Additional loan from bank Cadina cacbblanca 20 Instructions Budget and Cash components Cash budget Operating Cash example Cash budget example + N 1 16 . E H M Budget information a. The company's single product is purchased for $30 per unit and resold for 545 per unit. The expected inventory level of 5,000 units on December 31, 2019, is more than management's desired level, 2 which is 25% of the next month's expected sales (in units). Expected sales are: January 6,000 units; February. 8.000 units; March 10,000 units; and April, 9.000 units. b. Cash sales and credit sales represent 25% and 75%, respectively, of total sales. Of the credit sales, 60% is collected in the first month after the month of sale and 40% in the second month after the 3 month of sale. For the December 31, 2019, accounts receivable balance is $525.000. c. Merchandise purchases are paid for as follows: 20% in the first month after the month of purchase and 80% in the second month after the month of purchase. For the December 31, 2019 the accounts 4 payable balance 20% is paid in January and the remaining 80% is paid in February 5 d. Sales commissions equal to 20% of sales are paid each month. Sales salaries (excluding commissions) are 57.500 month. 6 7 Isle Corporation Estimated Balance Sheet 12/31/19 8 Assets Liabilities and Equity 9 Cash $ 36,000 Accounts payable S 360.000 10 Accounts receivable 525.000 Bank loan payable 15.000 11 Inventory 150,000 Taxes payable (due 3/15/2020) 90.000 12 Total current assets S 711,000 Total liabilities S 465,000 13 Equipment 540.000 Common Stock 472.500 14 Less: accumulated depr 67,500 Retained earnings 246.000 15 Equipment, net 472,500 Total stockholders' equity 718.500 Total assets $ 1.183.500 Total abilities and equity $ 1.183.500 17 18 19 Budget Assumptions from above General January February March April Sales Budgets 20 a. Selling price per unit $45.00 January, February, and March 2020 21 Cost per unit $30.00 a. January February March 22 Budget Sales Units 6000 8.000 10.000 9.000 Budgeted Units 6.000 8.000 10.000 23 Units in inventory 12/31/19 5.000 Budgeted Unit Price 45 45 45 24 Ratio of inventory to future sales 25% Budgeted Total Dollars $270.000 $360,000 $450,000 25 b. Cash sales 25% 26 Credit sales 75% Merchandise Purchases Budgets 27 Credit sales collections January, February, and March 2020 28 Collections in first month after 60% January Februar March 29 Collections in second month after 40% Next month's budgeted sales units 8,000 10.000 9.000 30 Accounts Receivable 12/31/19 525,000 Ratio of inventory to future sales 25% 251 258 Merchandise purchase payments Budgeted ending inventory units 2.000 2.500 2.250 32 In first month after the purchase 20% Add budgeted sales units 6,000 8,000 33 In second month after the purchase 80% Required available merchandise units 8.000 10.500 12.250 34 Accounts Payable 12/31/19 5 360.000 Deduct beginning inventory units -5.000 -2.000 - 2.500 35 d. 25 d. Sales commissions 20% Units to be purchased 3.000 8.500 9.750 36 Monthly sales salary $7.500 Budgeted cost per unit $30 $30 $30 37 22 Budgeted merchandise purchases $90.000 $255.000 $292.500 38 39 40 Selling Expense Budgets 41 January February, and March 2020 20 42 d. January February March 43 Budgeted sales $270.000 $360.000 $450.000 44 Sales commission percent 20% 20% 20% 45 Sales commissions expense 54.000 72.000 90.000 46 Sales salaries 7.500 7.500 7,5001 47 Total budgeted selling expenses $61.500 $79.500 $97.500 Cash Receipts from Customers January, February, and March 2020 b. January February March Total sales $270.000 $360,000 $450.000 Cash sales 67,500 90.000 112.500 Credit sales 202.500 270.000 337.500 Cash collections Receivables at 12/31/2019 315.000 210.000 January credit sales 121.500 81,000 February credit sales 162.000 Total cash from credit customers 315.000 331.500 243,000 Cash Sales 67,500 90.0001 112.500 Total cash received 382.500 421,500 355.500 a. 31 C 10,000 March $292.500 Cash Payments for Merchandise January February, and March 2020 C. January February Credit purchases $90,000 $255.000 Accts payable at 12/31/19 72.000 288.000 January purchases 18000 February purchases Total paid on purchases 72.000 306.000 72.000 51.000) 123.000 E F L 1 D G K M Isle Corporation Estimated Balance Sheet 12/31/19 2 Liabilities and Equity 3 Accounts payable $360,000 4 Bank loan payable 15,000 5 Taxes payable (due 3/15/2020) 90,000 6 $ 711,000 Total liabilities $ 465,000 7 Common Stock 472,500 8 Retained earnings 246,000 9 472,500 Total stockholders' equity 718,500 10 $ 1.183,500 Total abilities and equity $ 1.183,500 11 12 bo per month. Maintenance expense equals $3,000 per month and is paid in cash. 13 rchases are planned in the coming quarter: January, $72,000; February, $96,000; and March, $28,800. 14 f March at a cost of $150,000, which will be paid with cash on the last day of the month. th its bank to obtain additional loans as needed. The amount due on 12/31/19 is $15,000. The interest rate is 12% per year, and interest is paid at each artial or full payments on these loans can be made on the last day of the month. The company has agreed to maintain a minimum ending cash balance of 15 16 ncome taxes on the first quarter's income will not be paid until April 15. The taxes payable from the prior year is 90,000 and will be paid 3/15/2020 17 18 January February March 19 ISLE CORPORATION 20 Cash Budgets 21 January, February, and March 2020 22 72,000 96,000 28,800 previous plus e. to i. January March 23 150,000 Beginning cash balance $ 36,000 $182,850 $107,850 24 Cash receipts from customers 382500 421.500 355 500 25 Total cash available 418,500 604,350 463,350 26 Cash payments for: 27 90,000 Merchandise 72,000 306,000 123,000 28 Selling expenses 61,500 79,500 97,500 29 General & admin salaries 12,000 12.000 12,000 30 Maintenance expense 3,000 3,000 3,000 31 Interest expense 150 $0 32 Taxes payable 0 0 90000 33 Purchases of equipment 72.000 96,000 28,800 34 Purchase of land 0 0 150,000 35 Total cash payments 220 650 496 500 504300 36 Preliminary cash balance $197,850 $107,850 ($40,950) 37 Repayment of loan to bank - 15,000 38 Additional loan from bank 76.950 39 Ending cash balance $182 850 $107 850 $36.000 40 Loan balance, end of month $0 $0 $76 950 February A B D G H K M N S Check figures In Budger and Cash components March budgeted merchandise purchases March budgeted cash receipts from customers March cash payments for merchandise in Cash budgets January ending cash balance March ending cash balance 206.000 494,831 105,500 214315 189.258 Ope S 1 Name 2 Purpose 3. To use Excel, a common tool used for budgeting, to prepare components of the master budget. 4 To build a cash budget from its components 5 Instructions 6 Using the prior year ending balance sheet and budget information below create a three month budget in the worksheets provided. 7 Review the balance sheet and thoroughly read the budget information before you proceed. 8 The letters in the budget information and assumptions correspond to the required budgets, some budgets use the same information. In the budget data assumptions some items refer you to the balance sheet. Use cell referencing to replace "Refer to the balance sheet" with the amount. 10 Use cell referencing and formulas for all data input and calculations. 11 Required: 12 in the worksheet Budget and Cash components complete the following 13 1. Monthly sales budgets. 14 2. Monthly merchandise purchases budgets. 15 3. Monthly selling expense budgets. 16 4. Cash receipts budget 17 5. Cash payments for merchandise 18 Optional, but highly recommended: 19 in the worksheet Cash budget 20 6. Monthly cash budgets. 21 22 TARS Computer Corporation Estimated Balance Sheet 12/31/19 23 Assets Liabilities and Equity 24 Cash 35.000 Accounts payable $ 345.000 25 Accounts receivable 520.000 Bank loan payable 11.000 26 Inventory 95.000 Taxes payable (due 3/15/2020) 88.000 27 Total current assets 650.000 Total liabilities 28 Equipment 612.000 Common Stock 472.000 29 Less: accumulated depr 76.500 Retained earnings 269.500 30 Equipment, net 535,500 Total stockholders' equity 741.500 31 Total assets $ 1.185.500 Total liabilities and equity 1,185,500 32 Budget information a. The company's single product is purchased for $20 per unit and resold for 557 per unit. The expected inventory level of 4,750 units on December 31, 2019, is more than management's desired level, which is 20% of the next month's expected sales (in units). Expected sales are January, 6,500 units; February, 9,000 units; 33 March, 10,500 units; and April, 9.500 units. b. Cash sales and credit sales represent 25% and 75%, respectively, of total sales. Of the credit sales, 63% is collected in the first month after the month of sale and 37% in the second month after the month of sale. For the December 31, 2019, accounts receivable balance, 63% is collected in January 2020 37% in February 2020. 34 c. Merchandise purchases are paid for as follows: 20% in the first month after the month of purchase and 80% in the second month after the month of purchase. 35 For the December 31, 2019 the accounts payable balance 20% is paid in January and the remaining 80% is paid in February 36 d. Sales commissions equal to 20% of sales are paid each month. Sales salaries (excluding commissions) are 584.000 per year. 37 e. General and administrative salaries are $12.000 per month. Maintenance expense equals 52.000 per month and is paid in cash. f. he following amounts for new equipment purchases are planned in the coming quarter: January, 531.200; February, 598.400; and March, 521,600. g. The company plans to buy land at the end of March at a cost of $175,000, which will be paid with cash on the last day of the month. h. The company has a working arrangement with its bank to obtain additional loans as needed. The interest rate is 12% per year, and interest is paid at each month-end based on the beginning balance. Partial or full payments on these loans can be made on the last day of the month. The company has agreed to maintain a minimum ending cash balance of $50,000 at the end of each month. 1. The income tax rate for the company is 21%. Income taxes on the first quarter's income will not be paid until April 15. The taxes payable from the prior year is 41 88.000 and will be paid 3/15/2020 $ S 444,000 S 38 39 40 - o Q 5 March . B D E F G H K M N 1 Budget information a. The company's single product is purchased for $20 per unit and resold for $57 per unit. The expected inventory level of 4.750 units on December 31, 2019, is more than management's desired level, which is 20% of the next month's 2 expected sales lin units) Expected sales are lanuary 6500 units: February 9000 units: March 10500 units and April 2500 units b. Cash sales and credit sales represent 25% and 75%, respectively, of total sales. Of the credit sales, 63% is collected in the first month after the month of sale and 37% in the second month after the month of sale. For the December 31, 3 2019, accounts receivable balance, 63% is collected in January 2020 37% in February 2020. Merchandise purchases are paid for as follows: 20% in the first month after the month of purchase and 80% in the second month after the month of purchase. For the December 31, 2019 the accounts payable balance 20% is paid in 4 January and the remaining 80% is paid in February d. Sales commissions equal to 20% of sales are paid each month. Sales salaries (excluding commissions) are $84,000 per year. 6 7 TARS Computer Corporation Estimated Balance Sheet 12/31/19 8 Assets Liabilities and Equity 9 Cash $ 35.000 Accounts payable $ 345,000 10 Accounts receivable 520,000 Bank loan payable 11.000 11 Inventory 95.000 Taxes payable (due 3/15/2020) 88.000 12 Total current assets $ 650.000 Total liabilities $ 444,000 13 Equipment 612.000 Common Stock 472.000 14 Less: accumulated depr 76.500 Retained earnings 269,500 15 Equipment, net 535,500 Total stockholders' equity 741.500 16 Total assets $ 1.185.500 Total liabilities and equity $ 1.185.500 17 18 19 Budget Assumptions from above General January February March April Sales Budgets Cash Receipts from Customers 20 a. Selling price per unit $57.00 January February, and March 2020 January, February, and March 2020 21 Cost per unit $20.00 a. January February March b January February 22 Budget Sales Units 6500 9,000 10.500 9.500 Budgeted Units Total sales 23 Units in inventory 12/31/19 4,750 Budgeted Unit Price Cash sales 24 Ratio of inventory to future sales 20% Budaeted Total Dollars Credit sales 25 b. Cash sales 25% Cash collections 26 Credit sales 75% Merchandise Purchases Budgets Receivables at 12/31/2019 27 Credit sales collections January, February, and March 2020 January credit sales 28 Collections in first month after 63% a January February March February credit sales 29 Collections in second month after 37% Next month's budgeted sales units Total cash from credit customers 30 Accounts Receivable 12/31/19 Refer to balance sheet Ratio of inventory to future sales Cash Sales 31 C Merchandise purchase payments Budgeted ending inventory units Total cash received 32 In first month after the purchase 30% Add budgeted sales units 33 In second month after the purchase 70% Required available merchandise units Cash Payments for Merchandise 34 Accounts Payable 12/31/19 Refer to balance sheet Deduct beginning inventory units January February, and March 2020 35 d. Sales commissions 20% Units to be purchased C. January February 36 Monthly sales salary $7,000 Budgeted cost per unit Credit purchases 37 Budaeted merchandise purchases Accts payable at 12/31/19 38 January purchases 39 February purchases 40 Selling Expense Budgets Total paid on purchases 41 January February, and March 2020 42 d. January February March 43 Budgeted sales 44 Sales commission percent 45 Sales commissions expense 46 Sales salaries 47 ITotal budaeted selling expenses March E B C D G H K 1 TARS Computer Corporation Estimated Balance Sheet 12/31/19 2 Assets Liabilities and Equity 3 Cash 35000 Accounts payable 345000 4 Accounts receivable 520000 Bank loan payable 11000 5 Inventory 95000 Taxes payable (due 3/15 88000 6 Total current assets =SUM(D3:D5) Total liabilities =SUM(13:15) 7 Equipment 612000 Common Stock 472000 8 Less: accumulated depr 76500 Retained earnings 269500 9 Equipment, net =+D7-08 Total stockholders' eq =+18+)7 10 Total assets =+E6+E9 Total liabilities and equ =+K6+K9 11 Budget information 12 e. General and administrative salaries are $12,000 per month. Maintenance expense equals $2,000 per month and is paid in cash. f. he following amounts for new equipment purchases are planned in the coming quarter: January, $31,200; February, $98,400; and March, $21,600. 13 14 9. The company plans to buy land at the end of March at a cost of $175,000, which will be paid with cash on the last day of the month. h. The company has a working arrangement with its bank to obtain additional loans as needed. The interest rate is 12% per year, and interest is paid at each month-end based on the beginning balance. Partial or full payments on these loans can be made on the last day of the month. The company has agreed to maintain a minimum ending cash balance of $50,000 at the end of each month. 15 i. The income tax rate for the company is 21%. Income taxes on the first quarter's income will not be paid until April 15. The taxes payable from the prior year is88,000 and will be paid 3/15/2020 General January February March 12000 2000 TARS Computer Corporation Cash Budgets January, February, and March 2020 January February 31200 98400 March 21600 175000 Refer to balance shee 50000 0.01 16 17 18 Budget Assumptions from above 19 General and Admin 20 Salaries per month 21 Maintenance expense per month 22 Equipment purchases 23 Land purchase 24 Loan balance 12/31/19 25 Required ending cash balance 26 Interest rate per month 27 Income taxes payable 28 29 30 31 32 33 34 35 36 37 38 88000 previous plus e. to i. Beginning cash balance Cash receipts from customers Total cash available Cash payments for: Merchandise Selling expenses General & admin salaries Maintenance expense Interest expense Taxes payable Purchases of equipment Purchase of land Total cash payments Preliminary cash balance Repayment of loan to bank Additional loan from bank Cadina cacbblanca 20 Instructions Budget and Cash components Cash budget Operating Cash example Cash budget example + N 1 16 . E H M Budget information a. The company's single product is purchased for $30 per unit and resold for 545 per unit. The expected inventory level of 5,000 units on December 31, 2019, is more than management's desired level, 2 which is 25% of the next month's expected sales (in units). Expected sales are: January 6,000 units; February. 8.000 units; March 10,000 units; and April, 9.000 units. b. Cash sales and credit sales represent 25% and 75%, respectively, of total sales. Of the credit sales, 60% is collected in the first month after the month of sale and 40% in the second month after the 3 month of sale. For the December 31, 2019, accounts receivable balance is $525.000. c. Merchandise purchases are paid for as follows: 20% in the first month after the month of purchase and 80% in the second month after the month of purchase. For the December 31, 2019 the accounts 4 payable balance 20% is paid in January and the remaining 80% is paid in February 5 d. Sales commissions equal to 20% of sales are paid each month. Sales salaries (excluding commissions) are 57.500 month. 6 7 Isle Corporation Estimated Balance Sheet 12/31/19 8 Assets Liabilities and Equity 9 Cash $ 36,000 Accounts payable S 360.000 10 Accounts receivable 525.000 Bank loan payable 15.000 11 Inventory 150,000 Taxes payable (due 3/15/2020) 90.000 12 Total current assets S 711,000 Total liabilities S 465,000 13 Equipment 540.000 Common Stock 472.500 14 Less: accumulated depr 67,500 Retained earnings 246.000 15 Equipment, net 472,500 Total stockholders' equity 718.500 Total assets $ 1.183.500 Total abilities and equity $ 1.183.500 17 18 19 Budget Assumptions from above General January February March April Sales Budgets 20 a. Selling price per unit $45.00 January, February, and March 2020 21 Cost per unit $30.00 a. January February March 22 Budget Sales Units 6000 8.000 10.000 9.000 Budgeted Units 6.000 8.000 10.000 23 Units in inventory 12/31/19 5.000 Budgeted Unit Price 45 45 45 24 Ratio of inventory to future sales 25% Budgeted Total Dollars $270.000 $360,000 $450,000 25 b. Cash sales 25% 26 Credit sales 75% Merchandise Purchases Budgets 27 Credit sales collections January, February, and March 2020 28 Collections in first month after 60% January Februar March 29 Collections in second month after 40% Next month's budgeted sales units 8,000 10.000 9.000 30 Accounts Receivable 12/31/19 525,000 Ratio of inventory to future sales 25% 251 258 Merchandise purchase payments Budgeted ending inventory units 2.000 2.500 2.250 32 In first month after the purchase 20% Add budgeted sales units 6,000 8,000 33 In second month after the purchase 80% Required available merchandise units 8.000 10.500 12.250 34 Accounts Payable 12/31/19 5 360.000 Deduct beginning inventory units -5.000 -2.000 - 2.500 35 d. 25 d. Sales commissions 20% Units to be purchased 3.000 8.500 9.750 36 Monthly sales salary $7.500 Budgeted cost per unit $30 $30 $30 37 22 Budgeted merchandise purchases $90.000 $255.000 $292.500 38 39 40 Selling Expense Budgets 41 January February, and March 2020 20 42 d. January February March 43 Budgeted sales $270.000 $360.000 $450.000 44 Sales commission percent 20% 20% 20% 45 Sales commissions expense 54.000 72.000 90.000 46 Sales salaries 7.500 7.500 7,5001 47 Total budgeted selling expenses $61.500 $79.500 $97.500 Cash Receipts from Customers January, February, and March 2020 b. January February March Total sales $270.000 $360,000 $450.000 Cash sales 67,500 90.000 112.500 Credit sales 202.500 270.000 337.500 Cash collections Receivables at 12/31/2019 315.000 210.000 January credit sales 121.500 81,000 February credit sales 162.000 Total cash from credit customers 315.000 331.500 243,000 Cash Sales 67,500 90.0001 112.500 Total cash received 382.500 421,500 355.500 a. 31 C 10,000 March $292.500 Cash Payments for Merchandise January February, and March 2020 C. January February Credit purchases $90,000 $255.000 Accts payable at 12/31/19 72.000 288.000 January purchases 18000 February purchases Total paid on purchases 72.000 306.000 72.000 51.000) 123.000 E F L 1 D G K M Isle Corporation Estimated Balance Sheet 12/31/19 2 Liabilities and Equity 3 Accounts payable $360,000 4 Bank loan payable 15,000 5 Taxes payable (due 3/15/2020) 90,000 6 $ 711,000 Total liabilities $ 465,000 7 Common Stock 472,500 8 Retained earnings 246,000 9 472,500 Total stockholders' equity 718,500 10 $ 1.183,500 Total abilities and equity $ 1.183,500 11 12 bo per month. Maintenance expense equals $3,000 per month and is paid in cash. 13 rchases are planned in the coming quarter: January, $72,000; February, $96,000; and March, $28,800. 14 f March at a cost of $150,000, which will be paid with cash on the last day of the month. th its bank to obtain additional loans as needed. The amount due on 12/31/19 is $15,000. The interest rate is 12% per year, and interest is paid at each artial or full payments on these loans can be made on the last day of the month. The company has agreed to maintain a minimum ending cash balance of 15 16 ncome taxes on the first quarter's income will not be paid until April 15. The taxes payable from the prior year is 90,000 and will be paid 3/15/2020 17 18 January February March 19 ISLE CORPORATION 20 Cash Budgets 21 January, February, and March 2020 22 72,000 96,000 28,800 previous plus e. to i. January March 23 150,000 Beginning cash balance $ 36,000 $182,850 $107,850 24 Cash receipts from customers 382500 421.500 355 500 25 Total cash available 418,500 604,350 463,350 26 Cash payments for: 27 90,000 Merchandise 72,000 306,000 123,000 28 Selling expenses 61,500 79,500 97,500 29 General & admin salaries 12,000 12.000 12,000 30 Maintenance expense 3,000 3,000 3,000 31 Interest expense 150 $0 32 Taxes payable 0 0 90000 33 Purchases of equipment 72.000 96,000 28,800 34 Purchase of land 0 0 150,000 35 Total cash payments 220 650 496 500 504300 36 Preliminary cash balance $197,850 $107,850 ($40,950) 37 Repayment of loan to bank - 15,000 38 Additional loan from bank 76.950 39 Ending cash balance $182 850 $107 850 $36.000 40 Loan balance, end of month $0 $0 $76 950 February
