Homework help needed. I provided images of the questions and what needs to be filled.
A B C D E F G H Project - Part 1 L K Assignment 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 3"" 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. You have until May 12" to get this done. Grading The Project - Part 1 is worth a total of 200 points. 100 points for the correct answers/ numbers throughout the budget. 100 points based on utilizing formulas/ functions in as many cells as possible. 0 - W NJ66 X V fx A B C D E F G H J K L M 40,000 15,000 60,000 35,000 20,000 Per Unit Selling Price - $12.00 Cash Collections WTC sales are all on account with the following patter 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 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. Direct Labor Part of an hour of direct labor to produce one unit of product 0.1 ( or 6 minutes) 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,0DO Non-cash costs included in Fixed Manaufacturing Overhead $25,000 2 Selling and Administrative Expenses WTC has both fixed and variable selling and administrative expenses Rate per unit for Variable Selling and Administrative expenses $0.25 Fixed Selling and Administrative expenses per month $75,000 Non-cash costs included in Fixed Selling and Administrative expenses $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 $60,000 Equipment purchases made in Q3 2021 Equipment purchased with cash in July $55,000 Equipment purchased with cash in September $150,000 June 30 ending cash balance was $40,000 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.00A B C D E F G H I Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 Construct the sales budget Quarter 3, 2021 July August September Total Budgeted unit sales Seling price per unit Total sales Construct Expected Cash Collections Budget Quarter 3, 202 July August September A/R - 30Jun Total July Sales - 65% July Sales - 30% August Sales - 65% August Sales - 30% September - 65% Total Cash Collections Construct Production Budget Quarter 3, 2021 July August Budgeted Sales September Quarter Add: Desired Ending Inventory Total Needs Less: Beginning Inventory Required Production Construct Direct Materials Budget Quarter 3, 2021 July August September Quarter Production Materials per units (Ibs.) Production Needs Add: Desired ending inventory Total Needed Less: Beginning Inventory Materials to be purchased Cost per lbs. Materials to be purchased - $ Construct Expected Cash Disbursements Budget Quarter 3, 202 July Augus September A/P - 30Jun Total July Purchases - 55% July Purchases - 45% August Purchases - 55% August Purchases - 45% September Purchases - 55% Total Cash Disbursements Construct Direct Labor Budget Quarter 3, 2021 July August September Total Units of Production Direct Labor per unit Labor hours required Guaranteed Hours Labor Hours Paid Hourly Rate Total Direct Labor CostsConstruct Manufacturing Overhead Budget Quarter 3, 2021 July August September Total Budgeted DLH Variable MOH rate Variable MOH costs Fixed MOH costs Total MOH costs Predetermined Overhead Rate (POHR) Total MOH costs Less: noncash costs Cash disbursements for MOH Construct Ending Finished Goods Inventory Budget Quarter 3, 2021 Production costs per unit Quantity Direct Materials Cost Total Direct Labor Ibs. hrs . Manufacturing Overhead hrs. Product Cost per Unit Budgeted Finished Goods Inventory Ending inventory in units Unit product cost Ending finished goods inventory Construct Selling and Administrative Expense Budget Quarter 3, 2021 July August September Total Budgeted Sales Variable S & A rate Variable expenses Fixed S & A costs Total S & A costs Less: noncash costs Cash S & A Expenses Construct Cash Budget Quarter 3, 2021 August September 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: Borrowing (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 Income120 121 Total disbursements 122 Excess (Deficiency 123 Financing: 124 Borrowing (inflow) 125 Repayments (outflow) 126 Interest (outflow) 127 Total financing 128 Ending Cash Balance 129 130 131 Budgeted Income Statement 132 Sales (145,000 units x $12) 133 Cost of Goods Sold (145,000 units x $8.06) 134 Gross Margin 135 Selling and Administrative expenses 136 Operating Income 137 Interest expense 138 Net Income 139 140 Construct Budgeted Income Statement 141 Budgeted Balance Sheet 142 ASSETS: 143 Cash 144 Accounts receivable 145 Raw materials inventory 146 Finished goods inventory 147 Land 148 Equipment 149 Total Assets 150 151 LIABILITIES AND STOCKHOLDERS' EQUITY: 152 Accounts Payable 153 Notes Payable 154 Common Stock 155 Retained earnings 156 Total Liabilities and Stockholders' Equity 157 158 159
