Alignment Number 1 Styles Cells Ideas Sensi A13 GOAL SEEK: Highest wool cost per yard while meeting goal of $5,500,000 in net income Income Statement Balance Sheet Soal Seek JUJU Projected Budgeting Data ales & 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 7 Ending FG Inventory, September 30, 2020 3% of next months unit sweater sales 1,500 sweaters 9 Product Input Expenses Direct Materials 1 Ending RM Inventory, September 30, 2020 2 Yards of Wool Required per Sweater 3 Raw Materials Cost per Yard of Wool 4 Ending RM Inventory Requirement 8265.60 yards 4 yards per sweater 3.50 per yard 7% of next months sweater production needs 26 Wool Purchases Paid for in the Month of Purchase 27 Wool Purchases Paid for in the Month following the Purchase 85% 15% 25 29 Direct Labor 30 Number of Workers Required for the Making of each 31 Lobor Hours Required per Worker per Unit of FG (Sweater) 32 Labor Cost per Hour 2 workers 0.5 hours 15.00 per hour 33 34 Manufacturing Overheed 35 Variable Manufacturing Overhead 36 Fixed Manufacturing Overhead INPUTS INPUTS - Balance Sheet 11.75 per sweater $ 30,200.00 per month Oc) FG inventory Cost per Uni... S 30750.00 per month (Now & beyond) Ready TOTAL ASSETS SB02054.00 TOTAL LIABILITIES & EQUITY'S 302,05460 U dore - A E Cell Styles 2X Delete- Format Editing 8-98 Number Ideas Clipboard Alignment Styles Cells Ideas E36 X for 30750 D 22 Yards of Wool Required per Sweater 23 Raw Materials Cost per Yard of Wool 24 Ending RM Inventory Requirement 4 yards per sweater 3.50 per yard 7% of next months sweater production needs 26 Wool Purchases Paid for in the Month of Purchase 27 Wool Purchases Paid for in the Month following the Purchase 85% 15% 29 Direct Labor 30 Number of Workers Required for the Making of Each 31 Labor Hours Required per Worker per Unit of FG (Sweater) 32 Labor Cost per Hour 2 workers 0.5 hours 15.00 per hour 34 Manufacturing Overhead 35 Variable Manufacturing Overhead 36 Fixed Manufacturing Overhead 37 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) 39 Selling & Administrative Expenses 40 Variable S&A 41 Fixed S&A 42 Noncash Fixed S8A (included in above) $ $ 7.37 per unit sold 23,900.00 per month (10,750.00 per month 44 Factory Update & Cash Flow 45 Factory Update (PP&E) $ 400,500.00 paid on October 31, 2020 47 Principle Borrowed on October 1, 2020 48 Principle Repaid on November 30, 2020 49 Interest Payment on Borrowings in October & November $ 300,000.00 $ 300,000.00 $ 9,000.00 per month (paid in following month) ESS INPUTS INPUTS - Balance Sheet FG Inventory Cost per Unit... TOTAL ASSETS S 802,054.60 TOTAL LIABILITIES & EQUITY'S 302,054.00 stoSave OD 2 0 ACCT 285 - Excel Project Instructions & R... - Saved Michael Arhin MA le Home Insert Design Layout References Mailings Review View Help A A Aa A l l Heading 1 Normal T No Spac... Editi BIab x x AA. upboards Font Paragraph Styles 3rd Quarter Balance Sheet Lexie's Wool Sweaters Balance Sheet As of September 30, 2020 ASSETS LIABILITIES & EQUITIES Cash $ 150,000.bo Accounts Payable $ 14,500.00 Accounts Receivable $ 75,000.00 Notes Payable Raw Materials Inventory $ 28,929.00 Interest Payable Finished Goods Inventory $ 61,125.00 TOTAL LIABILITIES $ 14,500.00 PP&E, net $ 487,000.00 Retained Earnings $ 787,554.00 TOTAL ASSETS $ 302,054.00 TOTAL LIABILITIES & EQUITY'S 802,054.00 Malings Review View Help A Aa Ao EEES AT l . Heading 1 1 Normal No Spac... Heading 2 Title Paragraph Styles 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