Information and Instructions

Information

Christensen Inc. produces wiring harness assemblies used in the production of semi-trailer trucks.

1. Sch. 1: Projected sales in units for the first five months of 2020 are as follows:

Month	Units
January	10,000
February	9,500
March	12,000
April	14,000
May	16,500

2. Sch. 1: The unit selling price for the wiring harness assembly is $123.

3. Sch. 2: The desired ending finished goods inventory (in units) for each month in 2020 is 25% of the next months sales. Finished goods inventory as of 12/31/2018 is 1800 units. There is no beginning or ending WIP at any time.

4. Sch. 3: The data on materials used in production are as follows:

Direct Material	Per-Unit Usage	Unit Cost
Part #100	3	$8
Part #200	1	$11

5. Sch. 3: Inventory policy dictates that sufficient materials be on hand at the end of each month to satisfy 20% of the next months production needs. The amount of materials on hand at 12/31/2019 is equal to 20% of Januarys production needs.

6. Sch. 4: The direct labor used per unit of output is 1.7 hours. The average direct labor cost per hour is $18.

7. Sch. 5: Overhead each month is estimated using a flexible budget formula. Activity is measured in DL hours. Fixed and variable overhead costs are as follows

Overhead Category	Fixed Cost per Month	Variable Cost per DL Hour
Supplies	-	$1.15
Power	-	$0.30
Maintenance	$10,500	$1.20
Supervision	$15,000	-
Depreciation	$50,000	-
RE & PP Taxes	$9,800	-
Other	$62,000	$1.60

7. Sch. 8: Monthly selling and administrative expenses are estimated using a flexible budgeting formula. Activity is measured in units sold. Fixed and variable expenses are as follows:

	Fixed Cost per month	Variable Cost per Unit Sold
Salaries	$89,300	-
Commissions	-	$2.20
Depreciation	$15,500	-
Shipping	-	$3.00
Other	$129,200	$1.60

8. Sch. 10: In January, the company plans to purchase land for future expansion using cash. The land costs 95,000.

9. Sch. 10: All sales and purchases are for cash. The cash balance on 12/31/2019 is $116,000. For 2020 the company wants to have an ending cash balance each month of at least $130,000. If a cash shortage develops, sufficient cash will be borrowed to cover the shortage and provide the desired ending balance. Cash must be borrowed in $1,000 increments and is to be repaid the following month, as is the interest due. The annual interest rate is 8%.

Instructions

Use the spreadsheet templates in the file Budgeting Project Part 1 Budget Schedules to prepare a monthly operating budget and cash budget for the first quarter (Q1). Note that on some schedules you will also need to complete budget information for April and May.

Use excel formulas and cell references (i.e., link cells) at all times. No credit will be given for cells not utilizing these excel tools.

The following budget schedules are to be completed:

1. Sales budget
2. Production budget
3. DM purchases budget
4. DL budget
5. OH budget
6. Ending finished goods inventory budget
7. COGS budget
8. Selling & Administrative expense budget
9. Budgeted income statement (ignore income taxes)
10. Cash budget