What would be the way to solve this problem?

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 NAssumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 Budgeted Sales (in units) July August September October November 40,000 45,000 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. 9 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 UT The hourly rate per hour for regular time employees $15 The minimum number of hours to be paid each month 3,00036 The minimum number of hours to be paid each month 3,000 37 38 Manufactumiglhmaead 39 Manufacturing overhead is applied 130 units of product based on direct labor hours 40 The viable overhead rate per direct hour $25 41 Fixed Manufacturing Overhead per month $75,000 42 Noncash costs included in Fixed Manaufacturing Overhead $25,000 43 44 Wm 45 WTC has both fixed and variable selling and administrative expenses 45 Rate per unit for Variable Selling and Administrative expenses $0.25 47 Fixed Selling and Administrative expenses per month $75,000 48 Noncash costs included in Fixed Selling and Administrative expenses $15,000 49 5o Casthdger 51 Maintain a minimum month end cash balance of $40,000 52 Maintains a 15% open line of credit for $125,000 53 Borrowing happens on the first day of the month 54 Repayments are made on the last day of the month 55 Cash dividend paid in August $60,000 56 Equipment purchases made in Q3 2021 57 Equipment purchased with cash in July $55,000 58 Equipment purchased with cash in September $150,000 59 June 30 ending cash balance was $40,000 50 51 62 Wm 63 WTC Balance Sheet as of June 30 showed the following balances: 54 Accounts Receivable $137,627.00 55 Equipment $195,000.00 56 . Land $50,000.00 I II 57 Common Stock $300,000.00 58 Retained Earnings $240,567.00 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, 2021 July August September Tota A/P - 30Jun July Purchases - 55% July Purchases - 45% August Pur 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 Costs Construct Manufacturing Overhead Budget Quarter 3, 2021 July August September Total Budgeted DLH Variable MOH rate16 Fixed M OH costs 77 Total MOH costs 78 Predetermined Overhead Rate (POHR) 79 Total MOH costs 80 Less: noncash costs 81 Cash disbursements for MOH 82 83 84 Construct Ending Finished Goods Inventory Budget 85 Quarter 3, 2021 86 Production costs per unit Quantity Cost Total 87 Direct Materials 1bs. 88 Direct Labor mrs. 89 Manufacturing Overhead hrs. 90 Product Cost per Unit 91 Budgeted Finished Goods Inventory 92 Ending inventory in units 93 Unit product cost 94 Ending finished goods inventory 95 96 Construct Selling and Administrative Expense Budget 97 Quarter 3, 2021 98 July August September Total 99 Budgeted Sales 100 Variable S & A rate 101 Variable expenses 102 Fixed S & A costs 103 Total S & A costs 104 Less: noncash costs 105 Cash S & A Expenses 106 107 108 Construct Cash Budget 109 Quarter 3, 2021 110 July August September Total Beginning Cash Balance8 Net Income Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: Cash Accounts receivable Raw materials inventory Finished goods inventory Land Equipment Total Assets LIABILITIES AND STOCKHOLDERS' EQUITY: Accounts Payable Notes Payable Common Stock Retained earnings Total Liabilities and Stockholders' Equity