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