. D E G Sales: Sales in Units (Masks) Selling Price per Mask JULY 222,000 7.75 AUGUST 290,000 SEPTEMBER 216,000 OCTOBER 165,000 NOVEMBER 197,000 Collections: Cash Collected in the Month of Sale Cash Collected in Following Month 70% 30% 20% of next month's unit sales 44,400 masks Finished Goods Inventory: Ending FG Inventory Requirement Beginning FG Inventory, July 1, 2020 Raw Materials Inventory: Raw Materials Required per Mask Raw Materials Cost per Yard Ending RM Inventory Requirement Paid in Month of Purchase Paid in Following Month Beginning RM Inventory, July 1, 2020 Direct Labor: Labor Required per Mask Labor Cost per Hour 0.2 yards of fabric $14.00 per yard 30% of next month's production needs 60% 40% 14,136 yards of fabric $ 0.05 hours 18.00 per hour Manfacturing Overhead: Variable OH Fixed OH Voncash Fixed OH (included in above) $ 0.75 per mask $ + 114,000 per month 22,000 per month Selling & Administrative Expenses: Jariable S&A Fixed S&A oncash Fixed S&A (included in above) Other: $ $ 1.35 per unit sold 65,000 per month 9,000 per month D3 fox Sean Koeshall C D E G A Direct Labor: 3 Labor Required per Mask Labor Cost per Hour 0.05 hours 18.00 per hour Manfacturing Overhead: Variable OH Fixed OH Noncash Fixed OH (included in above) $ $ $ 0.75 per mask 114,000 per month 22,000 per month Selling & Administrative Expenses: Variable S&A 3 Fixed S&A Noncash Fixed S&A (included in above) $ $ $ 1.35 per unit sold 65,000 per month 9,000 per month 1 Other: Equipment Purchase 68,000 purchased on July 1, 2020 *depreciation is already included in noncash fixed OH above Cash Borrowings: Interest Payment Repayment 5,250 per month until repayment occurs 700,000 at the end of August 3 U 1 2 3 4 5 6 7 fx EO - 2-0 Trig G Define Name e, Trace Precedents Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & a Trace Dependents More Function Used Time Reference Functions 8 Create from Selection FRemove Arrows A1 fx Winnie's Wearable Masks B C D 1 Winnie's Wearable Masks Balance Sheet As of June 30, 2020 ASSETS LIABILITIES & EQUITIES 5 Cash $572.000 Accounts Payable $197.400 6 Accounts Receivable $375,000 Loan Payable $700,000 7 Raw Materials Inventory $197,904 Interest Payable $0 & Finished Goods Inventory $197.580 TOTAL LIABILITIES $897,400 9 PP&E, net $870,000 Retained Earnings $1,315,084 10 TOTAL EQUITIES $1,315,084 11 TOTAL ASSETS $2,212,484 TOTAL LIABILITIES & EQUITIES $2.212.484 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Home Insert Draw Page Layout Formulas Data Review View fx E y Define Name ? A AutoSum Recently Financial Logical Text Used Insert Function le Date & Lookup & Math & Time Reference Trig More Functions Create from Selection B7 B C D E 1 Finished Goods Inventory Cost per Unit Per Unit 2 3 Direct Material 4 Direct Labor 5 Variable Overhead 6 TOTAL FG INVENTORY COST PER UNIT 7 $ $ $ $ 2.80 0.80 0.75 4.35 Note: Computing the cost of a unit of inventory will be useful later on when you have to do a budgeted 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Define Name v Insert Function AutoSum Recently Financial Logical Text le Date & Lookup & Math & Time Reference Trig Used More Functions O Create from Selection B4 - D G Sales Budget 2020 July August September TOTAL 1 2 3 4 Unit Sales 5 Selling Price per Unit 6 TOTAL SALES 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 disert Function AltoSum Recently Financial Logical Used Text Date & Lookup & Math & More Time Reference Trig Functions Create from Selec B4 C E 1 Schedule of Expected Cash Collections 2 2020 July August September TOTAL 3 4 Cash from Current Month 5 Cash from Prior Month 6 TOTAL CASH COLLECTIONS 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Home Insert Draw Page Layout Formulas Data Review View fx v Define Name v 2 AutoSum Recently Financial Logical Used Insert Function Av ve Text Date & Lookup & Math & Reference Trig e, Trace Precedents a Trace Dependents FRemove Arrows More Functions Time 98 Create from Selection B4 fx D G Production Budget 2 July 2020 September August TOTAL October November Sales in Units Plus: Desired Units of Ending Inventory Total Unit Requirements Less: Units in Beginning Inventory PRODUCTION IN UNITS D Note: The amounts for October & November are necessary in order to complete September on this budget and for the Raw Materials Budget 2 3 4 5 3 Ulmus Dala Review View fx E OM Define Name ? AutoSum Recently Financial Logical Used N Insert unction |Av le Date & Lookup & Math & Time Reference e Trace Precedents a Trace Dependents Trig More Functions Create from Selection Remove Arrows Sh Form 1 D E G Raw Materials Purchases Budget July August 2020 September TOTAL October Units Produced Yards of RM Required Per Unit of FG Total Yards Used in Production Plus: Desired Yards in Ending Inventory Total Yards Required Less: Yards in Beginning Inventory RAW MATERIALS PURCHASES (YARDS) Cost per Yard RAW MATERIALS PURCHASES (COST) JU Define Name v Insert Function AutoSum Recently Financial Logical Used Text Date & Lookup & Math & Time Reference Trig More Functions Create from Selection B4 E F TOTAL A D 1 Direct Labor Budget 2 2020 3 July August September 4 Units Produced 5 Labor Hours per Unit 6 TOTAL LABOR HOURS REQUIRED 7 Rate per Labor Hour 8 TOTAL DIRECT LABOR COST 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 DU NOVIUW View fx EO IG lel Define Name ? A AutoSum Recently Financial Logical Text Used Insert Function Date & Lookup & Math & Time Reference Trig e Trai a Tra Rer More Functions Create from Selection B4 1 Manufacturing Overhead Budget 2020 July August September TOTAL 2 3 4 Units Produced 5 Variable Overhead Rate per Unit 6 Total Variable Overhead Cost 7 Fixed Overhead 8 TOTAL OVERHEAD COST 9 Noncash Overhead Expenses 10 TOTAL OVERHEAD DISBURSEMENTS 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 fr - - - 2 - A Define Name Insert Function AutoSum Recently Financial Logical Used e Date & Lookup & Math & Time Reference Text Trig More Functions Ed Create from Selection a E B4 D E 1 Selling & Administrative Expense Budget July August 2020 September TOTAL 2 3 4 Unit Sales 5 Variable S&A per Unit 6 Total Variable S&A 7 Fixed S&A 8 TOTAL S&A EXPENSE 9 Noncash S&A Expenses 10 TOTAL S&A DISBURSEMENTS 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Insert Function AutoSum Recently Financial Logical Used Text Date & Lookup & Math & Time Reference Trig More Functions E8 Create from Selection L22 D E F 1 Cash Budget July August 2020 September TOTAL 2 3 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 Equipment Purchase 14 Interest 15 Loan Repayment 16 Total Cash Disbursements 17 ENDING CASH BALANCE 18 19 20 21 22 23 24 25 26 27 28 29 30 liser Function AUTOSUM Recently Financial Logical Used Text Date & Lookup & Math & Time Reference Trig More Functions Create from Se F22 C D 1 Winnie's Wearable Masks 2 Budgeted Income Statement 3 For the Quarter Ending September 30, 2020 4 Sales 5 Variable Expenses: 6 Variable Manufacturing Expense 7 Variable S&A Expense 8 Contribution Margin 9 Fixed Expenses: 10 Fixed Manufacturing Expense 11 Fixed S&A Expense 12 Net Operating Income 13 Interest Expense 14 Net Income 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Define Name Insert Function AutoSum Recently Financial Logical Used Text e Date & Lookup & Math & Time Reference Trig eTrace Precec a Trace Depers More Functions Create from Selection A16 Remove Arre B D 1 E 2 3 4 ASSETS 5 Cash 6 Accounts Receivable 7 Raw Materials Inventory 8 Finished Goods Inventory 9 PP&E, net 10 11 TOTAL ASSETS 12 13 Winnie's Wearable Masks Budgeted Balance Sheet As of September 30, 2020 LIABILITIES & EQUITIES Accounts Payable Notes Payable Interest Payable TOTAL LIABILITIES Retained Earnings TOTAL EQUITIES TOTAL LIABILITIES & EQUITIES 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Insert Function AutoSum Recently Financial Logical Used Text Date & Lookup & Math & Time Reference Trig More Functions D11 x fx A B C D E F 1 2 GOAL SEEK Selling Price per Unit: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24