WORKING CAPITAL MANAGEMENT You have recently been hired to work in your company's newly established treasury department. The company is a small company that produces cardboard boxes in a variety of sizes for different purchases. The owner of the company, works primarily in the sales and production areas of the company. Currently, the company puts all receivables in one shoe box and all payables in another. Because of the disorganized system, the finance area needs work - and that's what you have been brought in to do. The company currently has a cash balance of $305,000 and it plans to purchase new box-folding machinery in the fourth quarter at a cost of $525,000. The machinery will be purchased with cash because of a discount offered. The company's policy is to maintain a minimum cash balance of #125,000. All sales and purchases are made on credit. The owner has projected the following gross sales for each of the next four quarters. Q1 Q2 Q3 04 Gross sales $1.310.000 $1.390.000 $1,440,000 $1.530,000 Also, gross sales for the first quarter of the next year are projected at $1,405,000. The company currently has an accounts receivable period of 53 days and an accounts receivable balance of $645,000. 20% of the accounts receivable balance is from a company that has just entered bankruptcy, and it is likely this portion of the accounts receivable will never be collected. The company typically orders 50% of the next quarter's projected gross sales in the current quarter, and suppliers are typically paid in 42 days. Wages, taxes and other costs run about 30% of gross sales. The company has a quarterly interest payment of $135,000 on its long tern debt. The company uses a local bank for its short-term financial needs. It pays 1.5% per quarter on all short-term borrowing and maintains a money market accounts that pays 1% per quarter on all short-term deposits. You have been asked to prepare a cash budget and short-term financial plan for the company under the current policies. You have also been asked to prepare additional/alternative plans based on changes in several inputs. Respond to the following questions. Written responses must comprise at least three complete sentences, with proper grammar and punctuation. Cite any referenced materials using APA format. In the Excel spreadsheets provided, all calculations that support your answers must be shown as formulae. 1. Use the numbers given to complete the cash budget and short-tern financial plan in Excel. (Sheet 1) Page Layout fondas Das Review View Developer How bert Out Copy format UNP Cash Budget end Tell me what you want to do FOR BTWO Meryem Wrap Text Mere Come $ % 1 contional Formats Cell Condition a 47 Insert Delete Format Sort Find 281629 " Purchmes Prenata Cab First Months Second Mouth Total Payweats " " 181 564 20% 181 544 180 544 191 544 192 574 191 574 178 574 178 917 90 65 0 91 46 913 907 38 44 188B 325 325 956 90 67 91 94 90 66 400 192 193 193 183 184 185 Les Duburan lavory Payments SORA Interest Shorten Interest Eqene (ne) Divided(Common) TANG 5 Capital Outlys Total Dibunus Being Cash Bale Collections - Distance Unadjusted Cash Balance 1 Care Barrow 2 Ending Cat Haluare 3C t ive Borow Clavesting) mative Interest Expense (n.) 15 No 36 Min. Acceptable Cash 3/ Men Acceptable Cash 38 Borrowing Rate (A ) 9 Lending Rate (mm ) 1:277 1.670 1536 1.833 1 911 1.37 1.701 1930 190 2000 1.72 1 1.670 0 1.670 0 1.883 0 1,336 0 1496 0 1.682 1.951 0 1.981 0 1.374 0 1,574 0 .701 0 1.910 0 1.910 0 1.916 0 1,916 1.4 2.172 (172) ?.000 (172) 1.782 0 1.782 (172) 2.103 (105) .000 70 0 0 0 1.000 2.000 3.5046 Monthly 2.50% Meely 0.19 0.21% Clipboard Font Styles Style 1S&P Enterprises needs a cash budget for March. The following information is available. January February March 3 Data 4 Actual January and February and expected March sales: 5. Cash sales 6 Sales on account 7 Total Sales $ 1,600 $ 3,750 S 5,100 25,000 30,000 40.000 $26.600 S 33.750 S 45.100 9 Accounts Receivable Collections: Month of sale Month following sale Second month following sale Uncollectible 15% 60% 22% $10,500 $23,500 15 Accounts payable for inventory purchases, March 1 balance 16 Budgeted inventory purchases in March 17 Inventory payments: Month of purchase 19 Month following purchase 60% 4036 21 Total budgeted selling & administrative expenses in March 22 Budgeted selling & administrative depreciation in March $12.500 $3,200 24 Other budgeted cash disbursements in March 25 Equipment purchases 26 Dividends to be paid $14,000 $2,000 28 Minimum cash balance to be maintained 29 March 1 cash balance 30 March 1 outstanding borrowings 31 March 1 Interest due $10,000 $11,500 $0 Budget Data Schedules and Cash Budget ... READY WORKING CAPITAL MANAGEMENT You have recently been hired to work in your company's newly established treasury department. The company is a small company that produces cardboard boxes in a variety of sizes for different purchases. The owner of the company, works primarily in the sales and production areas of the company. Currently, the company puts all receivables in one shoe box and all payables in another. Because of the disorganized system, the finance area needs work - and that's what you have been brought in to do. The company currently has a cash balance of $305,000 and it plans to purchase new box-folding machinery in the fourth quarter at a cost of $525,000. The machinery will be purchased with cash because of a discount offered. The company's policy is to maintain a minimum cash balance of #125,000. All sales and purchases are made on credit. The owner has projected the following gross sales for each of the next four quarters. Q1 Q2 Q3 04 Gross sales $1.310.000 $1.390.000 $1,440,000 $1.530,000 Also, gross sales for the first quarter of the next year are projected at $1,405,000. The company currently has an accounts receivable period of 53 days and an accounts receivable balance of $645,000. 20% of the accounts receivable balance is from a company that has just entered bankruptcy, and it is likely this portion of the accounts receivable will never be collected. The company typically orders 50% of the next quarter's projected gross sales in the current quarter, and suppliers are typically paid in 42 days. Wages, taxes and other costs run about 30% of gross sales. The company has a quarterly interest payment of $135,000 on its long tern debt. The company uses a local bank for its short-term financial needs. It pays 1.5% per quarter on all short-term borrowing and maintains a money market accounts that pays 1% per quarter on all short-term deposits. You have been asked to prepare a cash budget and short-term financial plan for the company under the current policies. You have also been asked to prepare additional/alternative plans based on changes in several inputs. Respond to the following questions. Written responses must comprise at least three complete sentences, with proper grammar and punctuation. Cite any referenced materials using APA format. In the Excel spreadsheets provided, all calculations that support your answers must be shown as formulae. 1. Use the numbers given to complete the cash budget and short-tern financial plan in Excel. (Sheet 1) Page Layout fondas Das Review View Developer How bert Out Copy format UNP Cash Budget end Tell me what you want to do FOR BTWO Meryem Wrap Text Mere Come $ % 1 contional Formats Cell Condition a 47 Insert Delete Format Sort Find 281629 " Purchmes Prenata Cab First Months Second Mouth Total Payweats " " 181 564 20% 181 544 180 544 191 544 192 574 191 574 178 574 178 917 90 65 0 91 46 913 907 38 44 188B 325 325 956 90 67 91 94 90 66 400 192 193 193 183 184 185 Les Duburan lavory Payments SORA Interest Shorten Interest Eqene (ne) Divided(Common) TANG 5 Capital Outlys Total Dibunus Being Cash Bale Collections - Distance Unadjusted Cash Balance 1 Care Barrow 2 Ending Cat Haluare 3C t ive Borow Clavesting) mative Interest Expense (n.) 15 No 36 Min. Acceptable Cash 3/ Men Acceptable Cash 38 Borrowing Rate (A ) 9 Lending Rate (mm ) 1:277 1.670 1536 1.833 1 911 1.37 1.701 1930 190 2000 1.72 1 1.670 0 1.670 0 1.883 0 1,336 0 1496 0 1.682 1.951 0 1.981 0 1.374 0 1,574 0 .701 0 1.910 0 1.910 0 1.916 0 1,916 1.4 2.172 (172) ?.000 (172) 1.782 0 1.782 (172) 2.103 (105) .000 70 0 0 0 1.000 2.000 3.5046 Monthly 2.50% Meely 0.19 0.21% Clipboard Font Styles Style 1S&P Enterprises needs a cash budget for March. The following information is available. January February March 3 Data 4 Actual January and February and expected March sales: 5. Cash sales 6 Sales on account 7 Total Sales $ 1,600 $ 3,750 S 5,100 25,000 30,000 40.000 $26.600 S 33.750 S 45.100 9 Accounts Receivable Collections: Month of sale Month following sale Second month following sale Uncollectible 15% 60% 22% $10,500 $23,500 15 Accounts payable for inventory purchases, March 1 balance 16 Budgeted inventory purchases in March 17 Inventory payments: Month of purchase 19 Month following purchase 60% 4036 21 Total budgeted selling & administrative expenses in March 22 Budgeted selling & administrative depreciation in March $12.500 $3,200 24 Other budgeted cash disbursements in March 25 Equipment purchases 26 Dividends to be paid $14,000 $2,000 28 Minimum cash balance to be maintained 29 March 1 cash balance 30 March 1 outstanding borrowings 31 March 1 Interest due $10,000 $11,500 $0 Budget Data Schedules and Cash Budget ... READY