QUESTION:

ANSWERS TEMPLATE:

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 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 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. CELL As you work, compare your spreadsheet against the check figures that follow. CHECK FIGURES: TAB FG Inventory Cost per Unit Total FG Inventory Cost per Unit Sales Budget Total Sales for Quarter Expected Cash Collections Total Cash Collections for Quarter Production Budget Production in Units for December Production in Units for Quarter RM Budget Raw Materials Purchases in October Raw Materials Purchases in Quarter DL Budget Total Direct Labor Cost for Quarter OH Budget Total Overhead Disbursements for Quarter S&A Budget Total S&A Disbursements for Quarter Cash Budget Ending Cash Balance for October Ending Cash Balance for Quarter Income Statement Contribution Margin Net Income Balance Sheet Total Assets Goal Seek 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,08723 $6,173,720.00 $6,001,320.00 $6,902,663.73 $4.55 Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020 30,000 November 2020 34,000 December 2020 55,000 January 2021 47,000 February 2021 32,000 Sales in Units (Sweaters) Selling Price per Sweater 100.00 Cash Sales Collected in the Month of Sale Credit Sales Collected in the Month of Sale Credit Sales Collected in the following Month 30% 50% 20% Inventory Policy Ending FG Inventory Requirement Ending FG Inventory, September 30, 2020 3% of next months unit sweater sales 1,500 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 8265.60 yards 4 yards per sweater 3.50 per yard 7% of next months sweater production needs Wool Purchases Paid for in the Month of Purchase Wool Purchases Paid for in the Month following the Purchase 85% 15% 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 2 workers 0.5 hours 15.00 per hour Manufacturing Overhead Variable Manufacturing Overhead Fixed Manufacturing Overhead Noncash Fixed Manufacturing Overhead (included in above) $ $ $ 11.75 per sweater 30,200.00 per month (Oct.) 10,250.00 per month (Oct.) $ $ 30,750.00 per month (Nov. & beyond) 15,750.00 per month (Nov. & beyond) Selling & Administrative Expenses Variable S&A Fixed S&A Noncash Fixed S&A (included in above) $ $ $ 7.37 per unit sold 23,900.00 per month 10,750.00 per month Factory Update & Cash Flow Factory Update (PP&E) $ 400,500.00 paid on October 31, 2020 Principle Borrowed on October 1, 2020 Principle Repaid on November 30, 2020 Interest Payment on Borrowings in October & November $ $ $ 300,000.00 300,000.00 9,000.00 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) 14.00 Direct Labor 15.00 Variable Overhead 11.75 TOTAL FG INVENTORY COST PER UNIT 40.75 Sales Budget 2020 October November December | QUARTER 30,000 34,000 55,000 119,000 100 100 100 3,000,000.00 $ 3,400,000.00 $ 5,500,000.00 $ 11,900,000.00 Unit Sales Selling Price per Unit TOTAL SALES $ Schedule of Expected Cash Collections 2020 October November December QUARTER Cash from Current Month Cash Sales $ 900,000.00 $ 1,020,000.00 $ 1,650,000.00 $ 3,570,000.00 Cash from Current Month Credit Sales $ 1,500,000.00 $ 1,700,000.00 $ 2,750,000.00 $ 5,950,000.00 Cash from Prior Month Credit Sales $ 75,000.00 $ 600,000.00 $ 680,000.00 $ 1,355,000.00 TOTAL CASH COLLECTIONS $ 2,475,000.00 $ 3,320,000.00 $ 5,080,000.00 $ 10,875,000.00 Sales in Units Plus: Desired Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory PRODUCTION IN UNITS Production Budget 2020 October November December 30,000 34,000 55,000 1,020 1,650 1,410 31,020 35,650 56,410 1,500 1,020 1,650 29,520 34,630 54,760 QUARTER 119,000 4,080 123,080 4,170 118,910 2021 January February 47,000 32,000 960 47,960 1,410 46,550 2,021.00 January 46,550.00 186,200.00 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) Raw Materials Purchases Budget 2,020.00 October November December 29,520.00 34,630.00 54,760.00 118,080.00 138,520.00 219,040.00 9,696.40 15,332.80 13,034.00 127,776.40 153,852.80 232,074.00 8,265.60 9,696.40 15,332.80 119,510.80 144,156.40 216,741.20 3.50 3.50 3.50 418,287.80 504,547.40 758,594.20 QUARTER 118,910.00 475,640.00 38,063.20 513,703.20 33,294.80 480,408.40 10.50 1,681,429.40 Units Produced Labor Hours per Unit TOTAL LABOR HOURS REQUIRED Rate per Labor Hour TOTAL DIRECT LABOR COST Direct Labor Budget 2020 October November December 29,520 34,630 54,760 $ 1.00 $ 1.00 $ 1.00 29,520 34,630 54,760 $ 15.00 $ 15.00 $ 15.00 $ 442,800.00 $ 519,450.00 $ 821,400.00 QUARTER 118,910 $ 1.00 118,910 $ 15.00 $ 1,783,650.00 Manufacturing Overhead Budget Units Produced Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead TOTAL OVERHEAD COST Noncash Overhead Expenses TOTAL OVERHEAD DISBURSEMENTS $ $ $ $ $ $ October 29,520 11.75 346,860.00 19,950.00 366,810.00 10,250.00 377,060.00 $ $ $ $ $ $ 2020 November 34,630 11.75 $ 406,902.50 $ 15,000.00 $ 421,902.50 $ 15,750.00 $ 437,652.50 $ December | 54,760 11.75 $ 643,430.00 $ 15,000.00 $ 658,430.00 $ 15,750.00 674,180.00 $ QUARTER 118,910 11.75 1,397,192.50 49,950.00 1,447,142.50 1,447,142.50 Selling & Administrative Expense Budget 2020 October November December QUARTER Unit Sales 30,000 34,000 55,000 119,000 Variable S&A per Unit $ 7.37 $ 7.37 $ 7.37 $ 7.37 Total Variable S&A $ 221,100.00 $ 250,580.00 $ 405,350.00 $ 877,030.00 Fixed S&A $ 13,150.00 $ 13,150.00 $ 13,150.00 $ 39,450.00 TOTAL S&A EXPENSE $ 234,250.00 $ 263,730.00 $ 418,500.00 $ 916,480.00 Noncash S&A Expenses $ 10,750.00 $ 10,750.00 $ 10,750.00 TOTAL S&A DISBURSEMENTS $ 245,000.00 $ 274,480.00 $ 429,250.00 $ 916,480.00 Cash Budget 2020 December October November QUARTER Beginning Cash Balance Cash Collections Available Cash Balance Cash Disbursements: Direct Material Prior Direct Material Current Direct Labor Overhead S&A Factory Update Total Cash Disbursements Borrowings Interest Repayments ENDING CASH BALANCE 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 Fixed S&A Expense Net Operating Income Interest Expense Net Income Lexie's Wool Sweaters Budgeted Balance Sheet As of December 31, 2020 LIABILITIES & EQUITIES Accounts Payable Notes Payable ASSETS Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory PP&E, net TOTAL ASSETS Interest Payable TOTAL LIABILITIES Retained Earnings TOTAL LIABILITIES & EQUITIES GOAL SEEK: Highest wool cost per yard while meeting goal of $5,500,000 in net income