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. 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 N otes 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 fan 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.) 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 N otes 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 fan 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.) Lexie'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 of this time for the change. Use 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! Evaluation Rubric that will be used to grade your project Possible Score Have correct answers that match the following check figures: Total Sales in December = $5,500,000 - Cash Collections in October = $2,475,000 Desired Units of Ending Finished Goods Inventory for the Quarter = 1,410 Sweaters - Raw Material Purchases in December = $758,594.20 - Total Direct Labor Cost in October = $442,800 - Total Overhead Disbursements in December = $658,430 - - November Ending Cash Balance = $2,429,404.41 - Net Income for the Quarter = $6,001,320 Total Assets and Total Liabilities & Equity at the End of the Quarter both = $6,902,663.73 - - Goal seek highest wool cost per yard = $4.55 All cells are formula driven you lose 2 points each time there is a number entered in the worksheets) Your worksheet must pass our tests of your formulas. Be sure that you only make one change at a time (and then change back to the original value before moving on to the next test). On your input tab we will: Change the number of workers required to 5, and net income should become $3,323,820. Change the cash payment of raw materials to 50% in month of sale and 50% in next month, and the ending cash balance for the quarter on the cash budget is $5,151,595.20. Change units sold in December to 10,000 and the retained earnings on the balance sheet becomes $4,454,274.60. Total Score Comments Spring 2020 Spreadsheet Project Name: Lexie's Wool Sweaters Projected Budgeting Data s Sales & Collections October 2020 - Home 2020 | December 2020 January 2021 February 2021 7 Sales in Unit (Sweatera) 9 Selling Price per Sweater 11 Cah Sales Collected in the Month of Sale 12 Credit Sales Collected in the Month of Sale 13 Credit Sales Collected in the following Month 1s Inventory Policy 16 Ending FG Inventory Requirement 17 Ending FG Inventory, September 30, 2020 of next month unit weer sales 19 Product Input Expenses 20 Dured Materials 21 Ending RM Inventory, September 30, 2020 22 Yarch of Wool Required per Sweater 23 Raw Material Colper Yard of Wool 24 Ending RM Inventory Requirement 25 26 Wool Purchane Paid for in the Month of Purchiae 27 Wool Purchane Paid for in the Month following the Purchione yerde yard per weer per yard of next month weater production neech worker 29 Dired Labor 30 Number of Worker Required for the Making of Each Sweater 31 Labor Hours Required per Worker per Unit of FG (Sweater) 32 Labor Cont per Hour per hour 34 Manufacturing Overhead 35 Variable Manufacturing Overhead 36 Fixed Manufacturing Overhead 37 Nonceh Fixed Manufacturing Overhead (induced in above) per month Oct) per month Oct.) per month (Nov. & beyond per month (Nov. & beyond 39 Selling & Acilave Expenses 40 Variable SSA 41 Fixed SEA 42 Noncoh Fixed SBA induced in above) per unit sold per month per month 44 Factory Update & Coh Flow 45 Factory Update (PPE) paid on October 31, 2020 47 Principle Borrowed on October 1, 2020 48 Principle Repaid on November 30, 2020 49 Interest Payment on Borrowing in October November per month load in following month) 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. 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 N otes 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 fan 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.) 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 N otes 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 fan 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.) Lexie'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 of this time for the change. Use 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! Evaluation Rubric that will be used to grade your project Possible Score Have correct answers that match the following check figures: Total Sales in December = $5,500,000 - Cash Collections in October = $2,475,000 Desired Units of Ending Finished Goods Inventory for the Quarter = 1,410 Sweaters - Raw Material Purchases in December = $758,594.20 - Total Direct Labor Cost in October = $442,800 - Total Overhead Disbursements in December = $658,430 - - November Ending Cash Balance = $2,429,404.41 - Net Income for the Quarter = $6,001,320 Total Assets and Total Liabilities & Equity at the End of the Quarter both = $6,902,663.73 - - Goal seek highest wool cost per yard = $4.55 All cells are formula driven you lose 2 points each time there is a number entered in the worksheets) Your worksheet must pass our tests of your formulas. Be sure that you only make one change at a time (and then change back to the original value before moving on to the next test). On your input tab we will: Change the number of workers required to 5, and net income should become $3,323,820. Change the cash payment of raw materials to 50% in month of sale and 50% in next month, and the ending cash balance for the quarter on the cash budget is $5,151,595.20. Change units sold in December to 10,000 and the retained earnings on the balance sheet becomes $4,454,274.60. Total Score Comments Spring 2020 Spreadsheet Project Name: Lexie's Wool Sweaters Projected Budgeting Data s Sales & Collections October 2020 - Home 2020 | December 2020 January 2021 February 2021 7 Sales in Unit (Sweatera) 9 Selling Price per Sweater 11 Cah Sales Collected in the Month of Sale 12 Credit Sales Collected in the Month of Sale 13 Credit Sales Collected in the following Month 1s Inventory Policy 16 Ending FG Inventory Requirement 17 Ending FG Inventory, September 30, 2020 of next month unit weer sales 19 Product Input Expenses 20 Dured Materials 21 Ending RM Inventory, September 30, 2020 22 Yarch of Wool Required per Sweater 23 Raw Material Colper Yard of Wool 24 Ending RM Inventory Requirement 25 26 Wool Purchane Paid for in the Month of Purchiae 27 Wool Purchane Paid for in the Month following the Purchione yerde yard per weer per yard of next month weater production neech worker 29 Dired Labor 30 Number of Worker Required for the Making of Each Sweater 31 Labor Hours Required per Worker per Unit of FG (Sweater) 32 Labor Cont per Hour per hour 34 Manufacturing Overhead 35 Variable Manufacturing Overhead 36 Fixed Manufacturing Overhead 37 Nonceh Fixed Manufacturing Overhead (induced in above) per month Oct) per month Oct.) per month (Nov. & beyond per month (Nov. & beyond 39 Selling & Acilave Expenses 40 Variable SSA 41 Fixed SEA 42 Noncoh Fixed SBA induced in above) per unit sold per month per month 44 Factory Update & Coh Flow 45 Factory Update (PPE) paid on October 31, 2020 47 Principle Borrowed on October 1, 2020 48 Principle Repaid on November 30, 2020 49 Interest Payment on Borrowing in October November per month load in following month)