**PLEASE SOLVE USING EXCEL** **PLEASE SHOW SOLUTIONS IN EXCEL AS WELL** The Lynb

Question

**PLEASE SOLVE USING EXCEL**

**PLEASE SHOW SOLUTIONS IN EXCEL AS WELL**

The Lynbrook Company is a wholesale distributor of premium fabric. The owners have asked you to assist them in planning for their cash needs for April, May, and June. Normally, the company has to borrow money during this period to accommodate major purchases of inventory. You have compiled the following information to help you in your evaluation of Lynbrook’s cash needs.

Budgeted monthly traditional income statements for April–July are as follows:

* includes $20,000 of depreciation each month

Additional information:

a) Sales are 20% for cash and 80% on account.

b) Sales on account are collected over a three-month period with 10% collected in the month of sale; 70% collected in the first month following the month of sale; and the remaining 20% collected in the second month following the month of sale. February’s sales totaled $200,000, and March’s sales totaled $300,000.

c) Inventory purchases are paid for within 15 days. Therefore, 50% of a month’s inventory purchases are paid for in the month of purchase. The remaining 50% is paid in the following month. Accounts payable at March 31 for inventory purchases during March total $126,000.

d) Each month’s ending inventory must equal 20% of the cost of the merchandise to be sold in the following month. The merchandise inventory at March 31 is $84,000.

e) Dividends of $49,000 will be declared and paid in April.

f) Land costing $16,000 will be purchased for cash in May.

g) The cash balance at March 31 is $52,000; the company must maintain a cash balance of at least $40,000 at the end of each month.

h) The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $200,000. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter.

Required:

Prepare a schedule of expected cash collections for April, May, and June.

Prepare the following for merchandise inventory:

a) A merchandise purchases budget for April, May, and June.

b) A schedule of expected cash disbursements for merchandise purchases for April, May, and June.

Prepare a cash budget for April, May, and June.
(hint: use Comprehensive Problem 1 on page 888-891 as an example to follow for this problem)

April May June July Sales 600,000 900,000 500,000 400,000 Cost of Goods Sold 420,000 630,000 350,000 280,000 Gross Margin 180,000 270,000 150,000 120,000 Selling and Administrative Expenses Selling Expense 79,000 120,000 62,000 51,000 Administrative Expenses 45,000 52,000 41,000 38,000 Total 124,000 172,000 103,000 89,000 Net Operating Income 56,000 98,000 47,000 31,000

Explanation / Answer

SALES BUDGET: February March April May June Quarter July Sales in $ 600000 900000 500000 2000000 400000 Cash sales (20%) 120000 180000 100000 400000 80000 Credit sales (80%) 200000 300000 480000 720000 400000 1600000 320000 (Sales given for february and march are taken as credit sales for those months) COLLECTIONS FROM RECEIVABLES: From current month's sales (10%) 48000 72000 40000 160000 From sales of last month (70%) 210000 336000 504000 1050000 From sales two months before (20%) 20000 30000 48000 98000 Total monthly collections from receivables 278000 438000 592000 1308000 PURCHASE BUDGET: Cost of goods sold 420000 630000 350000 1400000 280000 Desired ending inventory 126000 70000 56000 56000 Total needs 546000 700000 406000 1456000 Beginning inventory 84000 126000 70000 84000 Required purchases 462000 574000 336000 1372000 CASH DISBURSEMENTS FOR PURCHASES: For the previous month's purchases 126000 231000 287000 644000 For the current month's purchases 231000 287000 168000 686000 Total monthly payments for purchases 357000 518000 455000 1330000 CASH BUDGET: Beginning balance of cash 52000 40000 40000 52000 Receipts of cash: Cash sales 120000 180000 100000 400000 Collections from receivables 278000 438000 592000 1308000 Total cash available 450000 658000 732000 1760000 Cash disbursements: Payments for purchases 357000 518000 455000 1330000 Selling expense 79000 120000 62000 261000 Admiistrative expense (excluding depreciation) 25000 32000 21000 78000 Purchase of land 16000 0 16000 Dividends 49000 0 0 49000 Total disbursements 510000 686000 538000 1734000 Cash surplus/(deficit) -60000 -28000 194000 26000 Financing: Borrowing 100000 68000 0 168000 Repayment 0 0 168000 168000 Payment of interest 0 0 4360 4360 Total financing 100000 68000 -172360 -4360 Ending cash balance 40000 40000 21640 21640 Interest on borrowings = 100000*1%*3+68000*1%*2 = $4360

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.