The owner of Northwind Unlimited, Inc. has been concerned about the shortage of
ID: 2576162 • Letter: T
Question
The owner of Northwind Unlimited, Inc. has been concerned about the shortage of cash the company experiences during various times of the year. As a recent graduate of a highly regarded business school, you have been charged with the task of developing a budget that will allow the company to get a better understanding of its cash needs during the year. You have decided to prepare a master budget for the upcoming second quarter. The accounting department and other areas of the company have provided you with the following information.
The company sells many styles of pens, but all are sold for the same price—$10 each. Actual sales of pens for the last three months and budgeted sales for the next six months follow:
January (actual) 20,000 June (budget) 50,000
February (actual) 26,000 July (budget) 30,000
March (actual) 40,000 August (budget) 28,000
April (budget) 65,000 September (budget) 25,000
May (budget) 100,000
The company experiences peak sales during May. The production manager prefers that inventory should be on hand at the end of each month to supply 40% of the pens sold in the following month.
Suppliers are paid $4 for each pen. One-half of a month’s purchases is paid for in the month of purchase; the other half is paid for in the following month. All sales are on credit. Only 20% of a month’s sales are collected in the month of sale. An additional 70% is collected in the following month, and the remaining 10% is collected in the second month following sale. Bad debts have been negligible.
Monthly operating expenses for the company are given below:
Variable:
Sales commissions 4% of sales
Fixed:
Advertising $200,000
Rent $ 18,000
Salaries $106,000
Utilities $7,000
Insurance $3,000
Depreciation $14,000
Insurance is paid on an annual basis, in November of each year.
The company plans to purchase $16,000 in new equipment during May and $40,000 in new equipment during June; both purchases will be for cash. The company declares dividends of $15,000 each quarter, payable in the first month of the following quarter.
The company’s balance sheet as of March 31 is given below:
Assets
Cash $74,000
Accounts receivable ($26,000 February sales;
$320,000 March sales) 346,000
Inventory 104,000
Prepaid insurance 21,000
Property and equipment (net) 950,000
Total assets $1,495,000
Liabilities and Stockholders’ Equity
Accounts payable $100,000
Dividends payable 15,000
Common stock 800,000
Retained earnings 580,000
Total liabilities and stockholders’ equity $1,495,000
The company maintains a minimum cash balance of $50,000. All borrowing is done at the beginning of a month; any repayments are made at the end of a month.
The company has an agreement with a bank that allows the company to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $50,000 in cash.
Required (using an excel spreadsheet):
Prepare a master budget for the three-month period ending June 30. Include the following detailed schedules:
1. A sales budget, by month and in total.
2. A schedule of expected cash collections, by month and in total.
3. A merchandise purchases budget in units and in dollars. Show the budget by month and in total.
4. A schedule of expected cash disbursements for merchandise purchases, by month and in total.
5. A cash budget. Show the budget by month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $50,000.
6. A budgeted income statement for the three-month period ending June 30. Use the contribution approach.
7. A budgeted balance sheet as of June 30.
Explanation / Answer
Sales Budget April May June Total Sales in units A 65000 100000 50000 215000 Sale Price 10 10 10 10 Sales in value 650000 1000000 500000 2150000 Schedule of Cash collection April May June Total February March July August Sales in units A 65000 100000 50000 215000 26000 40000 30000 28000 Sale Price 10 10 10 10 10 10 10 10 Sales in value 650000 1000000 500000 2150000 260000 400000 300000 280000 Feb sales 26000 26000 Marc sales (70%,10% of $400000) 280000 40000 320000 April sales (20%,70%,10%) 130000 455000 65000 650000 May Sales 200000 700000 900000 June sales 100000 100000 Total A 436000 695000 865000 1996000 Accounts Receivable May 1000000*.1+June 500000*.8= 500000 ans 3 Purcahse Budget April May June Total July Units to be sold 65000 100000 50000 215000 30000 Closing Inventory 40% of next month sales S*.4 40000 20000 12000 12000 Total Finised Googd 105000 120000 62000 227000 Less: Beginning Inventory 26000 40000 20000 26000 Units to be produced 79000 80000 42000 201000 Purchase Price 4 4 4 4 Total Purchase price A 316000 320000 168000 804000 COGS= Units sold*4 260000 400000 200000 860000 Cash Disbursement of Mercandise Purchases From march purchases 100000 100000 From April Purchases 158000 158000 316000 From May Purchases 160000 160000 320000 From June purchases 84000 84000 Total B 258000 318000 244000 820000 Accounts payable as on 30 June 84000 working Cash payments payment of Inventory 258000 318000 244000 820000 Sales Commissions @ 4% 26000 40000 20000 86000 Advertising 200000 200000 200000 600000 Rent 18000 18000 18000 54000 Salaries 106000 106000 106000 318000 Utilities 7000 7000 7000 21000 Dividend Paid 15000 15000 Equipment purchased 16000 40000 56000 April May J June Total Beginning Cash balance 74000 $50,000 $50,000 74000 Cash receipt 436000 695000 865000 1996000 Total cash available 510000 745000 915000 2070000 Less: Cash Disbursements payment of Inventory 258000 318000 244000 820000 Sales Commissions @ 4% 26000 40000 20000 86000 Advertising 200000 200000 200000 600000 Rent 18000 18000 18000 54000 Salaries 106000 106000 106000 318000 Utilities 7000 7000 7000 21000 Dividend Paid 15000 0 0 15000 Equipment purchased 0 16000 40000 56000 Total cash Disbursements $630,000 $705,000 $635,000 $1,970,000 Excess /(deficiency) of cash receipts over cash disbursements ($120,000) $40,000 $280,000 $100,000 Minimum Cash balance (working) 50000 50000 50000 50000 Financing Borrowed 170000 10000 $180,000 Repaid -180000 ($180,000) Interest Repaid -5300 ($5,300) Total financing 170000 10000 -185300 -5300 Cash Balance $0 $0 $44,700 $180,000 Ending Cash balance $50,000 $50,000 $94,700 $94,700 Income Statement as on 3o June 2016 April May June Total Sales 650000 1000000 500000 2150000 Less: variable Cost Cost of Good Sold 260000 400000 200000 860000 Sales Commissions @ 4% 26000 40000 20000 86000 Contribution 364000 560000 280000 1204000 Fixed Expenses Advertising 200000 200000 200000 600000 Salaries 18000 18000 18000 54000 Utilities 106000 106000 106000 318000 Rent 7000 7000 7000 21000 Insurance 3000 3000 3000 9000 Depreciation 14000 14000 14000 42000 total fixed expenses 348000 348000 348000 1044000 Operating Income 16000 212000 -68000 160000 Interest On Short Term Loan 1700 1800 1800 5300 Net Income 14300 210200 -69800 154700 Please enter the total amout of Income statement April May and June is just shown for your understanding Statement of Retained earnings Opening balance 580000 Add: Net Income for the year 154700 Less: Dividend payable -15000 Balance June 30 719700 Accounts Receivable May sales (1000000*10%) 100000 June sales (500000*80%) 400000 Total 500000 BalANCE Sheet as on 30 June Assets Cash 94700 Accounts Receivable 500000 Inventory 48000 Prepaid Insurance=(21000-(3000*3) 12000 Property & Equipment 1006000 Less: Accumulated Depreciation -42000 1618700 Liabilities & Stockholder equity Accounts payable 84000 Dividend Payable 15000 Total Laibilities 99000 Stockholder Equity Common Stock 800000 Retained earnings 719700 Total Stockholder Equity 1519700 TotalLiabilities & Stockholder Equity 1618700
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.