Master Budget Project Enterprise Accounting Master Budget Project: The focus of this project is to create a master budget for the Williams Company based on the company's industry outlook, recent company outcomes, and the company's business rules. You will create a master budget for Williams Company, including their cash budget and the proforma financial statements. Master Budget Project Details 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 31st Balance Sheet on December 31st 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 February March April May 7000 8000 10000 8000 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 month's forecasted sales. Williams Company's finished goods inventory on December 31st 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 Raw PVC Resin Units 5 3 4. 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 month's production needs. William Company's December 31st 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). 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 bf $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. (Please use this template as the format for excel!) Below is a context for the answers as well as the spreadsheets that need filled out from Unit- Cost to the Budgeted Income Statement. 1. Unit-Cost Context For Answers Three-Month Master Budget williams Company Balance Sheet December 3 PVC Unit Costs Williams Company First Quarter Cash Accounts receivable Raw materials Finished goods Land Plant and equipment Le cumulated depreciation Total assests $41,700 192.000 102.240 64,400 50.000 $500,000 112,000 January-March Units per PVC pipe Unit Price Cost per PVC pipe 388,000 $838,340 $40,000 Accounts payable to suppliers Commen Stock Retained Earnings Total liabilities and equity $100,000 698.340 Cost Element Raw materials RAW PVC RESIN Direct labor Factory overhead (per direct labor hour) Unit cost per PVC pipe 798,340 $838,340 Units $0.00 PVC SALES Budgeted PVC Sales (units) January February March April May 7,000 8.000 10,000 000 7,000 PVC Selling Price $60.00 * Sales Collected Month Sold 40% Sales Collected Next Month 509 1,400 2096 2. Overhead FINISHED GOODS INVENTORY: PVC Beginning Inventory Endingwentory of Next Month's Sales PRODUCTION OF PVC ONE UNIT OF PVC PRODUCED USING PRICES OF RAW MATERIALS AND LABOR RAW PVC RESIN DIRECT LABOR HOURS 5 3 0.5 $4.00 $2.80 S24 Overhead Budget Williams Company First Quarter RAW MATERIALS Beginning inventory Levels Ending Invertory of Next Month's Production Needs % Purchases Paid in Month of Purcha Purchases Paid in Month After Purchase 18,000 10,800 25.00% 25.00 M. 20.00 30.00 30.000 100.00 0.00 % Quarter 1 FACTORY OVERHEAD Applied Per Direct Labor How $12.00 January February March Direct labor hours Applied factory overhead rate Factory overhead Less: included plant and equipment depreciation Total factory overhead SELLING AND ADMINISTRATION Total $10.000,00 per month plus 10.00% of sales PLANT AND EQUIPMENT Depreciates Evenly included in Factory Overhead at $12000 per year Monthly Depreciation $1,000 3. Selling 5. Budgeted Balance Sheet Selling and Administrative Expense Budget Williams Company First Quarter Budgeted Balance Sheet Williams Company First Quarter End of January End of Febraury End of March Quarter 1 January February March Quarter 1 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 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 4. Cash Total liabilities & Stockholders' Equity $0 $0 $0 Cash Budget Williams Company First Quarter January February March 6. Budgeted Income Statement Budgeted Income Statement Williams Company First Quarter January February March Quarter 1 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 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 $ $ - $ $o $o $o $o 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