Question

nviro Warren Corporation - Budget Project Warren Their sales data has come up from the Sales Department for the next quarter, the first quarter of the year. You and your staff have the following information about prior period financial relationships and are to put together a Formal Budget for the company. (Please see the instructions for the project about the required output.) Balance Sheet as of December 31, 20XX (last year) $ 100,000 200,000 15,000 250,000 30,000 250,000 750,000 100,000 150,000 100,000 1,000,000 215,000 Cash Accounts Receivable Allowance for Doubtful Accounts Inventory Prepaid Expenses Land Fixed Assets Accumulated Depreciation Accounts Payable Accrued Liabilities Capital Stock Retained Earnings The following has been compiled from historic financial reports: a) Sales for the 4th quarter of the prior year amounted to $1,000,000 and are projected at $1,250,000 and $2,000,000 for the first two quarters of this year b) Sales are all on credit, with 80% collected by the end of the quarter and the remainder collected in the following quarter, except for 2% which is never collected. The allowance for doubtful accounts shows the estimated amount of the uncollectibles as of December 31 of the prior year. No A/R is expected to be written off in the first quarter c) Ending inventories at the end of each quarter are expected to equal one third of the next quarter's sales. The Cost of Goods Sold average 60% of Sales d) All inventory purchases are credited to Accounts Payable and paid for as discussed below e) Monthly expenses are divided into three categories Budgeted Breakdown Accounts Accrued Payable Liability PrepaidsDepreciation Selling expenses $50,000 + 8% of Sales | $ 10,000 | $120,000 | $ 14,000 | $ Purchasing expenses 35,0005% of Purchas 5,000 S 65,000 S 5,500 S Admin. Expenses * |$62,000+ 3% of Sales | $ 10,000 $ 55,000 | $ 5.500 | $ 6,000 4,500 4,500 "Bad debts are estimated to be 2% of Sales Prepaid Expenses are paid at a rate of $40,000 per quarter Federal Income Taxes are budgeted at 40% of net income before taxes and are credited to Accrued Liabilities f) Beginning Accounts Payable plus 80% of current credits will be paid during the quarter. Beginning Accrued Liabilities except for $30,000 will be paid during the quarter plus the current quarter credits except for $120,000 g) New Assets have been purchased FOB destination for $150,000. These will arrive on April 2 with payment to be made all in cash on that day, April 2. Any cash flow problem? h) The company must maintain a minimum cash balance of $100,000. At 10% interest, an open line of credit is available at a local bank. If funds are needed, they must be borrowed at the beginning of the month they will be needed. If there are excess funds at the end of the month, the company will pay all or part of the loan on a FIFO basis plus the interest the amount paid. All borrowing and principle repayments must be in multiples of $1,000

I need to do a cash receipts, cash payments, and cash budget, and a budgeted Balance Sheet with the above info.

I did it myself already, but my numbers arent adding up.

Edit: the balance sheet isnt adding up. I'm having trouble on the cash account entry. The wording on the accounts payable/accrued liability calculation is weird(f).Yes, cash is given for the previous quarter at 100,000.

Edit2: Did u make a balance sheet, and then realize it didnt match? If so that is the problem that i have run into as well

Answer 1

Calculation of Cash receipts from Customers
	Quarter 1
Total Budgeted Sales	$1,250,000
Credit Sales	$1,250,000
Total Cash receipts from customer
Credit sales collection in the quarter of sale-80%	$1,000,000
Credit sales collection for the previous quarter	$180,000
Total Cash receipts from customer	$1,180,000
Cash Payments:
Accounts payable	$890,000
Accrued liabilities	$258,200
Prepaid expenses	$40,000
Total Cash payments	$1,188,200
Cash inflow	($8,200)
Opening Balance	$100,000
Cash balance available	$91,800
Addittional Loan (Line of Credit)	$159,000
Ending Balance	$250,800
Accounts payable
Beginning Balance		$150,000
Current credits during the quarter
Inventory (45000/5%)	$900,000
Monthly expenses	$25,000
	$925,000
80% of current credits		$740,000
		$890,000
Accrued Liabilities
Beginning Accrued liabilities	($100000-$30000)	$70,000
Current credits during the quarter
Federal tax	$68,200
Monthly expenses	$240,000
	$308,200
	($120,000)	$188,200
		$258,200
Budgeted income statement for the quarter
	Amount
Sales Revenue		$1,250,000
Less: Cost of goods sold		$750,000
Gross Profit		$500,000
Operating Expenses
Selling expenses	$150,000
Purchasing expense	$80,000
Administrative expenses	$99,500
Total operating expenses		$329,500
Operating Profit		$170,500
Tax @40%		$68,200
Net Income		$102,300
*bad debts expenses are $24500 ($99500-10000-55000-5500-4500)
Budgeted balance sheet as of March 31, 20X1
Cash		$250,800
Accounts receivable	$270,000
Allowance for doubtful debts	$39,500	$230,500
Inventory		$400,000
Prepaid expenses		$45,000
Land		$250,000
Fixed Assets	$750,000
Accumulated depreciation	$115,000	$635,000
Total Assets		$1,811,300
Accounts Payable		$185,000
Accrued liabilities		$150,000
Additional loan (line of credit)		$159,000
Common Stock		$1,000,000
Retained Earnings		$317,300
		$1,811,300