Let me know if there's any information I forgot to put in here. The excel sheet Part 2 asks for is the first image.

OPERATING INCOME Factory Utilities FIXED COSTS Plant Mantainence Supervisor CONTRIBUTION MARGIN Company Labels VARIABLE COSTS SALES REVENUES Total Fixed Costs Total Variable Costs Pro Forma Contribution Format Income Statement - Year 20XX $ 32,000 This section is for allFIXED costs, regardless of whether it is a product or a period cost. This section is for allVARIABLE costs, regardless of whether it is a product or a period cost. LAB 4 Tonia and Tara have realized that they will be able to better analyze their profitability and cost structure by converting their pro forma income statement to a Contribution Format Income Statement. After reformatting the income statement and analyzing fixed and variable costs, the two women analyze their breakeven point. They use this information to determine sales information for different target profits, along with completing an analysis on how sensitive the company is to changes in sales. This lab consists of two parts. Part I consists of identifying variable and fixed costs, breaking a mixed cost into its variable and fixed components, and reformatting the income statement. Part 2 consists of using the contribution margin calculated in Part I to perform a breakeven analysis and a sensitivity analysis. PART 1: Exhibit 4-A is a reproduction of Exhibit 1-A. In this exhibit, identify whether a cost is variable or fixed and enter the amount in the correct column. Note that Factory Utilities is a mixed cost. You will need to complete Worksheet 4-1 to determine the variable and fixed components for Factory Utilities using the High-Low method Exhibit 4-A. T.O.T.E.S. accountant collected the following data from last year's utility bills to assist you in calculating the fixed and variable components of the factory utility costs. Utility Expenses for Prior Year Cost of Month Machine Hours Utilities January 400 $3,315 February 415 $3,480 March 450 S 3,505 April 365 $ 2.972 May 283 $ 2,653 June 260 $ 2,490 July 284 $ 2,895 August 291 $ 2,945 September 310 $ 2,705 October 250 $ 2,617 November 350 $ 2,848 December 3421 S 2,575 Worksheet 4-1: Breakdown a mixed cost into fixed and variable components. Calculate fixed and variable costs for Factory Utilities using the High-Low method. Step 1: Identify months with the High and Low Activity Levels, e.g, machine-hours. Use the data from Utility Expenses for Prior Year. High Month: Low Month: Step 2: Calculate variable cost per machine hour (round to the penny) using the high-low method. Step 3: Calculate fixed cost per month (Round to nearest dollar) using the cost formula and monthly data. Write the cost formula FIRST. Step 4: Calculate fixed cost per year (round to nearest dollar) using result from Step 3. Step 5: Calculate the annual total variable cost (round to nearest dollar). (Use BUDGET NOT prior year data to calculate.) EXHIBIT 4-A: Determine Variable and Fixed Costs T.O.T.E.S. IDENTIFY IF COST IS VARIABLE OR FIXED Estimated Revenues And Expenses For Upcoming Year (put an X in the correct column) Forecasted Sales in units 500,000 VARIABLE FIXED ACCOUNT NAME AMOUNT COST COST Sales Revenue $2,475,000 Expenses Administrative Office Supplies 3,600 Administrative Staff, e.g. accountant, etc. 150,000 Adminstrative Payroll and Fringe 147,300 Advertising, Trade Shows, Travel & Entertainment 46,000 Company Labels 4,200 Depreciation - Admin. Furniture & Equipment 2,450 Depreciation - Sewing Machines & Cutting Machines 7,600 Equipment Lease - Administrative Offices 5,000 Executive Salaries 85,500 Fabric 600,000 Factory Utilities (mixed cost) 38,000 Freight & Postage - Administrative 2,500 Insurance - Company Liability 9,000 Insurance - Plant 14,000 Legal & Professional 5,000 Logo Ink 26,000 Plant Maintenance Supervisor 32,000 Plant Manager 68,000 Rent - Administrative Building 11,000 Rent - Plant Building 42,000 Repairs and Maintenance - Plant 27,000 Sales Commissions 101,000 Sales Salaries 12,000 Shipping and Handling of Finished Product (Selling) 40,350 Thread 50,000 Wages - Cutting Department 120,000 Wages - Printing Department 15,000 Wages - Sewing Department 80,000 Webbing 555,000 Hint: There are 10 variable costs, including the variable portion of the mixed cost item. PART 2: Complete the Contribution Format Income Statement with ALL accounts listed using the Excel worksheet found on Blackboard. Complete Exhibit 4-B using the totals from the Excel Pro forma Contribution Format Income Statement. Verify all totals are correct PRIOR to starting Part 3. EXHIBIT 4-B: Pro forma Contribution Format Income Statement T.O.T.E.S. Pro forma Summarized Contribution Format Income Statement For Year ending December 31, 20XX SALES REVENUES $ TOTAL VARIABLE COSTS CONTRIBUTION MARGIN $ TOTAL FIXED COSTS OPERATING INCOME (LOSS) $ PART 3: Using the information from Exhibit 4-B, calculate contribution margin, contribution margin ratio, perform a breakeven analysis, and perform a sensitivity analysis using operating leverage. Unless otherwise specified, the information will be taken from the amounts appearing in Exhibits 4-A and Exhibit 4-B. 1. Compute the TOTAL VARIABLE COSTS PER UNIT as follows: a. Total projected annual variable costs from Exhibit 4-B b. Total projected units from Exhibit 4-A c. Total Variable Cost per unit (la + lb) $ per unit (Round to 4 decimal places) 2. Compute CONTRIBUTION MARGIN PER UNIT using sales price per unit and variable cost per unit (Round to 4 decimal places): 3. Compute the CONTRIBUTION MARGIN RATIO (Round to 4 decimal places): 4. Compute the BREAKEVEN IN UNITS (Round up to the nearest whole number): 5. Compute the BREAKEVEN IN SALES DOLLARS (Round up to the nearest dollar): 6. Compute the NUMBER OF UNITS THAT MUST BE SOLD to reach a targeted profit of $152,000 taking into consideration the total projected fixed costs (Round to the nearest whole number. Hint: Use the breakeven unit formula): 7. How MANY SALES DOLLARS MUST BE GENERATED to earn an annual operating income of $300,000? (Round to the nearest dollar. Hint: Use Breakeven in Sales $ formula) 8. Calculate the company's DEGREE OF OPERATING LEVERAGE based on the proposed budget (Round to 4 decimal places): 9. If sales in units increase by 10% what would be the NEW PROJECTED OPERATING INCOME? (Use degree of operating leverage calculated in #8. Round final answer to nearest dollar) 10. If sales in units decreased by 15% what would be the NEW PROJECTED OPERATING INCOME? (Round final answer to nearest dollar.) II. OPERATING INCOME Factory Utilities FIXED COSTS Plant Mantainence Supervisor CONTRIBUTION MARGIN Company Labels VARIABLE COSTS SALES REVENUES Total Fixed Costs Total Variable Costs Pro Forma Contribution Format Income Statement - Year 20XX $ 32,000 This section is for allFIXED costs, regardless of whether it is a product or a period cost. This section is for allVARIABLE costs, regardless of whether it is a product or a period cost. LAB 4 Tonia and Tara have realized that they will be able to better analyze their profitability and cost structure by converting their pro forma income statement to a Contribution Format Income Statement. After reformatting the income statement and analyzing fixed and variable costs, the two women analyze their breakeven point. They use this information to determine sales information for different target profits, along with completing an analysis on how sensitive the company is to changes in sales. This lab consists of two parts. Part I consists of identifying variable and fixed costs, breaking a mixed cost into its variable and fixed components, and reformatting the income statement. Part 2 consists of using the contribution margin calculated in Part I to perform a breakeven analysis and a sensitivity analysis. PART 1: Exhibit 4-A is a reproduction of Exhibit 1-A. In this exhibit, identify whether a cost is variable or fixed and enter the amount in the correct column. Note that Factory Utilities is a mixed cost. You will need to complete Worksheet 4-1 to determine the variable and fixed components for Factory Utilities using the High-Low method Exhibit 4-A. T.O.T.E.S. accountant collected the following data from last year's utility bills to assist you in calculating the fixed and variable components of the factory utility costs. Utility Expenses for Prior Year Cost of Month Machine Hours Utilities January 400 $3,315 February 415 $3,480 March 450 S 3,505 April 365 $ 2.972 May 283 $ 2,653 June 260 $ 2,490 July 284 $ 2,895 August 291 $ 2,945 September 310 $ 2,705 October 250 $ 2,617 November 350 $ 2,848 December 3421 S 2,575 Worksheet 4-1: Breakdown a mixed cost into fixed and variable components. Calculate fixed and variable costs for Factory Utilities using the High-Low method. Step 1: Identify months with the High and Low Activity Levels, e.g, machine-hours. Use the data from Utility Expenses for Prior Year. High Month: Low Month: Step 2: Calculate variable cost per machine hour (round to the penny) using the high-low method. Step 3: Calculate fixed cost per month (Round to nearest dollar) using the cost formula and monthly data. Write the cost formula FIRST. Step 4: Calculate fixed cost per year (round to nearest dollar) using result from Step 3. Step 5: Calculate the annual total variable cost (round to nearest dollar). (Use BUDGET NOT prior year data to calculate.) EXHIBIT 4-A: Determine Variable and Fixed Costs T.O.T.E.S. IDENTIFY IF COST IS VARIABLE OR FIXED Estimated Revenues And Expenses For Upcoming Year (put an X in the correct column) Forecasted Sales in units 500,000 VARIABLE FIXED ACCOUNT NAME AMOUNT COST COST Sales Revenue $2,475,000 Expenses Administrative Office Supplies 3,600 Administrative Staff, e.g. accountant, etc. 150,000 Adminstrative Payroll and Fringe 147,300 Advertising, Trade Shows, Travel & Entertainment 46,000 Company Labels 4,200 Depreciation - Admin. Furniture & Equipment 2,450 Depreciation - Sewing Machines & Cutting Machines 7,600 Equipment Lease - Administrative Offices 5,000 Executive Salaries 85,500 Fabric 600,000 Factory Utilities (mixed cost) 38,000 Freight & Postage - Administrative 2,500 Insurance - Company Liability 9,000 Insurance - Plant 14,000 Legal & Professional 5,000 Logo Ink 26,000 Plant Maintenance Supervisor 32,000 Plant Manager 68,000 Rent - Administrative Building 11,000 Rent - Plant Building 42,000 Repairs and Maintenance - Plant 27,000 Sales Commissions 101,000 Sales Salaries 12,000 Shipping and Handling of Finished Product (Selling) 40,350 Thread 50,000 Wages - Cutting Department 120,000 Wages - Printing Department 15,000 Wages - Sewing Department 80,000 Webbing 555,000 Hint: There are 10 variable costs, including the variable portion of the mixed cost item. PART 2: Complete the Contribution Format Income Statement with ALL accounts listed using the Excel worksheet found on Blackboard. Complete Exhibit 4-B using the totals from the Excel Pro forma Contribution Format Income Statement. Verify all totals are correct PRIOR to starting Part 3. EXHIBIT 4-B: Pro forma Contribution Format Income Statement T.O.T.E.S. Pro forma Summarized Contribution Format Income Statement For Year ending December 31, 20XX SALES REVENUES $ TOTAL VARIABLE COSTS CONTRIBUTION MARGIN $ TOTAL FIXED COSTS OPERATING INCOME (LOSS) $ PART 3: Using the information from Exhibit 4-B, calculate contribution margin, contribution margin ratio, perform a breakeven analysis, and perform a sensitivity analysis using operating leverage. Unless otherwise specified, the information will be taken from the amounts appearing in Exhibits 4-A and Exhibit 4-B. 1. Compute the TOTAL VARIABLE COSTS PER UNIT as follows: a. Total projected annual variable costs from Exhibit 4-B b. Total projected units from Exhibit 4-A c. Total Variable Cost per unit (la + lb) $ per unit (Round to 4 decimal places) 2. Compute CONTRIBUTION MARGIN PER UNIT using sales price per unit and variable cost per unit (Round to 4 decimal places): 3. Compute the CONTRIBUTION MARGIN RATIO (Round to 4 decimal places): 4. Compute the BREAKEVEN IN UNITS (Round up to the nearest whole number): 5. Compute the BREAKEVEN IN SALES DOLLARS (Round up to the nearest dollar): 6. Compute the NUMBER OF UNITS THAT MUST BE SOLD to reach a targeted profit of $152,000 taking into consideration the total projected fixed costs (Round to the nearest whole number. Hint: Use the breakeven unit formula): 7. How MANY SALES DOLLARS MUST BE GENERATED to earn an annual operating income of $300,000? (Round to the nearest dollar. Hint: Use Breakeven in Sales $ formula) 8. Calculate the company's DEGREE OF OPERATING LEVERAGE based on the proposed budget (Round to 4 decimal places): 9. If sales in units increase by 10% what would be the NEW PROJECTED OPERATING INCOME? (Use degree of operating leverage calculated in #8. Round final answer to nearest dollar) 10. If sales in units decreased by 15% what would be the NEW PROJECTED OPERATING INCOME? (Round final answer to nearest dollar.)