I need cash budget, income statement and balance sheet done using this file.

Lexie's Wool Sweaters Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Lexie's Wool Sweaters is a small business that makes 100% wool sweaters to sell. The information below pertains to the company's budgeting process during their busiest time of year. Lexie's uses variable costing. Each student must complete this assignment individually. . . . . . . . . . . . . . . . . . . Sales & Collections Each sweater is considered a unit of finished good which is sold for $100.00. Budgeted sales in units (sweaters) are as follows: October 2020 30,000 sweaters November 2020 34,000 sweaters December 2020 55,000 sweaters January 2021 47,000 sweaters February 2021 32,000 sweaters 30% of the company's sales are cash sales which are collected immediately. The remaining 50% of total sales are collected in the month of sale and the other 20% are collected in the month following the sale. Inventory Policy The company desires to have very little finished goods inventory on hand. At the end of each month, ending Inventory is 3% of the following month's budgeted sales in units. On September 30, the company had 1,500 units on hand. Product Input Expenses Direct Materials Each sweater requires 4 yards of wool. The cost per yard and other materials used is $3.50. Lexle's desires to have materials on hand at the end of each month equal to 7% of the following month's production needs. On September 30, the company had 8,265.60 yards of wool on hand. The company pays 85% of the month's material purchases in the month of purchase and delays the payment for the other 15% of the month's material purchases until the month following the purchase. Direct Labor The process to make each sweater requires 2 workers to contribute .5 hours of labor time. The workers are currently being paid $15.00/hour. Wages are paid in the month incurred. Manufacturing Overhead Variable manufacturing overhead is $11.75 per sweater. Fixed manufacturing overhead is $30,200 per month including $10,250 in depreciation that is not a current cash outflow. All cash disbursements for manufacturing overhead are paid in the month incurred. See below for changes to fixed overhead after October. Selling & Administrative Expenses nd administrative expenses is $7.37 per sweater sold. Fixed selling and administrative expense is $23,900 per month including $10,750 in depreciation that is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the month incurred. Factory Update & Cash Flow Lexle's borrowed $300,000 on Oct. 1, 2020, to prepare for a factory update. The full $300,000 was repaid on Nov. 30, 2020, but Lexle's paid two interest payments of $4,500 for the borrowings. The factory update cost $400,500 which was paid on Oct. 31, 2020. This addition to depreciable PP&E caused the fixed manufacturing overhead to raise to $30,750 per month in November with $15,750 being non-cash. 3rd Quarter Balance Sheet Lexie's Wool Sweaters Balance Sheet As of September 30, 2020 ASSETS LIABILITIES & EQUITIES Cash $ 150,000.00 Accounts Payable $ 14,500.00 Accounts Receivable $ 75,000.00 Notes Payable Raw Materials Inventory $ 28,929.60 Interest Payable Finished Goods Inventory $ 61,125.00 TOTAL LIABILITIES $ 14,500.00 PP&E, net $ 487,000.00 Retained Earnings $ 787,554.60 TOTAL ASSETS $ 802,054.60 TOTAL LIABILITIES & EQUITY $ 802,054.60 Requirements Open the template provided to you in the assignments link in the Excel Project folder in Canvas. Enter your name at the top of the INPUTS tab. Use the information above to fill in the tan cells within the INPUTS tab. Prepare a master budget for the quarter ended December 31, 2020 that includes: Finished Goods Inventory Cost per Unit Manufacturing Overhead Budget Sales Budget Selling & Administrative Expense Budget Schedule of Expected Cash Collections Cash Budget Production Budget Budgeted Income Statement Raw Materials Purchases Budget Budgeted Balance Sheet Direct Labor Budget Each schedule should be on a separate worksheet as appears in your template. Complete the tan shaded areas of the template only. All of your spreadsheets must be formula driven! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values. (The exception is the inputs tab and the goal seek value on the last tab.) Lexle's is considering using a new wool supplier to get a little higher quality input. However, the overall goal is company growth so they also want to ensure they aren't sacrificing too much net income at this time e Goal Seek to find the highest wool cost per yard that could be paid to the new supplier while still pulling in net income of $5,500,000 for the quarter. HINT: To find goal seek, click on the Data tab and under Data Tools click on the What-If Analysis. After you find your answer, manually enter your answer on the Goal Seek tab and then change the materials cost per yard back to $3.50 on the INPUTS tab. As you work, compare your spreadsheet against the check figures that follow. This will allow you to ensure you are on the right track and catch your mistakes early so that you aren't hunting through the entire file if you realize you have mistakes at the end. These tabs all build on one another so catching your mistakes early will make this a much smoother process. + CHECK FIGURES: TAB FG Inventory Cost per Unit Sales Budget Expected Cash Collections Production Budget RM Budget CELL Total FG Inventory Cost per Unit Total Sales for Quarter Total Cash Collections for Quarter Production in Units for December Production in Units for Quarter Raw Materials Purchases in October Raw Materials Purchases in Quarter Total Direct Labor Cost for Quarter Total Overhead Disbursements for Quarter Total S&A Disbursements for Quarter Ending Cash Balance for October Ending Cash Balance for Quarter Contribution Margin Net Income Total Assets DL Budget OH Budget S&A Budget Cash Budget VALUE $40.75 $11,900,000.00 $10,875,000.00 54,760 118,910 $418,287.80 $1,681,429.40 $1,783,650.00 $1,447,142.50 $916,480.00 $1,110,595.37 $4,886,087.23 $6,173,720.00 $6,001,320.00 $6,902,663.73 $4.55 Income Statement Balance Sheet Goal Seek When you are done, compare your file against the project rubric on the next page! Once you are happy with your finished project, upload your completed Excel file to Canvas via the project link to submit. You may only submit once so make sure this is your final version. Good luck! Have fun. I hope you enjoy this project! Cash Budget 2020 October November December QUARTER 4 Beginning Cash Balance 5 Cash Collections 6 Available Cash Balance 7 Cash Disbursements: 8 Direct Material Prior 9 Direct Material Current 10 Direct Labor 11 Overhead 12 S&A 13 Factory Update 14 Total Cash Disbursements 15 Borrowings 16 Interest 17 Repayments 18 ENDING CASH BALANCE B m in Lexie's Wool Sweaters Budgeted Income Statement For the Quarter Ending December 31, 2020 Sales Variable Expenses: Variable Manufacturing Expense Variable S&A Expense Contribution Margin Fixed Expenses: Fixed Manufacturing Expense 11 Fixed S&A Expense 12 Net Operating Income 13 Interest Expense 14 Net Income E ASSETS Cash Lexie's Wool Sweaters Budgeted Balance Sheet As of December 31, 2020 LIABILITIES & EQUITIES Accounts Payable Notes Payable Interest Payable TOTAL LIABILITIES Retained Earnings TOTAL LIABILITIES & EQUITIES 6 Accounts Receivable 7 Raw Materials Inventory 8 Finished Goods Inventory 9 PP&E, net 10 TOTAL ASSETS Lexie's Wool Sweaters Objective: The purpose of this assignment is to let you see the complexity of budgeting and to develop your spreadsheet skills. Case: Lexie's Wool Sweaters is a small business that makes 100% wool sweaters to sell. The information below pertains to the company's budgeting process during their busiest time of year. Lexie's uses variable costing. Each student must complete this assignment individually. . . . . . . . . . . . . . . . . . . Sales & Collections Each sweater is considered a unit of finished good which is sold for $100.00. Budgeted sales in units (sweaters) are as follows: October 2020 30,000 sweaters November 2020 34,000 sweaters December 2020 55,000 sweaters January 2021 47,000 sweaters February 2021 32,000 sweaters 30% of the company's sales are cash sales which are collected immediately. The remaining 50% of total sales are collected in the month of sale and the other 20% are collected in the month following the sale. Inventory Policy The company desires to have very little finished goods inventory on hand. At the end of each month, ending Inventory is 3% of the following month's budgeted sales in units. On September 30, the company had 1,500 units on hand. Product Input Expenses Direct Materials Each sweater requires 4 yards of wool. The cost per yard and other materials used is $3.50. Lexle's desires to have materials on hand at the end of each month equal to 7% of the following month's production needs. On September 30, the company had 8,265.60 yards of wool on hand. The company pays 85% of the month's material purchases in the month of purchase and delays the payment for the other 15% of the month's material purchases until the month following the purchase. Direct Labor The process to make each sweater requires 2 workers to contribute .5 hours of labor time. The workers are currently being paid $15.00/hour. Wages are paid in the month incurred. Manufacturing Overhead Variable manufacturing overhead is $11.75 per sweater. Fixed manufacturing overhead is $30,200 per month including $10,250 in depreciation that is not a current cash outflow. All cash disbursements for manufacturing overhead are paid in the month incurred. See below for changes to fixed overhead after October. Selling & Administrative Expenses nd administrative expenses is $7.37 per sweater sold. Fixed selling and administrative expense is $23,900 per month including $10,750 in depreciation that is not a current cash outflow. All cash disbursements for selling and administrative costs are paid in the month incurred. Factory Update & Cash Flow Lexle's borrowed $300,000 on Oct. 1, 2020, to prepare for a factory update. The full $300,000 was repaid on Nov. 30, 2020, but Lexle's paid two interest payments of $4,500 for the borrowings. The factory update cost $400,500 which was paid on Oct. 31, 2020. This addition to depreciable PP&E caused the fixed manufacturing overhead to raise to $30,750 per month in November with $15,750 being non-cash. 3rd Quarter Balance Sheet Lexie's Wool Sweaters Balance Sheet As of September 30, 2020 ASSETS LIABILITIES & EQUITIES Cash $ 150,000.00 Accounts Payable $ 14,500.00 Accounts Receivable $ 75,000.00 Notes Payable Raw Materials Inventory $ 28,929.60 Interest Payable Finished Goods Inventory $ 61,125.00 TOTAL LIABILITIES $ 14,500.00 PP&E, net $ 487,000.00 Retained Earnings $ 787,554.60 TOTAL ASSETS $ 802,054.60 TOTAL LIABILITIES & EQUITY $ 802,054.60 Requirements Open the template provided to you in the assignments link in the Excel Project folder in Canvas. Enter your name at the top of the INPUTS tab. Use the information above to fill in the tan cells within the INPUTS tab. Prepare a master budget for the quarter ended December 31, 2020 that includes: Finished Goods Inventory Cost per Unit Manufacturing Overhead Budget Sales Budget Selling & Administrative Expense Budget Schedule of Expected Cash Collections Cash Budget Production Budget Budgeted Income Statement Raw Materials Purchases Budget Budgeted Balance Sheet Direct Labor Budget Each schedule should be on a separate worksheet as appears in your template. Complete the tan shaded areas of the template only. All of your spreadsheets must be formula driven! This means that EVERY cell should contain either a value referenced from the input worksheet or a formula manually entered into the cell using the referenced values or the input values. (The exception is the inputs tab and the goal seek value on the last tab.) Lexle's is considering using a new wool supplier to get a little higher quality input. However, the overall goal is company growth so they also want to ensure they aren't sacrificing too much net income at this time e Goal Seek to find the highest wool cost per yard that could be paid to the new supplier while still pulling in net income of $5,500,000 for the quarter. HINT: To find goal seek, click on the Data tab and under Data Tools click on the What-If Analysis. After you find your answer, manually enter your answer on the Goal Seek tab and then change the materials cost per yard back to $3.50 on the INPUTS tab. As you work, compare your spreadsheet against the check figures that follow. This will allow you to ensure you are on the right track and catch your mistakes early so that you aren't hunting through the entire file if you realize you have mistakes at the end. These tabs all build on one another so catching your mistakes early will make this a much smoother process. + CHECK FIGURES: TAB FG Inventory Cost per Unit Sales Budget Expected Cash Collections Production Budget RM Budget CELL Total FG Inventory Cost per Unit Total Sales for Quarter Total Cash Collections for Quarter Production in Units for December Production in Units for Quarter Raw Materials Purchases in October Raw Materials Purchases in Quarter Total Direct Labor Cost for Quarter Total Overhead Disbursements for Quarter Total S&A Disbursements for Quarter Ending Cash Balance for October Ending Cash Balance for Quarter Contribution Margin Net Income Total Assets DL Budget OH Budget S&A Budget Cash Budget VALUE $40.75 $11,900,000.00 $10,875,000.00 54,760 118,910 $418,287.80 $1,681,429.40 $1,783,650.00 $1,447,142.50 $916,480.00 $1,110,595.37 $4,886,087.23 $6,173,720.00 $6,001,320.00 $6,902,663.73 $4.55 Income Statement Balance Sheet Goal Seek When you are done, compare your file against the project rubric on the next page! Once you are happy with your finished project, upload your completed Excel file to Canvas via the project link to submit. You may only submit once so make sure this is your final version. Good luck! Have fun. I hope you enjoy this project! Cash Budget 2020 October November December QUARTER 4 Beginning Cash Balance 5 Cash Collections 6 Available Cash Balance 7 Cash Disbursements: 8 Direct Material Prior 9 Direct Material Current 10 Direct Labor 11 Overhead 12 S&A 13 Factory Update 14 Total Cash Disbursements 15 Borrowings 16 Interest 17 Repayments 18 ENDING CASH BALANCE B m in Lexie's Wool Sweaters Budgeted Income Statement For the Quarter Ending December 31, 2020 Sales Variable Expenses: Variable Manufacturing Expense Variable S&A Expense Contribution Margin Fixed Expenses: Fixed Manufacturing Expense 11 Fixed S&A Expense 12 Net Operating Income 13 Interest Expense 14 Net Income E ASSETS Cash Lexie's Wool Sweaters Budgeted Balance Sheet As of December 31, 2020 LIABILITIES & EQUITIES Accounts Payable Notes Payable Interest Payable TOTAL LIABILITIES Retained Earnings TOTAL LIABILITIES & EQUITIES 6 Accounts Receivable 7 Raw Materials Inventory 8 Finished Goods Inventory 9 PP&E, net 10 TOTAL ASSETS