Budgeting Problem

ABC Company is a manufacturer of Widgets. The Companys sales data for the first quarter of the upcoming year is as follows:

Budgeted Sales in units Jan 250,000; Feb 210,000; Mar 280,000; Apr 285,000; May 275,000

The selling price of each Widget is $117.

Distributors purchase Widgets on account. The percentage of sales collected in the month of sale is 55%. The percentage of sales collected in the month following the month of sale is 40%. The percentage of sales collected the second month following the month of the sale is 5%. The company doesnt have any bad debts. The accounts receivable balance at the beginning of the period is comprised of $725,000 from November Sales and $6,100,000 from December Sales.

The owner of the company realizes that excessive inventories tie up funds and create storage problems, but realizes the importance of having some inventory to fill unanticipated orders. His production manager has calculated that the ending inventory each month should be equal to 10% of the next months sales to provide an adequate cushion. The finished goods inventory at the beginning of the period was 25,000 units.

Widgets require 23 lbs of a patented plastic material per unit that the company purchases from its parent company for $1.25 per pound. The production manager has calculated that the ending inventory necessary to provide a cushion for unexpected orders needs to be 15% of the next periods production. The beginning raw materials inventory included 901,000 pounds of the patented plastic material. The company purchases raw materials on account, paying for 40% of the materials in the month of purchase, and 60% of the materials in the month following the purchase. The account payable related to materials had an account balance of $4,650,000 at the end of last year.

Each Widget requires .4 hours of direct labor for assembly. Employees are paid on an hourly basis $16 per hour. Employees at the ABC Company are hired as needed to meet demand. The owner is unwilling to hire employees through the local union because the union requires that the potential hires be guaranteed a 40 hour work week.

Overhead is assigned to units of product on the basis of machines hours. Variable manufacturing overhead costs amount to $275 per machine hour. Each unit of product requires .01 machine hours to complete. Fixed manufacturing overhead amounts to $12,000,000 each month. Included in the manufacturing overhead is depreciation in the amount of $8,375,000 each month.

The salesmen are paid $3 per unit sold in addition to their fixed salaries which total $1,060,000 of the companys monthly budget. Advertising expenses account for $1,035,000 of the companys monthly budget. Executive Salaries account for $2,300,000 of the monthly budget. Property taxes account for $60,000 of the companys monthly budget. Depreciation on office equipment accounts for $1,300,000 of the companys monthly budget. Liability insurance accounts for $10,000 of the companys monthly budget.

The companys cash balance at the end of last year was $5,120,000. Management intends to purchase a new piece of equipment in March for $2,750,000. The board of directors approved a dividend last year to be paid to shareholders in January in the amount of $7,500,000. Management wants to maintain a cash balance at the beginning of each month in the amount of $5,000,000 in the event that the company needs cash for contingencies. The company has a line of credit with the local bank in the amount of $20,000,000 in which they pay interest at a fixed rate of 1.6% per month. Funds are borrowed on the first day of each period and repaid on the last day of each period.

The balance sheet data for the company for the period ended 12/31/14 is included at the bottom of the template.

EXCEL SHEET

Sales Budget Jan Mar 210,000 Budgeted Sales in units Selling Price per Unit Total Sales Feb 250,000 117 $ 29,250,000 $ Total 280,000 117 $ 32,760,000 $ 117 $ Apr 1,300,000 585 $ 152,100,000 $ May 285,000 117 $ 33,345,000 $ is 275,000 117 32,175,000 $ 24,570,000 $ Schedule of Expected Cash Collections Mar Total Apr May check figures Nov A/R Dec A/R Jan Feb Mar Total Cash Collections Production Budget Jan Feb Mar Total Apr May Budgeted Sales Add Desired Ending Inventory Total Needs Less Beginning Inventory Required Production Direct Materials Budget Jan Feb Mar Total Apr Required production in units Raw materials needed per unit (lbs) Raw materials needed to meet production Desired Raw materials ending inventory Total Raw material needs Less Raw materials beginning inventory Raw materials to be purchased Cost per Pound Total Cost of Raw materials to be purchased Schedule of Expected Cash Disbursements Feb Mar Total Apr check figures Accounts payable January February March Direct Labor Budget Jan Feb Mar Total Apr Required production in units Direct Labor hours per unit Total Direct Labor hours needed Direct Labor Rate Total Direct Labor Cost Manufacturing Overhead Budget Required production in units Machine hours per unit Total machine hours VOH per machine hour Variable Manufacturing Overhead Fixed Manufacturing Overhead Total Manufacturing Overhead Less depreciation Cash dispursements for manufacturing overhead Total fixed manufacturing overhead Budgeted Machine hours Predetermined overhead rate Ending Finished Goods Inventory Direct materials Direct labor Manufacturing overhead pounds hours machine hours per pound per hour per machine hour Budgeted finished goods inventory Ending finished good inventory in units Unit product cost Ending finished goods inventory in dollars Selling & Administrative Expense Budget Feb Mar Total Budgeted sales in units Variable selling and administrative expense per unit Variable selling and administrative expenses Fixed selling and administrative expenses: Salaries of sales staff Advertising Executive Salaries Property Taxes Liability Insurance Depreciation Total fixed selling and administrative expenses Total selling and administrative expenses Less depreciation Cash disbursements for SG&A Cash Budget Cash balance at beginning of period Collections from customers Total cash available Less disbursements Direct mateials Direct labor Manufacturing overhead Selling & Administrative Equipment purchases Dividends Total cash disbursments Excess/ (Deficiency) Financing Borrowings Repayments Interest Total Financing Cash balance, ending Income Statement 3/31/15 Sales Cost of goods sold Gross Margin SG&A Net operating income Interest Expense Net Income 12/31/14 3/31/15 6,825,000 Balance Sheet Cash A/R Raw materials Inventory Finished goods Inventory Total Current Assets 6,825,000 Property, plant, Equipment Land Buildings Equipment Accumulated Depreciation Net Property, Plant, Equipment Total Assets 2,000,000 8,750,000 127,000,000 (37,000,000) 100,750,000 107,575,000 Current Liabilities Accounts Payable Line of credit Stockholders quity Common Stock Retained Earnings Total Stockholder Equity Total Liabilities and Stockholder's Equity 11,000,000 96,575,000 107,575,000 107,575,000 Sales Budget Jan Mar 210,000 Budgeted Sales in units Selling Price per Unit Total Sales Feb 250,000 117 $ 29,250,000 $ Total 280,000 117 $ 32,760,000 $ 117 $ Apr 1,300,000 585 $ 152,100,000 $ May 285,000 117 $ 33,345,000 $ is 275,000 117 32,175,000 $ 24,570,000 $ Schedule of Expected Cash Collections Mar Total Apr May check figures Nov A/R Dec A/R Jan Feb Mar Total Cash Collections Production Budget Jan Feb Mar Total Apr May Budgeted Sales Add Desired Ending Inventory Total Needs Less Beginning Inventory Required Production Direct Materials Budget Jan Feb Mar Total Apr Required production in units Raw materials needed per unit (lbs) Raw materials needed to meet production Desired Raw materials ending inventory Total Raw material needs Less Raw materials beginning inventory Raw materials to be purchased Cost per Pound Total Cost of Raw materials to be purchased Schedule of Expected Cash Disbursements Feb Mar Total Apr check figures Accounts payable January February March Direct Labor Budget Jan Feb Mar Total Apr Required production in units Direct Labor hours per unit Total Direct Labor hours needed Direct Labor Rate Total Direct Labor Cost Manufacturing Overhead Budget Required production in units Machine hours per unit Total machine hours VOH per machine hour Variable Manufacturing Overhead Fixed Manufacturing Overhead Total Manufacturing Overhead Less depreciation Cash dispursements for manufacturing overhead Total fixed manufacturing overhead Budgeted Machine hours Predetermined overhead rate Ending Finished Goods Inventory Direct materials Direct labor Manufacturing overhead pounds hours machine hours per pound per hour per machine hour Budgeted finished goods inventory Ending finished good inventory in units Unit product cost Ending finished goods inventory in dollars Selling & Administrative Expense Budget Feb Mar Total Budgeted sales in units Variable selling and administrative expense per unit Variable selling and administrative expenses Fixed selling and administrative expenses: Salaries of sales staff Advertising Executive Salaries Property Taxes Liability Insurance Depreciation Total fixed selling and administrative expenses Total selling and administrative expenses Less depreciation Cash disbursements for SG&A Cash Budget Cash balance at beginning of period Collections from customers Total cash available Less disbursements Direct mateials Direct labor Manufacturing overhead Selling & Administrative Equipment purchases Dividends Total cash disbursments Excess/ (Deficiency) Financing Borrowings Repayments Interest Total Financing Cash balance, ending Income Statement 3/31/15 Sales Cost of goods sold Gross Margin SG&A Net operating income Interest Expense Net Income 12/31/14 3/31/15 6,825,000 Balance Sheet Cash A/R Raw materials Inventory Finished goods Inventory Total Current Assets 6,825,000 Property, plant, Equipment Land Buildings Equipment Accumulated Depreciation Net Property, Plant, Equipment Total Assets 2,000,000 8,750,000 127,000,000 (37,000,000) 100,750,000 107,575,000 Current Liabilities Accounts Payable Line of credit Stockholders quity Common Stock Retained Earnings Total Stockholder Equity Total Liabilities and Stockholder's Equity 11,000,000 96,575,000 107,575,000 107,575,000