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. 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. Lexie'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 Variable selling and 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 Lexie'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 Lexie'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.) 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 at 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! Sales & Collections October 2020 November 2020 December 2020 January 2021 February 2021 Sales In Units (Sweaters) Selling Price per Sweater Cash Sales Collected in the Month of Sale Credit Sales Collected in the Month of Sale Credit Sales Collected in the following Month Inventory Policy Ending FG Inventory Requirement Ending FG Inventory, September 30, 2020 of next months unit sweater sales sweaters Product Input Expenses Direct Materials Ending RM Inventory, September 30, 2020 Yards of Wool Required per Sweater Raw Materials Cost per Yard of Wool Ending RM Inventory Requirement yards yards per sweater per yard of next months sweater production needs Wool Purchases Pald for in the Month of Purchase Wool Purchases Pald for in the Month following the Purchase Direct Labor Number of Workers Required for the Making of Each Sweater Labor Hours Required per Worker per Unit of FG (Sweater) Labor Cost per Hour workers hours per hour Manufacturing Overhead Variable Manufacturing Overhead Fixed Manufacturing Overhead Noncash Fixed Manufacturing Overhead (Included in above) per sweater per month (Oct.) per month (Oct.) per month (Nov. & beyond) per month (Nov. & beyond) Selling & Administrative Expenses Variable S&A Fixed S&A Noncash Fixed S&A (Included in above) per unit sold per month per month Factory Update & Cash Flow Factory Update (PP&E) paid on October 31, 2020 Principle Borrowed on October 1, 2020 Principle Repaid on November 30, 2020 Interest Payment on Borrowings in October & November per month (paid in following month) ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS Lexie's Wool Sweaters Balance Sheet As of September 30, 2020 LIABILITIES & EQUITIES $ 150,000.00 Accounts Payable $ 14,500.00 $ 75,000.00 Notes Payable $ 28,929.60 Interest Payable $ 61,125.00 TOTAL LIABILITIES $ 14,500.00 $ 487,000.00 Retained Earnings $ 787,554.60 $ 802,054.60 TOTAL LIABILITIES & EQUITY $ 802,054.60 Finished Goods Inventory Cost per Unit Per Unit (Sweater) Direct Material (wool & other materials) Direct Labor Variable Overhead TOTAL FG INVENTORY COST PER UNIT Sales Budget 2020 October November December QUARTER Unit Sales Selling Price per Unit TOTAL SALES QUARTER Schedule of Expected Cash Collections 2020 October November December Cash from Current Month Cash Sales Cash from Current Month Credit Sales Cash from Prior Month Credit Sales TOTAL CASH COLLECTIONS Production Budget 2020 November December 2021 October QUARTER I January February Sales In Units Plus: Desired Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory PRODUCTION IN UNITS Raw Materials Purchases Budget 2020 October November December 2021 January I QUARTER Units (Sweaters) Produced Total Yards of Wool Used in Production Plus: Desired Wool in Ending Inventory Total Wool Required Less: Wool in Beginning Inventory RAW MATERIALS PURCHASES (UNITS) Cost per yard RAW MATERIALS PURCHASES (COST) Direct Labor Budget 2020 December October November QUARTER Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST Manufacturing Overhead Budget 2020 October November December 1 QUARTER Units Produced Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses TOTAL OVERHEAD DISBURSEMENTS Selling & Administrative Expense Budget __ 2020 October | November December QUARTER Unit Sales Variable S&A per Unit Total Variable S&A Fixed S&A TOTAL S&A EXPENSE Noncash S&A Expenses TOTAL S&A DISBURSEMENTS