You are the accountant at Wesleyan Technology Company (WTC) in the Fall of 2020. Your boss, the controller, has asked you to take another pass at the 3rd Quarter Budget for 2021 as she is not sure the numbers are correct. She sends you the attached Excel file, providing the necessary information to construct the budget from scratch. Your job is to take the information provided on the Info tab in Excel file and put together the Budget for 3Q, 2021 on the Input tab.

Just need cash budget, income statement & balance sheet.

A x V fx U AB c D E F G 0.1 or 6 minutes) Direct Labor Part of an hour of direct labor to produce one unit of product WTC has a "no layoft" policy so all employees are paid for 40 hours of work each week The hourly rate per hour for regular time employees The minimum number of hours to be paid each month $15 3,000 Manufacturing Overhead Manufacturing overhead is applied to units of product based on direct labor hours The viable overhead rate per direct hour Fixed Manufacturing Overhead per month Non-cash costs included in Fixed Manufacturing Overhead $25 $75,000 $25,000 2 Selling and Administrantive Expenses WTC has both fixed 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 1 2 3 4. 5 66 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 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 Budgeted Financial Staments WTC Balance Sheet as of June 30 showed the following balances: Accounts Receivable $137.627.00 Equipment $195,000.00 Land $50,000.00 Common Stock $300,000.00 Retained Earnings $240,567.00 B D E F G Construct Cash Budget Quarter 3, 2021 August September July Total 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 Bomowing (inflow) Repayments (outflow) Interest (outflow) Total financing Ending Cash Balance Budgeted Income Statement Sales (145,000 units x $12) Cost of Goods Sold (145,000 units x $8.06) Gross Margin Selling and Administrative expenses Operating Income Interest expense Net Income Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: Cash Accounts receivable Raw materials inventory Finished goods inventory 163 fx A C D E F 129 130 131 132 133 134 Budgeted Income Statement Sales (145,000 units x $12) Cost of Goods Sold (145,000 units x $8.06) Gross Margin Selling and Administrative expenses Operating Income Interest expense Net Income 135 136 137 138 139 140 141 Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: 142 143 Cash 144 Accounts receivable 145 146 147 Raw materials inventory Finished goods inventory Land Equipment Total Assets 148 149 150 151 152 153 LIABILITIES AND STOCKHOLDERS' EQUITY: Accounts Payable Notes Payable Common Stock Retained eaming Total Liabilities and Stockholders' Equity 154 155 156 157 158 159 160 161 162 163 164 165 G C D E F Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 Construct the sales budget Quarter 3, 2021 August September Total Budgeted unit sales 40,000 45,000 60,000 145,000 Selling price per unit $12 $12 $12 $12 Total sales $480,000 $540,000 5720,000 $1,740,000 Construct Expected Cash Collections Budget Quarter 3, 2021 August September July $108,000 $312,000 Total $108,000 $312,000 $144,000 A/R - 30 Jun July Sales - 65% July Sales - 30% August Sales - 65% August Sales - 30% September - 65% Total Cash Collections $144,000 $351,000 $162,000 $468,000 $630,000 $162,000 $468,000 $1,545,000 $420,000 $495,000 Construct Production Budget Budgeted Sales Add: Desired Ending Inventory Total Needs Less: Beginning Inventory Required Production July 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 +3,250 150,250 6,000 144,250 Construct Direct Materials Budget 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 - S 40,750 10 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 32,750 1,475,250 40,750 1,434,500 $0.25 $358,625 Construct Expected Cash Disbursements Budget B D E F G Construct Expected Cash Disbursements Budget Quarter 3, 2021 August September $33,187.50 $56,925.00 A/P-30Jun July Purchases - 55% July Purchases - 45% August Purchases - 55% August Purchases - 45% September Purchases - 55% Total Cash Disbursements $46,575.00 $66,206.25 Total $33,187.50 $56,925.00 $46,575.00 $66,206.25 $54,168.75 $74,112.50 $331,175.00 $54,168.75 $74,112.50 $128,281.25 $90,112.50 $112,781.25 Construct Direct Labor Budget Quarter 3, 2021 August September July Total per unit Units of Production Direct Labor Labor hours required Gaarames Heur Labor Hours Paid Hourly Rate Total Direct Labor Costs Construct Manufacturing Overhead Budget Quarter 3, 2021 August September July Total Budested DLH Variable MOH rate Variable MOH costs Fixed MOH costs Total MOH costs 3 Predetermined Overhead Rate (POHR) m Total MOH costs Less: noncash costs Cash disbursements for MOH 1 N 3 Construct Ending Finished Goods Inventory Budger 35 36 37 38 Quantity Total Production costs per unit Direct Materials Direct Labor Manufacturing Overhead Qua 15, 2021 Cost lbs. hrs. hrs. Product Cost per Unit 90 91 Budgeted Finished Goods Inventory 7 E F G Construct Direct Labor Budget 9 0 51 52 53 Units of Production Direct Labor per unit Labor hours required Guaranteed Hours Labor Hours Paid Hourly Rate Total Direct Labor Costs 54 55 166 67 Quarter 3, 2021 August September 47,250 56,250 0.10 0.1 4,725 5,625 3,000 3,000 4,725 5,625 $15 $15 $70,875 $84,378 July 40,750 0.10 4,075 3,000 4,075 $15 $61,125 Total 144,250 0.10 14,425 9,000 14,425 $15 $216,375 68 69 70 71 72 73 74 75 Construct Manufacturing Overhead Budget Quarter 3, 2021 July August September Budgeted DLH 4,075 4,725 5,625 Variable MOH rate $25 $25 $25 Variable MOH costs $101,875 $118,125 $140,625 Fixed MOH costs $75,000 $75,000 $75,000 Total MOH costs $176,875 $193,125 $215,625 Predetermined Overhead Rate (POHR) Total MOH costs $176,875 $193,125 $215,625 Less: noncash costs $25,000 $25,000 $25,000 Cash disbursements for MOH $161,875 $ 168,125 $190,625 76 77 Total 14,425 $25 $360,625 $225,000 $585,625 $40.60 $585,625 $75,000 $510,625 78 79 80 81 82 83 B D E 3 F 4 5 6 -7 58 Construct Ending Finished Goods Inventory Budget Quarter 3, 2021 Production costs per unit Quantity Cost Direct Materials 10 lbs. $0.25 Direct Labor 0.1 hrs. $25.00 Manufacturing Overhead 0.1 hrs. Product Cost per Unit Budgeted Finished Goods Inventory Ending inventory in units Unit product cost Ending finished goods inventory Total $2.50 $2.50 $40.60 $45.60 39 20 21 32 93 5,250 $45.60 $239,389 94 95 96 97 98 99 100 Construct Selling and Administrative Expense Budget Quarter 3, 2021 July August September Budgeted Sales 40,000 45,000 60,000 Variable S & A rate $0.25 $0.25 $0.25 Variable expenses $10,000 $11,250 $15,000 Fixed S & A costs $75,000 $75,000 $75,000 Total S & A costs $85,000 $86,250 $90,000 Less: noncash costs $15,000 $15,000 $15,000 Cash S & A Expenses $70,000 $71,250 $78,000 101 102 103 Total 145,000 $0.25 $36,250 $225,000 $261,250 $45,000 $216,250 104 105 106 107 108 Construct Cash Budget