Data needed for spreadsheet input:

(NOTE: Retained Earning is not 228,986.36. CORRECT RETAINED EARNING IS $ 268,017.00

Provided information:

Information needed:

Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 Budgeted Sales (in units) July August 40,000 45,000 September October November 60,000 35,000 20,000 Per Unit Selling Price $12.00 Cash Collections WTC sales are all on account with the following pattern of collection is: 65% Collected in the month of the sale 30% Collected in the month following the sale 5% Uncollectible June's sales were $360,000 Production Budget Ending inventory is to be equal to 15% of the following month's budgeted sales. Direct Materials 10 pounds of material is required per unit of product 10% of the following month's production for Materials on hand at month end $0.25 cost per pound of material Cash Disbursement for Materials Cash Disbursement for Materials WTC paid for purchases as follows: 55% is paid in the month the materials are purchased 45% is paid in the following month June 30 Accounts Payable balance of $33,187.50 is to be paid in full in July (or 6 minutes) Direct Labor Part of an hour of direct labor to produce one unit of product 0.1 WTC has a "no layoff" policy so all employees are paid for 40 hours of work each week The hourly rate per hour for regular time employees $15 The minimum number of hours to be paid each month 3,000 Manufacturing Overhead Manufacturing overhead is applied to units of product based on direct labor hours The viable overhead rate per direct hour $25 Fixed Manufacturing Overhead per month $75,000 Non-cash costs included in Fixed Manaufacturing Overhead $25,000 Selling and Administrative Expenses WTC has both fixed and variable selling and administrative expenses Rate per unit for Variable Selling and Administrative expenses Fixed Selling and Administrative expenses per month Non-cash costs included in Fixed Selling and Administrative expenses $0.25 $75,000 $15,000 Cash Budget Maintain a minimum month end cash balance of $40,000 Maintains a 15% open line of credit for $125,000 Borrowing happens on the first day of the month Repayments are made on the last day of the month Cash dividend paid in August Equipment purchases made in Q3 2021 Equipment purchased with cash in July Equipment purchased with cash in September June 30 ending cash balance was $40,000 $60,000 $55,000 $150,000 Budgeted Financial Staments WTC Balance Sheet as of June 30 showed the following balances: Accounts Receivable $137,627.00 Equipment $195,000.00 $50,000.00 Common Stock $300,000.00 Retained Earnings $228,986.36 Land Construct the sales budget Budgeted unit sales Selling price per unit Total sales July 40,000 $12 $480,000 Quarter 3, 2021 August September 45,000 60,000 $12 $12 $540,000 $720,000 L Total 145,000 $12 $1,740,000 Construct Expected Cash Collections Budget Quarter 3, 2021 August September A/R-30Jun (360,000*.30) 65% Collected in the month of the sale 30%. Collected in the month following the sale $ $ July 108,000 312,000 Total $ 108,000 $1,131,000 $ 300,000 $ $ 351,000 $ 144,000$ 468,000 162,000 Total Cash Collections $ 420,000 $ 495,000 $ 630,000 $1,545,000 Construct Production Budget Budgeted Sales Add: Desired Endling Inventory Total Needs Less: Beginning Inventory Required Production 40,000 6,750 46,750 6,000 40,750 Quarter 3, 2021 August September 45,000 60,000 9,000 5,250 54,000 65,250 6,750 9,000 47,250 56,250 Quarter 145,000 21,000 166,000 21,750 144,250 Construct Direct Materials Budget July 40,750 Production Materials per units (lbs.) Production Needs Add: Desired ending inventory Total Needed Less: Beginning Inventory Materials to be purchased Cost per lbs. Materials to be purchased - $ 407,500 47,250 454,750 40,750 414,000 $0.25 $103,500 Quarter 3, 2021 August September 47,250 56,250 10 10 472,500 562,500 56,250 32,750 528,750 595,250 47,250 56,250 481,500 539,000 $0.25 $0.25 $120,375 $134,750 Quarter 144,250 10 1,442,500 136,250 1,578,750 144,250 1,434,500 $0.25 $358,625 Construct Expected Cash Disbursements Budget Quarter 3, 2021 August September A/P-30Jun 55% is paid in the month materials are purchased 45% is paid in the following month July $33,187.50 $56,925.00 Total $33,187.50 $197,243.75 $100,743.75 $66,206.25 $46,575.00 $74,112.50 $54,168.75 - Total Cash Disbursements $90,112.50 $112,781.25 $128,281.25 $331.175.00 Construct Direct Labor Budget . L Units of Production Direct Labor per unit Labor hours required Guaranteed Hours Labor Hours Paid Hourly Rate Total Direct Labor Costs July 40,750 0.10 4,075 3,000 4,075 $15 $61,125 Quarter 3, 2021 August September 47,250 56,250 0.10 0.10 4,725 5,625 3,000 3,000 4,725 5,625 $15 $15 $70,875 $84,375 Total 144,250 0.10 14,425 3,000 14,425 $15 $216,375 Construct Manufacturing Overhead Budget Budgeted DLH Variable MOH rate Variable MOH costs Fixed MOH costs Total MOH costs Quarter 3, 2021 July August September 4,075 4,725 5,625 $25 $25 $25 $101,875 $118,125 $140,625 $75,000 $75,000 $75,000 $176,875 $193,125 $215,625 Predetermined Overhead Rate (POHR) $176,875 $193,125 $215,625 ($25,000 $25,000) $25,000 $ 151,875 $168,125 $190,625 Total 14,425 $25 $360,625 $75,000 $435,625 $30.20 $435,625 $75,000 $360,625 Total MOH costs Less: noncash costs Cash disbursements for MOH Construct Ending Finished Goods Inventory Budget Quarter 3, 2021 Quantity Cost 10 Production costs per unit Direct Materials Direct Labor Manufacturing Overhead 0.10 lbs. $0.25 hrs. $15.00 hrs. $30.20 Product Cost per Unit Total $2.50 $1.50 $3.02 $7.02 0.1 Budgeted Finished Goods Inventory Ending inventory in units 5,250 Ending inventory in units Unit product cost Ending finished goods inventory 5,250 $7.02 $36,855 Construct Selling and Administrative Expense Budget Budgeted Sales | Variable S & A rate Variable expenses Fixed S & A costs Total S & A costs Less: noncash costs Cash S & A Expenses July 40,000 $0.25 $10,000 $75,000 $85,000 $15,000) $70,000 Quarter 3, 2021 August September 45,000 60,000 $0.25 $0.25 $11,250 $15,000 $75,000 $75,000 $86,250 $90,000 ($15,000) ($15,000) $71,250 $ 75,000 Total 145,000 $0.25 $36,250 $225,000 $261.250 ($45,000) $216,250 Construct Cash Budget Quarter 3, 2021 July August September Total $ 40,000.00 $ 40,000.00 $ 43,653.44 $ 40,000.00 $ 420,000.00 $ 495,000.00 $630,000.00 $1,545,000.00 $ 460,000.00 $ 535,000.00 $ 673,653.44 $1,585,000.00 $ 90,112.50 $ 61,125.00 $ 151,875.00 $ 70,000.00 $ 55,000.00 Beginning Cash Balance Add: Cash collections Total cash available Less: Cash disbursements Materials Direct labor Manufacturing O/H Selling and Admin Equipment purchase Dividend Total disbursements Excess Deficiency) Financing Borrowing Repayments Interest Total financing Ending Cash Balance $ 112,781.25 $ 128,281.25 $ 331,175.00 $ 70,875.00 $ 84,375.00 $ 216,375.00 $ 168,125.00 $ 190,625.00 $ 510,625.00 $ 71,250.00 $ 75,000.00 $ 216,250.00 $ 150,000.00 $ 205,000.00 $ 60,000.00 $ 60,000.00 $ 483,031.25 $ 628,281.25 $1,539,425.00 $ 51,968.75 / $ 45,372.19 $ 45,575.00 $ 428,112.50 $ 31,887.50 $ 8,112.50 $ (8,112.50 $ (202.81) $ (8,315.31) $ 43,653.44 1$ $ $ $ $ 8,112.50 (8,112.50 (202.81) (202.81) 45,372.19 $ 8,112.50 $ 40,000.00 $ 45,372.19 Budgeted Income Statement Sales Cost of Goods Sold Gross Margin Selling and Administrative expenses Operating Income Interest expense Net Income Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: Cash Accounts receivable WTC Balance Sheet as of June 30 showed the following balances: Accounts Receit $137,627.00 Equipment Land $50,000.00 Common Stock $300,000.00 Retained Earnin $268,017.00 Raw materials inventory Finished goods inventory Land Equipment Total Assets LIABILITIES AND STOCKHOLDERS' EQUITY: Accounts Payable Notes Payable Common Stock Retained eamings Total Liabilities and Stockholders' Equity