Comprehensive Problem #2 Budget Project Accounting 2302

The objective of this project is to understand and apply the basic concepts of profit planning. Use the Excel file to complete the assignment and submit the completed Excel file on Blackboard.

Required: A comprehensive 6-month budget, including supporting schedules, for the period January 1, 2018 to June 30, 2018 for Henron, Inc. (a fictional company).

Notes and Hints

1. The schedules/budgets must be prepared on Excel. The templates I have prepared must be used as is.

2. Part of this project is demonstrating proper use of Excel. You may only input a "hard number" into a pink cell. All yellow cells must be formula based (no numbers included - use appropriate cell referencing).

3. 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, etc.

4. 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.

1. The budget templates and this instruction sheet are located on the course materials page.Make sure you save the file to excel and then open the file through Excel (not Internet Explorer).

1. Check figures are also located on the course materials page.

INFORMATION FOR HENRON, INC. BUDGET PROJECT

1. 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. Henron sells each chair for $10 per unit, but plans to raise the sales price to $11.00 per unit beginning May 1, 2018

2. The estimated sales (in units) are as follows:

Nov 17

12,000

Dec 17

13,000

Jan 18

9,000

Feb 18

10,000

Mar 18

13,000

Apr 18

15,000

May 18

18,000

June 18

18,000

July 18

17,000

3. They expect that 60% of any month's sales are for cash, and the remaining 40% are on credit. Of the credit sales, they expect to collect 20% in the month of the sale, 70% in the following month, and 10% in the month after that

4. The firm's policy regarding inventory is to stock (i.e. have in ending inventory) 30% of the estimated sales for the next month.

5. Each lawn chair costs Henron $6. They plan to pay for 30% of the inventory purchases in the month of purchase, and pay the remaining 70% the following month (i.e. all of the previous month's Accounts Payable are paid off by the end of any month.)

6. Monthly operating expenses consist of the following (any cash expenses are paid as incurred):

Salaries and Wages

$7,000

Sales Commissions

7% of sales revenue

Rent

$6,000

Supplies Expense

$2,000

Other Overhead Expense

$5,000

Depreciation Expense

$2,500

7. Henron must maintain a minimum cash balance of $30,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 only borrow what you need, and pay off any loans as soon as possible.

8. 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.

12. See below the other Balance Sheet accounts with their balances as of the beginning of the year:

Buildings and Equipment.............................................$ 1,050,000

Accumulated Depreciation..........520,000

Common Stock................................... 200,000

Retained Earnings.............................. 360,040

Note: You must add current year depreciation expense to accumulated depreciation.

10.Henron maintains office supplies of $2,000 at the end of each month.

11.Accounts receivable consists of the credit sales that have not been received.

Accounts payable consists of inventory purchases that have not yet been paid.