Question

Comprehensive Problem #2 Budget Project Accounting 2302 The Excel file of this project is to understand and apply the basic concepts of profit planning. Use the to complete the assignment and submit the completed Excel file on Blackboard. a comprehensive 6-month budget, including supporting schedules, for the period January 1, 2018 to June 30, 2018 for Henron, Inc. (a fictional company). The budgets must be prepared on Excel using the templates I have prepared Part of this project is demonstrating proper use of Excel. You may only input a hard number" into a . All yellow celis must be formulas (use appropriate cell referencing). I recommend constructing the formulas for one month and then copying the formulas over to the remaining months. Beware of exceptions to this rule and let me know if you don't know how to do specific formulas, copy/paste, "sticky references, etc. Be careful with the '6 mos total" columns! Sometimes, they are a sum of the 6 months, but sometimes not (for instance, beginning inventory, or total cash available). Remember not to include Nov and Dec of 2017 in the totals. The budget templates and this instruction sheet are located on the comprehensive problems page in Blackboard. Make sure you save the file to excel and then open the file through Excel (not Internet Explorer). Check figures are also located under-Comprehensive Problem #2" in Blackboard. INFORMATION FOR HENRON, INC. BUDGET PROJECT Heron, Inc. is a company that re-sells one product, a lawn chair. A contractor makes the product exclusively for Heron, so Heron has no manufacturing costs. As of November 2017, each lawn chair costs Henron $4. Henron sells each chair for $10 per unit, but plans to raise the sales price to $11.25 per unit beginning May 1, 2018 1. 2. The estimated sales (in units) are as follows: Nov 17Dec 17 Jan 18 Feb 18 Mar 18 Apr 18 May 18 Jun 18 Jul 18 13,000 13,000 11,000 11,000 11500 12,500 16,000 18,000 17.000 3. Thirty percent of any month's sales are for cash, and the remaining 70% are on credit. y percent of the credit sales are collected in the month Cf sale 50% are collected in the following month, and 16% are collected in the second month after the sale. The remaining-4%are deemed uncollectible and written off as Bad Debt Expense in the month the debt is deemed uncollectible (e.g. if the sale is made in January and is not collected by the end of March, it is written off in March). They do not use the allowance method, no estimate of bad debt is needed. The firm's policy regarding inventory is to stock (ie, have in ending inventory) 40% of the estimated sales for the next month 4. 30 Page 1 of 2 elete percent of the inventory purchases are ining paid for in the month of purchase and the 60% are paid in the following month (i.e. all of the previous month's Accounts Payable are paid off by the end of any month) Henron bought some equipment in 2017 (so the full cost is already included in Equipment, and does NOT need to be added to the balance sheet again), but the contract requires turn ayments in 2018. A cash payment of $60,000 is due in January. and another payment of $30.00o 7. Dividends of $12,000 are to be paid in March. 8. Monthly operating expenses consist of the following (any cash expenses are paid as incurred): s and Wages Expense 7% of sales revenue $6,000 6% of sales revenue $1,000 $20,000 $24,000 Sales Commissions Rent Expense Other Variable Cash Expenses Supplies Expense Other Overhead Expense Depreciation Expense 9. Henron must maintain a minimum cash balance of $15,000. Borrowing can make up shortfalls For simplicity, assume that the bank will only lend (and accept repayments) in $1,000 increments. Ignore interest on the loan in your calculations, but minimize the amount borrowed and pay off any loans as soon as possible. 10. Cash on hand as of December 31, 2017 is expected to be $15,000. In addition, there will be no notes payable (loan balance) as of this date. 11. See below the other Balance Sheet accounts with their balances as of December 31, 2017 Buildings and Equipment $1,050,000 Accumulated Deprecation $520,000 Common Stock Retained Earnings Note: You must add current year depreciation expense to accumulated depreciation. $200,000 $336,220 12. Henron maintains office supplies of $1,000 at the end of each month. 13. Accounts receivable consists of the credit sales that have not been received or written off Accounts payable consists of inventory purchases that have not yet been paid. Page 2 of 2

Answer 1

Sales Budget	January	February	March	April	May	June	Total
Sales (Unit)	11000	11000	11500	12500	16000	18000
Selling Price per unit	10	10	10	10	11.25	11.25
Total Budgeted Sales	110000	110000	115000	125000	180000	202500	842500
Cash Sales	33000	33000	34500	37500	54000	60750	252750
Credit Sales	77000	77000	80500	87500	126000	141750	589750
Total Cash Collection	January	February	March	April	May	June	Total
Cash Sales	33000	33000	34500	37500	54000	60750	252750
Cash Collection from credit sales	83160	76160	74970	62720	94430	119525	510965
Total Cash Collection	116160	109160	109470	100220	148430	180275	763715

Schedule of Expected Cash collections from Credit Sales	Sales	Percentage	January	February	March	April	May	June
November sales	91000	16%	14560
December sales	91000
Collected in January		50%	45500
Collected in February		16%		14560
January Sales	77000
Collected in January		30%	23100
Collected in February		50%		38500
Collected in March		16%			12320
February Sales	77000
Collected in February		30%		23100
Collected in March		50%			38500
Collected in April		16%				12320
March Sales	80500
Collected in March		30%			24150
Collected in April		50%				24150
Collected in May		16%					12880
April Sales	87500
Collected in April		30%				26250
Collected in May		50%					43750
Collected in June		16%						14000
May sales	126000
Collected in May		30%					37800
Collected in June		50%						63000
June sales	141750
Collected in June		30%						42525
Total Cash Collection from credit sales .			83160	76160	74970	62720	94430	119525

Merchandise Purchase Budget	January	February	March	April	May	June	Total
Sales unit	11000	11000	11500	12500	16000	18000	80000
Add: Desired Ending Inventory	4400	4600	5000	6400	7200	6800	34400
Total Required	15400	15600	16500	18900	23200	24800	114400
Less: Opening Inventory	4400	4400	4600	5000	6400	7200	32000
Purchases	11000	11200	11900	13900	16800	17600	82400
Purchase cost($4)	4	4	4	4	4	4
Total Purchase cost	44000	44800	47600	55600	67200	70400	329600

Schedule of Expected Payments for purchases	January	February	March	April	May	June	Total
40% paid in the month of purchase	17600	17920	19040	22240	26880	28160	131840
Previous months account receivable	29280	26400	26880	28560	33360	40320	184800
Total Expected Payments	46880	44320	45920	50800	60240	68480	316640

Cash Budget	January	February	March	April	May	June	Total
Beginning cash balance	15000	15980	15520	35120	54290	105080	15000
Collections from customers	116160	109160	109470	100220	148430	180275	763715
Cash available	131160	125140	124990	135340	202720	285355	778715
Less payments
For inventory purchases	46880	44320	45920	50800	60240	68480	316640
For salaries and wages	7000	7000	7000	7000	7000	7000	42000
Sales Commission	7700	7700	8050	8750	12600	14175	58975
Rent Expenses	6000	6000	6000	6000	6000	6000	36000
Other variable cash expenses	6600	6600	6900	7500	10800	12150	50550
Supplies expense	1000	1000	1000	1000	1000	1000	6000
Payment for equiment purchased earlier	50000	30000					80000
Pay dividend			12000				12000
Total budgeted payments	125180	102620	86870	81050	97640	108805	602165
Cash balance before borrow/repay	5980	22520	38120	54290	105080	176550	176550
Financing activity
Borrowing (repayment)	10000	-7000	-3000				0
Ending cash balance	15980	15520	35120	54290	105080	176550	176550

Income Statement	Total
Sales Revenue	842500
Less:Expenses
For inventory purchases	329600
For salaries and wages	42000
Sales Commission	58975
Rent Expenses	36000
Other variable cash expenses	50550
Supplies expense	6000
Other overhead expenses	120000
Depreciation expense	144000
Bad debt Expense	23590
Total Expense	810715
Net Profit	31785

Balance Sheet Accounts
Cash	$176550
Accounts Receivable	124425
Inventory	27200
Buildings and Equipment	1050000
Accumulated Depreciation	664000
Accounts Payable	42240
Common Stock	200000
Retained Earnings	368005