Autosa" Oom B Excel - Project Pt1 (1) O Search Megan Turner MI Ga 0 File Draw Consseents Formulas M ZS View W ZC F Page Layout P ~ A A JI w 12 Home Insert N X Garamond 09 BIU Data Review A R a Wrap Text Help Y General Insert TI 4 Delete Paste Ideas CA A Merge & Center $ % 488 Conditional Format as Cell Formatting Table Styles Format Sort & Find & Filter Select Clipboard Font Alignment Number Styles Cells Editing Ideas A E7 fx B C D G H K L - M N 0 P 1 E F 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 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 30, 2021 on the Input lab. You have until May 12 to get this done. 4 5 6 7 Grading The Project Part 1 is worth a total of 200 points. 100 points for the correct answersumbers throughout the budget. 100 points based on utilizing formulas/functions in as many cells as possible. 8 9 10 11 12 Task info Info Input al 100% I Type here to search O Et 839 G 3:12 PM 7/22/2020 AutoSave Off n Excel - Project Pt1 (1) File Home Insert Draw Page Layout Formulas Data Calibri - 11 ' ' alle Wra Paste BI U v . Mer Clipboard Font 21 Alignment W2 fi C K D F G H Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 1 2 3 4 Budgeted Sales (in units) July August 40,000 45,000 September 60,000 October 35,000 November 20,000 5 6 7 Per Unit Selling Price - $12.00 8 9 10 11 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 12 13 14 15 June's sales were $360,000 16 17 Production Budget Ending inventory is to be equal to 15% of the following month's budgeted sales. 18 19 20 21 Direct Materials 10 pounds of material is required per unit of product 10% of the following month's production for Materials on $0.25 cost per pound of material nth end 23 24 25 Cash Disbursement for Materials Task Input Info 1 Type here to search AutoSave Off n Excel - Project Pt1 (1) SE T File Home Insert Draw Page Layout Formulas Data Review Calibri - 11 ' ' ale Wrap Text | || Paste BI U v . Merge & Cente Clipboard Font Is Alignment W2 fi H 1 K L D E F G 25 Cash Disbursement for Materials 26 WTC paid for purchases as follows: 27 55% is paid in the month the materials are purchased 28 45% is paid in the following month 29 30 June 30 Accounts Payable balance of $33,187.50 is to be paid in full in July. 31 32 33 0.1 or 6 minutes) 34 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 hously rate per hour for regular time employees The minimum number of hours to be paid each month 35 36 37 $15 3,000 38 39 40 41 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 $25 $75,000 $25,000 42 43 Pg2 44 45 46 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 47 $0.25 $75,000 $15,000 48 49 50 Cash Budget Task Info Input + I Type here to search te AutoSave Off n Excel - Project Pt1 (1) File Home Insert Draw Page Layout Formulas Data Reu Calibri - 11 ' ' ale Wrap T Paste BI U v . Merge Clipboard Font 21 Alignment W2 fi E H 1 C D F G 49 50 Cash Budget 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 56 Equipment purchases made in Q3 2021 57 Equipment purchased with cash in July 58 Equipment purchased with cash in September 59 June 30 endling cash balance was $40,000 60 $60,000 $55,000 $150,000 61 62 63 64 65 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 66 67 68 69 70 71 72 73 74 Task Info Input H Type here to search AutoSave Off n Excel - Project Pt1 (1) File Home Insert Draw Page Layout Formulas Data Revie Calibri - 11 ' ' ale Wrap Text III lili Paste BI U v . Merge & Clipboard Font 27 Alignment F106 fi B D E 107 108 Construct Cash Budget 109 110 Quarter 3, 2021 August September July Total 111 112 113 114 115 116 117 118 119 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 120 121 122 123 124 125 126 127 128 120 Task Info Input + Type here to search AutoSave Off n Excel - Project Pt1 (1) T File Home Insert Draw Page Layout Formulas Data Calibri - 11 ' ' abov Paste BI U v . EN Clipboard Font KI Alignment F106 fi B D E F 131 Budgeted Income Statement 132 Sales 133 Cost of Goods Sold 134 135 Gross Margin Selling and Administrative expenses Operating Income 136 137 Interest expense 138 Net Income 139 140 141 Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: 142 143 Cash 144 Accounts receivable 145 Raw materials inventory 146 Finished goods inventory 147 Land 148 Equipment Total Assets 149 1 col Task Info Input Type here to search AutoSave Off n Excel - Project Pt1 (1) T File Home Insert Draw Page Layout Formulas Dat Calibri - 11 ' ' v Paste BI U v . Clipboard Font KI Alignme F106 fi A D E 140 Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: 141 142 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: Accounts Payable 152 153 Notes Payable 154 Common Stock 155 Retained earnings Total Liabilities and Stockholders' Equity 156 157 158 159 160 Task Info Input 1 Type here to search Autosa" Oom B Excel - Project Pt1 (1) O Search Megan Turner MI Ga 0 File Draw Consseents Formulas M ZS View W ZC F Page Layout P ~ A A JI w 12 Home Insert N X Garamond 09 BIU Data Review A R a Wrap Text Help Y General Insert TI 4 Delete Paste Ideas CA A Merge & Center $ % 488 Conditional Format as Cell Formatting Table Styles Format Sort & Find & Filter Select Clipboard Font Alignment Number Styles Cells Editing Ideas A E7 fx B C D G H K L - M N 0 P 1 E F 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 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 30, 2021 on the Input lab. You have until May 12 to get this done. 4 5 6 7 Grading The Project Part 1 is worth a total of 200 points. 100 points for the correct answersumbers throughout the budget. 100 points based on utilizing formulas/functions in as many cells as possible. 8 9 10 11 12 Task info Info Input al 100% I Type here to search O Et 839 G 3:12 PM 7/22/2020 AutoSave Off n Excel - Project Pt1 (1) File Home Insert Draw Page Layout Formulas Data Calibri - 11 ' ' alle Wra Paste BI U v . Mer Clipboard Font 21 Alignment W2 fi C K D F G H Assumptions for Wesleyan Technology Company (WTC) on preparing a budget for 3rd Quarter, 2021 1 2 3 4 Budgeted Sales (in units) July August 40,000 45,000 September 60,000 October 35,000 November 20,000 5 6 7 Per Unit Selling Price - $12.00 8 9 10 11 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 12 13 14 15 June's sales were $360,000 16 17 Production Budget Ending inventory is to be equal to 15% of the following month's budgeted sales. 18 19 20 21 Direct Materials 10 pounds of material is required per unit of product 10% of the following month's production for Materials on $0.25 cost per pound of material nth end 23 24 25 Cash Disbursement for Materials Task Input Info 1 Type here to search AutoSave Off n Excel - Project Pt1 (1) SE T File Home Insert Draw Page Layout Formulas Data Review Calibri - 11 ' ' ale Wrap Text | || Paste BI U v . Merge & Cente Clipboard Font Is Alignment W2 fi H 1 K L D E F G 25 Cash Disbursement for Materials 26 WTC paid for purchases as follows: 27 55% is paid in the month the materials are purchased 28 45% is paid in the following month 29 30 June 30 Accounts Payable balance of $33,187.50 is to be paid in full in July. 31 32 33 0.1 or 6 minutes) 34 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 hously rate per hour for regular time employees The minimum number of hours to be paid each month 35 36 37 $15 3,000 38 39 40 41 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 $25 $75,000 $25,000 42 43 Pg2 44 45 46 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 47 $0.25 $75,000 $15,000 48 49 50 Cash Budget Task Info Input + I Type here to search te AutoSave Off n Excel - Project Pt1 (1) File Home Insert Draw Page Layout Formulas Data Reu Calibri - 11 ' ' ale Wrap T Paste BI U v . Merge Clipboard Font 21 Alignment W2 fi E H 1 C D F G 49 50 Cash Budget 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 56 Equipment purchases made in Q3 2021 57 Equipment purchased with cash in July 58 Equipment purchased with cash in September 59 June 30 endling cash balance was $40,000 60 $60,000 $55,000 $150,000 61 62 63 64 65 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 66 67 68 69 70 71 72 73 74 Task Info Input H Type here to search AutoSave Off n Excel - Project Pt1 (1) File Home Insert Draw Page Layout Formulas Data Revie Calibri - 11 ' ' ale Wrap Text III lili Paste BI U v . Merge & Clipboard Font 27 Alignment F106 fi B D E 107 108 Construct Cash Budget 109 110 Quarter 3, 2021 August September July Total 111 112 113 114 115 116 117 118 119 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 120 121 122 123 124 125 126 127 128 120 Task Info Input + Type here to search AutoSave Off n Excel - Project Pt1 (1) T File Home Insert Draw Page Layout Formulas Data Calibri - 11 ' ' abov Paste BI U v . EN Clipboard Font KI Alignment F106 fi B D E F 131 Budgeted Income Statement 132 Sales 133 Cost of Goods Sold 134 135 Gross Margin Selling and Administrative expenses Operating Income 136 137 Interest expense 138 Net Income 139 140 141 Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: 142 143 Cash 144 Accounts receivable 145 Raw materials inventory 146 Finished goods inventory 147 Land 148 Equipment Total Assets 149 1 col Task Info Input Type here to search AutoSave Off n Excel - Project Pt1 (1) T File Home Insert Draw Page Layout Formulas Dat Calibri - 11 ' ' v Paste BI U v . Clipboard Font KI Alignme F106 fi A D E 140 Construct Budgeted Income Statement Budgeted Balance Sheet ASSETS: 141 142 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: Accounts Payable 152 153 Notes Payable 154 Common Stock 155 Retained earnings Total Liabilities and Stockholders' Equity 156 157 158 159 160 Task Info Input 1 Type here to search