O M N 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. 9 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. 153. Monthly selling expense budgets. 16 4. Cash receipts budget 175. Cash payments for merchandise 18 6. Monthly cash budgets. 19 20 TARS Computer Corporation Estimated Balance Sheet 12/31/19 21 Assets Liabilities and Equity 22 Cash $ 35,000 Accounts payable $345.000 23 Accounts receivable 520,000 Bank loan payable 11,000 24 Inventory 95,000 Taxes payable (due 3/15/2020) 88,000 25 Total current assets $ 650,000 Total liabilities $ 444,000 26 Fruinment 612 non Common Sinek 47) Instructions Budget and Cash components Cash budget Budget components example Cash budget example Select destination and press ENTER or choose Paste CAM Excel Product View ormulas Data Review Wrap Text AA a a General Autosum Merge & Center - $ - % 9 Insert Delete Format Alignment : Conditional Format Cell Formatting as Table Styles Styles Clear - Number Sort & Find Filter Select Editing Cells H K M N O P R U V master budget. Check figures in Budget 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 three month budget in the worksheets provided. proceed. budgets, some budgets use the same information. -Il referencing to replace "Refer to the balance sheet" with the amount 214315 189,258 uity $345,000 11,000 88,000 $ 444,000 472 Cash budget Budget components example Activate Windows Cash budget example 20 TARS Computer Corporation Estimated Balance Sheet 12/31/19 21 Assets Liabilities and Equity 22 Cash $ 35,000 Accounts payable $ 345,000 23 Accounts receivable 520,000 Bank loan payable 11,000 24 Inventory 95,000 Taxes payable (due 3/15/2020) 88,000 25 Total current assets $ 650,000 Total liabilities $ 444,000 26 Equipment 612,000 Common Stock 472,000 27 Less: accumulated deg 76,500 Retained earnings 269,500 28 Equipment, net 535,500 Total stockholders' equity 741,500 29 Total assets $1,185,500 Total liabilities and equity $ 1,185,500 30 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 expected sales (in units). Expected sales 31 are January, 6,500 units; February, 9,000 units; 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 32 collected in January 2020 37% in February 2020. 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 33 month of purchase. For the December 31, 2019 the accounts payable balance 20% is paid in January and the remaining 80% is paid in d. Sales commissions equal to 20% of sales are paid each month. Sales salaries (excluding commissions) are $84,000 per year. 34 35 le. 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 36 21 ann H4H Instructions Budget and cash components Cash budges Budget comoonents example Cash budget example Select destination and press ENTER or choose Paste Calibri Wrap Text General A A 3. A E BIU- ir Merge & Center $ - % 9 X Cut Copy Paste Format Painter Clipboard V18 449 Conditional Format Cell Formatting as Table Styles - Styles Font Alignment Number B D N E F G H M 30 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 expected sales (in units). Expected sales 31 fare January, 6,500 units; February, 9,000 units; 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 32 collected in January 2020 37% in February 2020. 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 payable balance 20% is paid in January and the remaining 80% is paid in 33 d. Sales commissions equal to 20% of sales are paid each month. Sales salaries (excluding commissions) are $84,000 per year. 34 35 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, 36 $21,600 37 8. 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. th. 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 38 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 39 from the prior year is 88,000 and will be paid 3/15/2020 1 a P a 2 N Budget informe The company's single product is purchased for so per unit and resold for 557 per unit. The expected intory level of 4,70 units on December 31, 2019, more than management's desired level which is 20% next month's excited sales inted sales an.500 3.000 units March 10.500 units ud.Atil. 9.30 6. Cash sales and credit sales represent 25 and 75, respectively for sales of sales, is collected in the fustment after the month of sale and 37 in the second month wher med sale For the December 1, 2017, Mountable balance, sceded in 2020 2022 Merchandise purchases are said for as follows 20% in the first month after the month of purchase and in the second month after the month of purchase. For the December 31, 2019 the account balances and the remaining sisaldina la Sales comissions to 203 of sales are paid eachment Sales salaries Commissare TAS Computer Corporation Estimated Balance 51 Les and Cavite Cash $35.000 Accounts payable $45.000 Accounts receivable 520000 Bank payable 11,000 | inventory 95.000 Tepatu 15/2020) 88.000 Total current $ 650.000 Yotal 5464000 Equipment 512.000 Commons 472.000 Less acumulared de 75.500 Red 20 od Equipment net SI5.500 Total holders 71.500 Total assets 3118500 Total abilities and equity 10 11 13 15 16 March Castecios rom Customers January February and March 2000 January February March 41 Totatsies Gredit sales 11 Budget Asumintrove General February March April Sares budgets a. SIE DIE per 35700 January February and March 2000 Cost per unit $20.00 January February Budgets Uniti 9.000 9.500 9.500 Budged Units Uits inventory 12/31/13 de Unter 204 25 Cash sales Credit sales 75 Merchandise Purchases budgets Credit les colo January February and March 2020 Collections in first month after 65 TOUS Collections in second month 17 Nestor's budged Accounts Receivable 13/3/19 Refer to balance sheet Ratio of very Instructions Budget and Cash components ger Bugetoon TO Select destination and ER chieste March Recev 13/31/2019 anar cred sales Februarycredit sales Totalcash from dit te Cash Sales Buget Assumptions from where General February March April Selling price per unit 55700 Cort per una Budet Sales Units 5.000 16.09.500 Units inity 12/31/19 Rate oferte 20 25 Casals Credits 3 second Collections in this month after SUN Collections in second month att 37 Accounts Receivable 1 Berbalance theet Sales budgets January February, and March 2000 January February Bu Pusted in Batters Cash Recetrom Customers January February, and March 2010 Lanuary February Tots els Casas Credit Sales Co Receivables at 2019 January credit sales Terent sales Total shocredit cum March Merchandise Purchases dues January February, and March 2020 Jan February Neemed sales Ratio of try News Budgeted ending Inventory units Add budgeted sales units le nechat Deductingin units to be used Based cost The ved In first month after the phone 30 In second month wher the purch 70 Accounts Payable 1/1/19 Refer to balance sheet Sales comissions 205 Monthless sy Cashymes for Merchandise January February, and March 2000 January February March Acts pavable at aus purchases February purchases Totalcalde Sense Budget wy, February and Math Math les con percent Salontense Instructor Budget and Cash components Cash ught me A B D E F H K L M N 1 TARS Computer Corporation Estimated Balance Sheet 12/31/19 Assets Liabilities and Equity cash $ 35,000 Accounts payable $345.000 4 Accounts receivable 520,000 Bank loan payable 11,000 5 inventory 95,000 Taxes payable due 3/15/2020) 88,000 6 Total current assets $ 650.000 Total abilities S 441,000 7 Equipment 612,000 Common Stock 472.000 8 Less: accumulated depr 76,500 Retained Gamings 269,500 9 Equipment, net 535,500 Total stockholders equity 741,500 10 Total assets $ 1.185,500 Total liabilities and equity $1,185,500 11 Budget information 12 . General and administrative salaries we $12.000 per month. Maintenance expense equals $2.000 per month and is paid in each 13 he following amounts for new equipment purchases are planned in the coming Quarter January $31.200: February $98.400; and March $21.600. 14 8. The company plans to buy and at the end of March at a cost of $175,000, which will be paid with cash on the last day of the month . 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 $30,000 at the end of each month 15 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 8.000 and will be paid /192020 16 17 15 Budget Assumptions from above General January February March 19 e. General and Admin TARS Computer Corporation 220 Salaries per month $12.000 Cash Budgets 21 Maintenance expense per month January February, and March 2020 221 Equipment purchases 11,200 5,00 21,000 previous toi January February March 23 and purchase 175.000 ning cash balance 24 h. Loan balance 12/11/19 Refer to balance sheet Cash receipts from customers Banner HH doc.com Cashbudget Ch dhe February March 98,400 18 Budget Assumptions from above General January 19 e. General and Admin 20 Salaries per month $12,000 21 Maintenance expense per month 2,000 22 1. Equipment purchases 31,200 23 8. Land purchase 24 h. Loan balance 12/31/19 Refer to balance sheet 25 Required ending cash balance 50,000 26 Interest rate per month 1.0% 27 1. Income taxes payable 28 21,600 175,000 March 88,000 29 30 31 32 33 34 35 36 37 38 39 TARS Computer Corporation Cash Budgets January, February, and March 2020 previous plus e. toi. January February 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 Ending cash balance Loan balance, end of month 40 Instructions Budoet and cash components Cash budget Budget components example Cash budoet example 1 2 F M Budget information a. The company's single product is purchased for $10 per unit and resold for Sus per unit. The expected inventory level of 5,000 units on December 11, 2015, is more than mantement's desired which is 25% of the next month's excected sales in units. Expected sales an.000 units Ebruary. 1.000 March 10.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 in the second month after the month of sale. For the December 11, 2019, accounts receivable balance is $525.000 c. Merchandise purchases are paid for as follows: 20% in the first months after the month of purchase and in the second month after the month of purchase. For the December 31, 2019 the accounts payable balance 20% paid in January and the remaining is paid in february d. Sales commissions equal to 20% of sales are paid each month Sales salaries excluding commission are 57.500 month, 3 5 6 Isle Corporation Estimated Balance Sheet 12/31/19 $36.000 525,000 150.000 $ 711,000 7 Assets Cash 10 Accounts receivable 11 inventory 12 Total current assets 13 Equipment Lesse accumulated depe 15 Equipment, net 10 Total assets 17 18 19 Budget Assumptions from above General 20 a. Selling price per unit $45.00 21 Cost per unit $10.00 22 Budget Sales units 23 units in inventory 12/31/19 Accounts payable Bank loan payable Taxes payable de 15:2020) Totalt Common Stock Retained ang Total stockholders' equity Total abilities and equity 540,000 67.500 $160,000 15.000 90,000 $ 465.000 472.500 20.000 718,500 $1,181,500 472,500 $112.500 January February March April Cash Receipts Sales Budgets nuary February, and March 2010 January February 000 1.000 45 10 Total sales Cashsilor 5.000 6000 3.000 10.000 5.000 Budgeted Catho nudents campe January February March April General $45.00 $30.00 3.000 10.000 9,000 Sales budgets January, February, and March 2020 January February March Budgeted Units 6.000 8.000 Budited Unit Price 45 45 Butted Total Dollars 2221 $360,000 SASO 220 10.000 5,000 25% 17 18 19 Budget Assumptions from above 20 a. Selling price per unit 21 Cost per unit Budget Sales Units 23 Units in inventory 12/11/19 24 Ratio of inventory to future sales 25 b. Cash sales 26 Credit sales 27 Credit sales collections 28 Collections in first month after 29 Collections in second month after 30 Accounts Receivable 12/31/19 31 Merchandise purchase payments in first month after the purchase 33 In second month after the purchase Accounts Payable 12/31/19 35 d. Sales commissions 16 Monthly sales salary 37 38 39 Cash Receipts January Februar b. Total sales Cash sales Credit sales Cash collections Receivables at 17/11/2019 January credit sales February credit sales Total cash from credit customers 75% ON GN 525,000 March 9.000 2.250 Totalcarere Merchandise Purchases Budgets January February, and March 2020 January February Next month's budgeted sales units 3.000 10.000 Ratio of inventory to future sales 25 25% Budgeted ending inventory units 2,000 2.500 Add budgeted sales units Required avtale merchandise 000 10.500 Deduct beginning inventory units 500 20 units to be purchased 1.000 1.500 Budgeted cost per Buduated merchandise purchases SER 20N BON $160.000 201 $2.500 9.790 Credit purchases Acts payable at 12/31/19 January purchases February purchases Selling Expense Budgets Feed Mart 20 $1,183,500 March 2020 uary February March 6,000 8,000 10,000 45 45 45 0,000 $360,000 $450,000 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,000 112,500 Total cash received. 382,500 421,500 355,500 Budgets March 2020 uary February March 8,000 10,000 9,000 25% 25% 25% 2,000 2,500 2,250 6,000 8,000 10,000 3,000 10,500 12,250 5,000 -2,000 -2,500 3,000 8,500 9,750 $30 $30 5.000 $255.000 $292.500 $30 Cash Payments for Merchandise January, February, and March 2020 C. January February March Credit purchases $90,000 $255,000 $292,500 Accts payable at 12/31/19 72,000 288,000 January purchases 18000 72,000 February purchases 51,000 Total paid on purchases 72,000 306,000 123,000 gets arch 2020 Cash budget example 1009 G H K M a. Cash collect Receivable January cre February Total cash Cash Sales Total cash re Merchandise Purchases Budgets January, February, and March 2020 January February March Next month's budgeted sales units 8,000 10,000 9,000 Ratio of inventory to future sales 25% 25% 25% Budgeted ending inventory units 2,000 2,500 2,250 Add budgeted sales units 6,000 8,000 10,000 Required available merchandise 8,000 10,500 12,250 Deduct beginning inventory units -5,000 -2,000 -2,500 Units to be purchased 3,000 8,500 9,750 Budgeted cost per unit $30 $30 $30 Budgeted merchandise purchases $90,000 $255,000 $292,500 C. Credit purg Accts paya January pu Februaryp Total paid Selling Expense Budgets January, February, and March 2020 d. January February March Budgeted sales $270,000 $360,000 $450,000 Sales commission percent 20% 20% 20% Sales commissions expense 54,000 72,000 90,000 Sales salaries 7,500 7,500 7,500 Total budgeted selling expenses $61,500 $79,500 $97,500 ponents Cash budget Budget components example Cash budget example 1 K L H19 f ISLE CORPORATION AA B c D E H M 1 Isle Corporation Estimated Balance Sheet 12/31/19 2 Assets Liabilities and Equity 3 cash $36,000 Accounts payable $ 350,000 Accounts receivable 525,000 Bank loan payable 15,000 5 Inventory 150,000 Taxes payable (due 9/15/2020) 90,000 6 Total current assets $ 711,000 Total liabilities $ 465,000 Equipment 540,000 Common Stock 472,500 Less: accumulated depr 67,500 Retained eamings 246,000 9 Equipment, net 472,500 Total stockholders' equity 718,500 10 Total assets Total abilities and equity $1,183,500 11 12 e. General and administrative salaries are $12,000 per month. Maintenance expense equals 1,000 per month and is paid in cash 13 The following amounts for new equipment purchases are planned in the coming quarter: January $72.000 February 596,000 and March 20,000 14 s. The company plans to buy land at the end of March at a cost of $150,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 amount due on 12/31/19 is $15,000. 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 15 ending cash balance of $35,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 90,000 and will be paid 10 January February March 18 Budget Assumptions from above General ISLE CORPORATION 19 e. General and Admin 20 Salaries per month $12,000 Cash Budgets 21 Maintanece expense per month January, February, and March 2020 3,000 96,000 72.000 February January 28,800 previous plus e. to. 22 1. Equipment purchases March 150,000 $ 35,000 Beginning cash balance $102,450 107 te Windows 23 8. Land purchase Cacharemman 15.000 24 h nan balance 19781/19 Budget and cash components Instructions Cash budget Budget commaple Cashbudget example ENTER of the Paste 431 Alignment Number Styles Celk Loting G H L M E February F March 96,000 March 28,800 150,000 Clipboard Font 119 ISLE CORPORATION HA B c D 18 Budget Assumptions from above General January 19 e. General and Admin 20 Salaries per month $12,000 21 Maintanece expense per month 3,000 22 1. Equipment purchases 72,000 23 8. Land purchase 24 h. Loan balance 12/31/19 15,000 25 Required ending cash balance 36,000 26 Interest rate per month 10% 27 1. Income taxes payable 28 29 30 31 32 33 34 35 36 37 38 39 90,000 ISLE CORPORATION Cash Budgets January, February, and March 2020 previous plus e. toi January February Beginning cash balance $36,000 $182,850 $107,850 Cash receipts from customers 382,500 421,500 355.500 Total cash available 418,500 604,350 463,350 Cash payments for: Merchandise 72,000 306,000 123,000 Selling expenses 61,500 79,500 97,500 General & admin salaries 12,000 12.000 12,000 Maintenance expense 3,000 3,000 Interest expense 150 $0 Taxes payable 0 0 90000 Purchases of equipment 72.000 96,000 28,8001 Purchase of land 0 0 150,000 Total cash payments 220,650 495,500 504300 Preliminary cash balance $197.50 $107,850 1500,950 Repayment of loan to bank - 15,000 Additional loan from bank 76.950 Ending cash balance $12.850 $10780 $16.000 Loan balance, end of month $0 $0 $26.950 40 Cash budget Budget components example Cash budget example 94 Instructions Budget and Cach components Select destination and press ENTER or choose Paste BI EE Type here to search