80.2K

Verified Solution

Link Copied!

This project is a master budgeting project using the Williams Company scenario presented below. Complete your master budget using the provided Excel spreadsheet template (i.e., Williams Company Template.xlsx) and submit the completed in this budget template using the submission link in this BBL folder.

The Williams Company manufactures PVC piping in long pipes which a finish manufacturer purchases and cuts to length for sale to retailers. Williams Company manufactures their piping using resin, sheets of raw PVC, and direct labor.

The Williams Company has the following balance sheet as of December 31^st.

Balance Sheet on December 31^st

Assets
Cash		$41700
Accounts Receivable		192000
Raw Materials		102240
Finished Goods		64400
Land		50000
Plant and Equipment	500000
Less: Accumulated Depreciation	112000	388000
Total Assets		$838340
Liabilities and Stockholders Equity
Accounts Payable to Suppliers		$40000
Common Stock	100000
Retained Earnings	698340	798340
Total Liabilities and Equity		$838340

The information shown below is extracted from the records of the Williams Company.

1. The Williams Company has projected its unit sales for the next five months to be:

Month	Units
January	7000
February	8000
March	10000
April	8000
May	7000

All sales are made on accounts receivable. Each unit of PVC sells for $60. Forty percent of all sales are collected in the month of the sale. The remaining 60% of the sales are collected in the following month. May consider bad debts as non-existent.

2. Williams Company has a management rule that at the end of each month the ending finished goods inventory of PVC must be 20% of the following months forecasted sales. Williams Companys finished goods inventory on December 31^st consists of 1400 units of PVC piping.

3. In order to produce one unit of PVC pipe, the following units of raw PVC and resin are used.

Raw Material	Units
Raw PVC	5
Resin	3

The price of raw PVC sheets is now $4.00 per unit. The price of resin is $2.80 per unit. Management desires to maintain ending raw materials inventories for raw PVC and resin at 25% of the next months production needs. William Companys December 31^st raw material holdings were 18,000 units of raw PVC (at $4.00 per unit) and 10,800 units of resin (at $2.80 per unit).

4. Seventy percent of all purchases (raw PVC and resin) are paid in the month of purchase. The remaining 30% is placed on an account payable and paid the following month.

5. The production of PVC by Williams Company requires 30 minutes of direct labor time to complete. All labor costs are paid in the month incurred. Each hour of direct labor costs Williams Company $24.

6. Factory overhead is applied at the rate of $12 per direct labor hour. Actual overhead costs are paid as they are incurred. Monthly differences between applied and actual overhead costs are expected to be negligible.

7. Selling and Administrative expenses are $10,000 per month plus 10% of sales. All these expenses are paid in the month in which they are incurred.

8. Plant and equipment depreciate at the rate of $12,000 per year. This depreciation is incurred evenly throughout the year and is included in the factory overhead costs mentioned earlier.

Sales Budget Williams Company First Quarter January February March Quarter 1 Expected unit sales Unit selling price Total sales $0 SO $0 SO Production Budget in Units Williams Company First Quarter January February March Quarter 1 April May 7,000 8,000 10,000 8,000 7,000 Expected unit sales Add: Desired ending finished goods units Total required units Less: Beginning finished goods units Required production units Notes Desired ending inventory is currently of next month's expected sales 20% Raw Materials Budget Williams Company First Quarter April January February March Quarter 1 Units to be produced 7,000 8,000 10,000 RAW PVC RAW PVC per unit Units of RAW PVC needed for production Add: Desired ending RAW PVC inventory Total RAW PVC units required Less: Beginning RAW PVC inventory RAW PVC materials purchase Cost per unit of RAW PVC Total cost of RAW PVC purchases 25% Note: Desired ending inventory of RA of next month's production needs Quarter 1 April January February March Units to be produced RESIN RESIN per unit Units of RESIN needed for production Add: Desired ending RESIN inventory TotalRESIN units required Less: Beginning RESIN inventory RESIN materials purchase Cost per unit of RESIN Total cost of RESIN purchases 25% Note: Desired ending inventory of RE of next month's production needs Direct Labor Budget Williams Company First Quarter February March Quarter 1 January Units to be produced Direct labor time (hours) per unit Total required direct labor hours Direct labor costs per hour Total direct labor costs PVC Unit Costs Williams Company First Quarter January-March Units per PVC pip Unit Price Cost per PVC pipe Cost Element Raw materials RAW PVC RESIN Direct labor Factory overhead (per direct labor hour) Unit cost per PVC pipe $0.00 Selling and Administrative Expense Budget Williams Company First Quarter February March Quarter 1 January Variable expenses Sales Selling and adminstrative expense percentage Total variable selling and administrative expense Fixed expenses Fixed selling and administrative expense Total selling and administrative expense Cash Budget Williams Company First Quarter January February March Beginning cash balance Add: Receipts Cash sales to customers Cash collected from accounts receivable Total available cash Less: Disursements Raw materials cash purchases Raw materials accounts payable purchases Direct labor Factory overhead Selling and administrative expense Income tax expense Total disbursements Excess (deficiency) of available cash over cash disbursements $ $ $ $ Budgeted Balance Sheet Williams Company First Quarter End of Januar End of Febraur End of March Quarter 1 Assets Cash Accounts receivable Finished goods inventory Raw materials inventory RAW PVC RESIN Land Building and equipment Less: Accummulated depreciation Total assets Liabilities & Stockholders' Equity Accounts payable Common Stock Retained earnings Total liabilities & Stockholders' Equity $0 $0 $0 Budgeted Income Statement Williams Company First Quarter January February March Quarter 1 Sales Cost of goods sold (units sold*unit cost) Gross profit Selling and administration expenses Income from operations Interest expense Income before income taxes Income tax expense Net income $0 $0 $0 $0 Notes: 1. 1400 finished PVC in inventory 12/31 balance sheet value of $64,400. Unit cost for these units $64,400/1400=$46. 2. COGS in January calculated 1400*$ 46 plus (7000-1400)*$ 46.4 Sales Budget Williams Company First Quarter January February March Quarter 1 Expected unit sales Unit selling price Total sales $0 SO $0 SO Production Budget in Units Williams Company First Quarter January February March Quarter 1 April May 7,000 8,000 10,000 8,000 7,000 Expected unit sales Add: Desired ending finished goods units Total required units Less: Beginning finished goods units Required production units Notes Desired ending inventory is currently of next month's expected sales 20% Raw Materials Budget Williams Company First Quarter April January February March Quarter 1 Units to be produced 7,000 8,000 10,000 RAW PVC RAW PVC per unit Units of RAW PVC needed for production Add: Desired ending RAW PVC inventory Total RAW PVC units required Less: Beginning RAW PVC inventory RAW PVC materials purchase Cost per unit of RAW PVC Total cost of RAW PVC purchases 25% Note: Desired ending inventory of RA of next month's production needs Quarter 1 April January February March Units to be produced RESIN RESIN per unit Units of RESIN needed for production Add: Desired ending RESIN inventory TotalRESIN units required Less: Beginning RESIN inventory RESIN materials purchase Cost per unit of RESIN Total cost of RESIN purchases 25% Note: Desired ending inventory of RE of next month's production needs Direct Labor Budget Williams Company First Quarter February March Quarter 1 January Units to be produced Direct labor time (hours) per unit Total required direct labor hours Direct labor costs per hour Total direct labor costs PVC Unit Costs Williams Company First Quarter January-March Units per PVC pip Unit Price Cost per PVC pipe Cost Element Raw materials RAW PVC RESIN Direct labor Factory overhead (per direct labor hour) Unit cost per PVC pipe $0.00 Selling and Administrative Expense Budget Williams Company First Quarter February March Quarter 1 January Variable expenses Sales Selling and adminstrative expense percentage Total variable selling and administrative expense Fixed expenses Fixed selling and administrative expense Total selling and administrative expense Cash Budget Williams Company First Quarter January February March Beginning cash balance Add: Receipts Cash sales to customers Cash collected from accounts receivable Total available cash Less: Disursements Raw materials cash purchases Raw materials accounts payable purchases Direct labor Factory overhead Selling and administrative expense Income tax expense Total disbursements Excess (deficiency) of available cash over cash disbursements $ $ $ $ Budgeted Balance Sheet Williams Company First Quarter End of Januar End of Febraur End of March Quarter 1 Assets Cash Accounts receivable Finished goods inventory Raw materials inventory RAW PVC RESIN Land Building and equipment Less: Accummulated depreciation Total assets Liabilities & Stockholders' Equity Accounts payable Common Stock Retained earnings Total liabilities & Stockholders' Equity $0 $0 $0 Budgeted Income Statement Williams Company First Quarter January February March Quarter 1 Sales Cost of goods sold (units sold*unit cost) Gross profit Selling and administration expenses Income from operations Interest expense Income before income taxes Income tax expense Net income $0 $0 $0 $0 Notes: 1. 1400 finished PVC in inventory 12/31 balance sheet value of $64,400. Unit cost for these units $64,400/1400=$46. 2. COGS in January calculated 1400*$ 46 plus (7000-1400)*$ 46.4

Answer & Explanation Solved by verified expert

See Answer