Broncos Corp. manufactures wooden frames with bronco medallion for framing the college degrees awarded by Western Michigan University. Each Bronco frame sells for $150 and requires:

5 linear feet of special Oak wood that costs $4.00 per foot

Other direct material package (each package includes one Bronco Medallion, a glass face, and a cardboard cutout) that costs $25 each package (bought from an outside supplier)

30 minutes of labor hours to build (Labor cost averages $15.00 per hour)

Bronco Corp. has the following inventory policies:

Ending finished goods inventory should be 25% of next month's sales.

Ending inventory of Oak wood should be 30% of next month's production need.

Ending inventory of other direct material package should be 20% of next month's production need. (the supply chain for the other direct material packages is quite efficient and therefore, company maintains a smaller inventory of these packages as compared to Oak wood inventory)

As per the Marketing and Sales department of the Bronco Corp., sales are high in the month of graduation and the month following the graduation during Fall and Spring each year. Therefore, months of December, January, April, and May are considered high demand months. In a November budget meeting of the current year, the Sales Manager provided following estimates of unit sales for the upcoming months (December current year - May next year):

December 2,000 frames

January 1,500 frames

February 800 frames

March 760 frames

April 2,100 frames

May 1,800 frames

Variable manufacturing overhead is incurred at a rate of $12.50 per frame produced. Annual fixed manufacturing overhead is estimated to be $300,000 ($25,000 per month) for expected production of 20,000 frames for the year. Fixed selling and administrative expenses are estimated at $31,000 per month and variable selling and administrative expenses are estimated at $15.00 per unit sold.

Of its sales each month, 80% is collected in the same month and remaining 20% in the month following the sales.

Of the purchase of Oak wood, 60% is paid for during the month of purchase and remaining 40% is paid in the following month. Budgeted Oak wood purchase for December is $34,200. Other direct material package purchases are all paid for in the month of purchase itself. Also, all other operating costs are paid during the month incurred. Monthly fixed manufacturing overhead includes $5,000 in depreciation. During January, Bronco Corp. plans to pay $110,000 for a piece of equipment to replace an old equipment.

Bronco had $61,000 cash on hand on January 1. The company has a policy to maintain a monthly minimum cash balance of $50,000. The company may borrow any amount using the credit line provided by their bank to pay for deficits and maintain the minimum required balance of cash. Borrowings or any part of the borrowings may be paid off in the month there is excess cash available (Ignore interest on borrowings).

Requirements:

Using the information provided above prepare the following budgets for the first quarter (January - March) for Bronco Corporation. Include each month and quarter 1 total for each budget.

Sales budget

Production budget

Direct Materials budgets (Hint: You will need TWO Direct Material Purchases budgets - one for the Oak wood and another for the Direct Material Package.)

Direct labor budget

Manufacturing Overheads budget

Budgeted Cost of Goods Sold (COGS)

Selling and administrative expenses budget

Prepare Bronco's budgeted income statement for quarter 1.

Prepare the following for Bronco for quarter 1:

Budgeted cash receipts/collection each month (including quarter 1 total)

Budgeted cash payments each month (including quarter 1 total)

Cash budget of Bronco for quarter 1.

After completing requirements 1-3 above revise the budget spreadsheet to include following changes:

Bronco Corp. is contemplating to increase the selling price of the frames by 10% during the high sales months (graduation months) of December- January and April-May. The management of Bronco believes that it would affect unit sales marginally only, thereby, reducing unit sales by 5% in those months. How would these two changes affect the income of Quarter 1? Based on the analysis, should Bronco Corp. increase the price of frames temporarily during those high demand months? Include a short explanation in Excel file itself.

Sheet 1: Original Data

Given Data:
Sales price per unit
Linear feet of oak wood per frame
Oak wood cost per foot
other DM package per frame
Other DM package cost per unit
Labor hours per unit
Labor rate per hour
Ending Finished goods inventory policy
Ending inventory policy for Oak wood
Ending inventory policy for other DM package
Variable mfg OH rate per unit produced
Fixed mfg OH per month
Fixed selling and adm expenses per month
Variable selling and adm expenses per unit sold
Annual fixed manufacturing overhead
Annual production volume in units
		Sales Budget Quarter 2
Budgeted	December	January	February	March	Quarter 1	April	May
Sales (in units)
x sales price per unit
Total Sales Revenue ($)
		Production Budget
	December	January	February	March	Quarter 1	April	May
Budgeted Sales (in units)
Add: Required Ending Inventory of Fin. Goods
Less: Bud. Beginning Inventory of Fin. Goods
Budgeted Production (in units)
		Oak Wood Purchase Budget
	December	January	February	March	Quarter 1	April
Bud. Production (in units)
Oak Wood per frame (linear feet)
Oak wood needed for Production (linear feet)
Add: Ending inventory of Oak wood (linear feet)
Less: Beg. Inventory of Oak wood (linear feet)
Budgeted Oak wood Purchase (in linear feet)
Oak wood Cost per foot
Total Budgeted Oak wood Purchase ($)
		Other Direct Material Package Purchase Budget
	December	January	February	March	Quarter 1	April
Bud. Production (in units)
Other DM package per frame (in units)
Other DM packages needed for Production (units)
Add: Ending inventory of other DM packages (units)
Less: Beg. Inventory of other DM packages (units)
Bud. Purchase of Other DM packages (in units)
Cost per package
Total Bud. Purchase Cost of other DM packages ($)
		Direct Labor Budget
		January	February	March	Quarter 1
Bud. Production (in units)
DL hours per frame
Bud. DL Hours needed for Production
DL rate per hour ($)
Bud. DL Cost ($)
		MOH Budget						Mfg. Cost per unit
		January	February	March	Quarter 1		DM1 (oakwood)				$0.00
Bud. Production (in units)							DM2 (other DM package)				$-
Var. MOH per frame							DL				$0.00
Bud. Total Var. MOH ($)							Var.MOH				$0.00
Bud. Total Fixed MOH ($)							Fixed MOH				#DIV/0!
Bud. TOTAL MOH ($)							Total	Mfg. Cost per unit			#DIV/0!
		COGS Budget
		January	February	March	Quarter 1
Budgeted Sales
* Mfg. cost per unit
Bud. COGS
		Selling and Adm. Expense Budget
		January	February	March	Quarter 1
Budgeted Sales (in units)
Variable S&A Expense per unit
Budgeted Variable S&A Expense Total
Budgeted Fixed S&A Expense Total
Budgeted Total S&A Expense
		Budgeted Income Statement
		January	February	March	Quarter 1
Budgeted Sales Revenue
Less: Budgeted COGS
Gross Margin
Less: Bud. S&A Expenses
Net Operating Income (Loss)
		Budgeted Cash Receipts/Collection
	December	January	February	March	Quarter 1
Budgeted Sales ($)
Current month's sales collected, 80%
Prior month's sales collected, 20%
Budgeted Total Cash collection/receipt
		Budgeted Cash Payments
	December	January	February	March	Quarter 1
Budgeted Oak wood Purchase ($)
Bud. Cash payment toward current month purchase(60%)
Bud. Cash payment toward prior month purchase (40%)
Bud. Total Cash payment toward Oakwood purchase
Budgeted Purchase of other DM packages ($) (all cash)
Bud. Cash payment towards DL cost
Budgeted MOH costs
Less: Depreciation cost (non cash expense)
Budgeted S&A Expenses
Budgeted Capital Expenditure
Budgeted Total Cash Payments
		Cash Budget
	December	January	February	March	Quarter 1
Beginning Cash Balance
Budgeted Cash Collection/ Receipts
Less: Budgeted Cash Payments
Preliminary Cash Balance
Borrowings and Repayments:
Borrowing
Repayment of Principal
Ending Cash Balance

Sheet 2: Revised Estimates

Given Data:
Sales price per unit
Linear feet of oak wood per frame
Oak wood cost per foot
other DM package per frame
Other DM package cost per unit
Labor hours per unit
Labor rate per hour
Ending Finished goods inventory policy
Ending inventory policy for Oak wood
Ending inventory policy for other DM package
Variable mfg OH rate per unit produced
Fixed mfg OH per month
Fixed selling and adm expenses per month
Variable selling and adm expenses per unit sold
Annual fixed manufacturing overhead
Annual production volume in units
		Sales Budget Quarter 2
Budgeted	December	January	February	March	Quarter 1	April	May
Sales (in units)
x sales price per unit
Total Sales Revenue ($)
		Production Budget
	December	January	February	March	Quarter 1	April	May
Budgeted Sales (in units)
Add: Required Ending Inventory of Fin. Goods
Less: Bud. Beginning Inventory of Fin. Goods
Budgeted Production (in units)
					0
		Oak Wood Purchase Budget
	December	January	February	March	Quarter 1	April
Bud. Production (in units)
Oak Wood per frame (linear feet)
Oak wood needed for Production (linear feet)
Add: Ending inventory of Oak wood (linear feet)
Less: Beg. Inventory of Oak wood (linear feet)
Budgeted Oak wood Purchase (in linear feet)
Oak wood Cost per foot
Total Budgeted Oak wood Purchase ($)
					$0.00
		Other Direct Material Package Purchase Budget
	December	January	February	March	Quarter 1	April
Bud. Production (in units)
Other DM package per frame (in units)
Other DM packages needed for Production (units)
Add: Ending inventory of other DM packages (units)
Less: Beg. Inventory of other DM packages (units)
Bud. Purchase of Other DM packages (in units)
Cost per package
Total Bud. Purchase Cost of other DM packages ($)
		Direct Labor Budget
		January	February	March	Quarter 1
Bud. Production (in units)
DL hours per frame
Bud. DL Hours needed for Production
DL rate per hour ($)
Bud. DL Cost ($)
		MOH Budget						Mfg. Cost per unit
		January	February	March	Quarter 1		DM1 (oakwood)
Bud. Production (in units)							DM2 (other DM package)
Var. MOH per frame							DL
Bud. Total Var. MOH ($)							Var.MOH
Bud. Total Fixed MOH ($)							Fixed MOH
Bud. TOTAL MOH ($)								Mfg. Cost per unit
		COGS Budget
		January	February	March	Quarter 1
Budgeted Sales
* Mfg. cost per unit
Bud. COGS
		Selling and Adm. Expense Budget
		January	February	March	Quarter 1
Budgeted Sales (in units)
Variable S&A Expense per unit
Budgeted Variable S&A Expense Total
Budgeted Fixed S&A Expense Total
Budgeted Total S&A Expense
		Budgeted Income Statement
		January	February	March	Quarter 1
Budgeted Sales Revenue
Less: Budgeted COGS
Gross Margin
Less: Bud. S&A Expenses
Net Operating Income (Loss)
		Budgeted Cash Receipts/Collection
	December	January	February	March	Quarter 1
Budgeted Sales ($)
Current month's sales collected, 80%
Prior month's sales collected, 20%
Budgeted Total Cash collection/receipt
		Budgeted Cash Payments
	December	January	February	March	Quarter 1
Budgeted Oak wood Purchase ($)
Bud. Cash payment toward current month purchase
Bud. Cash payment toward prior month purchase
Bud. Total Cash payment toward Oakwood purchase
Budgeted Purchase of other DM packages ($) (all cash)
Bud. Cash payment towards DL cost
Budgeted MOH costs
Less: Depreciation cost (non cash expense)
Budgeted S&A Expenses
Budgeted Capital Expenditure
Budgeted Total Cash Payments
		Cash Budget
	December	January	February	March	Quarter 1
Beginning Cash Balance
Budgeted Cash Collection/ Receipts
Less: Budgeted Cash Payments
Preliminary Cash Balance
Borrowings and Repayments:
Borrowing
Repayment of Principal
Ending Cash Balance