D K 1 4 Project - Part 1 2 3 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 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 tob. You have until May 12" to get this done. View Menu DO POR Home Insert Page Layout Formulas Data Review 5 X Cut Garamond 12 CA A = General Copy Format BI U BE. A AQ Merge & Center Wrap - % pa Text D1 Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 B D E F H J K Assumptions for Wesleyan Technology Company (WTC) on preparing a budaet for 3rd Quarter. 2021 PS Budgeted Sales (in units) July 40,000 August 45,000 September 60,000 October 35,000 November 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 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, Task Info Input + Sum: Average- Count1 enu 9. Home Insert Page Layout Formulas Data Review View E X Cut Copy Format 001 3 Garamond - 12 ' ' General BIUBB A. Merge & Center - Wrap Text e fx Assumptions for Wesleyan Technology Company (WTO) on preparing a budget for 3rd Quarter, 2021 D E F G H 3 K D1 B June 30 Accounts Payable balance of $33,187.50 is to be paid in full in July. 0.1 (or 6 minutes) Direct Labor Part of an hour of direct labor to produce one unit of product 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 The minimum number of hours to be paid each month $15 3,000 $25 $75,000 $25,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 Manaufacturing Overhead 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 PS $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 Task info Input + Sum=0 Average 0 Count=1 Merge & Center - * Cut Copy Format Garamond BI U 99. A. Wra Te 01 Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter H B D Casn Buager 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 $268,017.00 it 5 6 7 8 79 30 31 Task Innut Data Formulas Review VE Excel - Project P11(1).xlsx + BOP 2 Home Insert Page Layout Cut Calibri - 11 ' ' CopyFormat BIUBBARE Genera TE IH O Merge & Center - Wrap Text @fx B D E F Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 onstruct the sales budget Quarter 3, 2021 July August September Total udgeted unit sales 40,000 45,000 60,000 145,000 elling price per unit $12 $12 $12 $12 Total sales $480,000 $540,000 $720,000 $1,740,000 Construct Expected Cash Collections Budget Quarter 3, 2021 August September July $108,000 $312,000 A/R-30Jun July August Sep 144000 $351,000 Total $108,000 $456,000 $513,000 $468,000 162000 468000 Total Cash Collections $420,000 $495,000 $630,000 $1,545,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 5,250 166,000 6,000 310,250 Task Info Input + Formulas Data Review a PR Home Insert Page Layout Cut Calibri - 11 ' Copy Format BIU.B.A.A.QE Gener III Merge & Center - Wrap Text B D E F 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 - $ July 40,750 10 407,500 4,725 412,225 4,075 408,150 $0.25 $102,038 Quarter 3, 2021 August September 47,250 56,250 10 10 472,500 562,500 5,625 3,275 478,125 565,775 4,725 5,625 473,400 560,150 $0.25 $0.25 $118,350 $140,038 Quarter 310,250 10 3,102,500 13,625 3,116,125 4,075 3,112,050 $0.25 $778,013 Construct Expected Cash Disbursements Budget Quarter 3, 2021 August September A/P - 30 Jun july august July $33,187.50 $56,120.63 45916.875 $65,092.50 Total $33,187.50 $102,037.50 $118,350.00 $77,020.63 sep 53257.5 77020.625 Total Cash Disbursements $89,308.13 $111,009.38 $130,278.13 $330,595.63 Construct Direct Labor Budget July Quarter 3, 2021 August September Total Inits of Production Task Info nput + MacBE he Formulas Review Insert Data Vie Home Page Layout enu General X Cut Copy Format Calibri - 11 SAATE BIU.E. 9 A. IULIM % Merge & Center - MI Wrap Text K53 @fx B D E F P Construct Direct Labor Budget Quarter 3, 2021 August September July 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 August September July 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 Quantity Cost Total Production costs per unit Direct Materials Task info Anipul lbs. + + Formulas Data Review V se Excel - Project Pt1(1).xlsx DO POR Home Insert Page Layout X Cut Calibri - 11 DA == Copy Format BIU BARE Geners lali Merge & Center - Wrap Text 53 @fx B D E F Construct Ending Finished Goods Inventory Budget Quantity Total Production costs per unit Direct Materials Direct Labor Manufacturing Overhead Quarter 3, 2021 Cost Ibs. hrs. 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 August September July 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 July Quarter 3, 2021 August September Total Beginning Cash Balance Add: Cash collections Total cash avallable Task Info Input Formulas Data Review nu DOPQ Home Insert Page Layout X Cut Calibri 11 TAAT CopyFormat BIUBBARE IM Gener WIIIII Merge & Center - - Wrap Text 53 @fx B Cash S & A Expenses D E F 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 Borrowing (inflow) Repayments (outflow) Interest (outflow) Total financing Ending Cash Balance Budgeted Income Statement Sales Cost of Goods Sold Gross Margin Task Info input X Cut Copy Format Calibri 11 A A BI UBB: 9 AO IIIIM I Merge & Center - Wrap Text 53 . @fx B Ending Cash Balance D E F 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 Raw materials inventory Finished goods inventory Land Equipment Total Assets LIABILITIES AND STOCKHOLDERS' EQUITY: Accounts Payable Notes Payable Task Info Input + Merge & Center Copy Format BI U3 9: A:@o23 K53 @fx B D E Sales Cost of Goods Sold Gross Margin Selling and Administrative expenses Operating Income Interest expense Net Income 3 2 Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: Cash Accounts receivable 3 4 15 86 47 Raw materials inventory Finished goods inventory Land Equipment Total Assets 48 49 50 51 52 53 154 155 LIABILITIES AND STOCKHOLDERS' EQUITY: Accounts Payable Notes Payable Common Stock Retained earnings Total Liabilities and Stockholders' Equity 156 157 158 Task Info nput +