I need help solving this lab. I tried to work on it and have solved some of it, so I want to see if its right and how to solve the rest. It connects to lab 1, 4, and 6. I hahe posted all of the requirements please help get all of the parts done. Thank you!

( I have attached the pictures below )

COMBINED CASH BUDGET LAB 7 INOW that the operating budgets are complete, Tonia and Tara prepare a combined cash budget for upcoming year. Based on the forecasted cash balances each month, the company can predict if they will have to borrow money at any time during the next quarter. Using information from Labs 1.4 and 6. prepare the cash forecasts for the months of January, February and March All sales are on account. Past history shows the following collection patterns: 10% in month of sale and 90% in the month following. Bad debts are negligible. December sales are projected to be $190,000. Direct labor and all salaries and wages are paid one-half in the month incurred and the second half in the following month. Wages and salaries for December were: Direct Labor $22,888 Other Salaries and Wages: Executive Salaries $13,838 Plant Manager $5,667 Plant Maintenance Supervisor $2,667 Sales Commissions $917 Sales Salaries $950 Rent is paid on the 10th of each month. Direct materials are paid on the 15th of the month following purchase. The total cost of direct materials in December was $25,000_Total insurance (both liability and plant) premiums of S23,009-are paid on January 10. All other variable and fixed overhead costs are paid in the month following. Other costs for the month of December are: Other variable costs $ 8,483 Other fixed costs $30,000 T.O.T.E.S. CASE STUDY-WHITE CLOUDS company maintains a cash balance of $130,000 at all times, even if it requires borrowing. The cash ce at the end of December is $135,000. If the forecasted cash balance is going to fall below the required minimum, the company has an operating line of credit, which it can draw on at any time. Kepayments are made on the first day of each quarter... January 1, April 1. July 1 and October Interest Expense can be ignored at this time. Prepare Exhibit 7-A using information from previous labs and the worksheets to help with calculations 42 T.O.T.E.S. CASE STUDY-WHITE CLOUDS Worksheets 7-1: Worksheets for Cash Budget Calculations Round all amounts to the nearest dollar except "Sales Price per bag." Worksheet 1 Calculate Sales Revenue per month Data from Company Overw tab ) January February March Sales Price per bag Total Sales in Dollars 5.05 5.05 5-05 22.100217.150222,200 January February March Worksheet 2: se information from Lab, Exhibit 6-F) Direct Labor Wages Earned in December Enter total for each month Determine monthly Direct Labor Costs Calculate Amount Paid in each month. For Previous Month For Current Month Total Direct Labor Paid Paid in lanuary Paid in February Paid in March Worksheet 3 Calculate monthly Salaries and Wages Expense other than Direct Labor Given in Lab December Executive Salaries 113.858 Plant Manager alat Plant Maintenance Supervisor Sales Commisions 12 Sales Salaries 950 Ecom.xd4-A Annual Budget NA 50 CAS,000 32,000 JO1000 12.000 Annual Total 392.650 Monthly Salary Cost 131488 Total for the month of December 140A Complete Exhibit 7-A through rent BEFORE continuing Worksheet 4 Other Variable costs 4-A) (Excluding all variable costs that have already been accounted for there are 4 variable costs remaining) Total Other Variable Costs + by 12 = Monthly Worksheets Other Fixed Costs (Exhibit 4-A) (Excluding all fixed costs that have already been accounted for there are 10 fixed costs remaining.) Total Fixed Costs + by 12 - Monthly 4200 +50,000 + 26,000+ T.O.T.ES. CASE STUDY - WHITE CLOUDS EXHIBIT 7-A: Combined Cash Budget 20xx T.O.T.ES Combined Cash Budget - 20xx January 135.000 February March 130.000 TL Beginning Cash: Sales Dollars Cash Receipts: Worksheet 1) December Sales 140,000 January Sales 1212,100 February Sales 215 150 March Sales 222, 200 Total Receipts Total Cash Available 71,000 904 21.210 190,890 9021 21715 102199.436 90 2222002 142210212605 217655 31988 31888 89,698 85.691 Cash Disbursements: Direct Labor (Worksheet2) Other Salaries & Wages (Worksheet 3) Direct Materials (Exhibit 6-E) Liability Insurance Mabilly and plant) Total Rent A Nint calculate monthly Other Variable Costs/Worksheet 4) Other Fixed Costs (Worksheet 5) Total Disbursements Ending Cash Balance before financing Plus: New borrowings Less: Payments Ending Cash Balance 23,000 95,000 8483 30,003 24,039 -12019.5. 27963.5 2277964 BC 31888 15944 130,000 - T.O.T.E.S. CASE STUDY-WHITE CLOUDS X X X EXHIBIT 1-A: Classify costs as Period or Product (DM, DL or MOH) T.O.T.E.S. CLASSIFY COST Estimated Revenues And Expenses For Upcoming Year futon in the correct column) Forecasted Sales in units 500,000 Period Product Cost AMOUNT Cost DM DL ACCOUNT NAME MOH Sales Revenue $2,525,000 Expenses Administrative Office Supplies 3,600 X Administrative Staff, e.g accountant, etc. 125,000 X Adminstrative Payroll and Fringe 119,400 X Advertising. Trade Shows, Travel & Entertainment 46,000 X 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 169,650 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 X Logo Ink 26,000 Plant Maintenance Supervisor 32,000 Plant Manager 68,000 Rent - Administrative Building 11,000 X 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 88,000 Wages - Printing Department 8,250 Wages - Sewing Department 176,000 Webbing 540,000 XXXX X XX Hint: There are 15 product costs, 5 of which are direct. T.O.T.E.S. CASE STUDY-WHITE CLOUDS Use Excelle for Lab 1 (found on Blackboard) BEFORE completing Exhibit 1-8. Bring file to lab so that you can correct any errors. Summarize the results on Exhibit 1-B below. 2,525,000 EXHIBIT 1-B: Operating Budget Summary HINTS: T.O.T.E.S. Use Excel file Operating Budget Summary - 20XX to determine totals. at Standard SALES REVENUES Include all I COST OF GOODS SOLD (COGS) PRODUCT Direct Materials 1.140,000 costs in COGS for a Direct Labor 272 250 traditional Manufacturing Overhead (MOH) 308,800, income statement. TOTAL COST OF GOODS SOLD (PRODUCT COSTS) 1,721,050 GROSS MARGIN SELLING & ADMINSTRATIVE COSTS Include all Total Selling 199 350 PERIOD expenses Total Administrative 452,600 TOTAL SELLING & ADMIN. COSTS (PERIOD COSTS) 651,950 OPERATING INCOME (LOSS) 152.000 803,950 JVC CONTRIBUTION MARGIN/COST/VOLUME/PROFIT ANALYSIS 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 1 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. Month January February March Utility Expenses for Prior Year Machine Cost of .. Machine Month Cost of Hours Utilities 400 $ 3,157 July 284 $ 2,611 415 $ 3,228 August 291 $ 2,644 420 $ 3,251 September 310 $ 2,734 450 $ 3,392 October 340 $ 2,875 283 $ 2,607 November 350 $ 2,922 260 $ 2,498 December 395 $ 3,133 April May June T.O.T.E.S. CASE STUDY - WHITE CLOUDS 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, eg machine-hours. Use the data from Utility Expenses for Prior Year. High Month: April Low Month: June Step 2: Calculate yariable cost per machine hour (round to the penny) using the high-low method. Total cost of hiah - Total cost of low Highest activity Unit - lowest actity unit 3,392-2,498 894 -4.715 / 450 - 260 190 Step 3: Calculate fixed cost per month (Round to nearest dollar) using the cost formula and monthly data. Write the cost formula FIRST. TC = VC TFC FC = Tc TVC = 3,392 - (4.71 X 450) = 3,392 - 2119.5 Loudon FC = 1273 Step 4: Calculate fixed cost per year (round to nearest dollar) using result from Step 3. = 1273 X 12 -19276 Step 5: Calculate the annual total variable cost using the cost formula (Round to nearest dollar). (Use BUDGET NOT prior year data to calculate.) 38,000 - 15276 2 = 22724 u T.O.T.ES, CASE STUDY-WHITE CLOUDS EXHIBIT 4-A: Determine Variable and Fixed Costs IDENTIFY IF COST IS T.O.T.E.S. VARIABLE OR FIXED (put an X in the correct column) Estimated Revenues And Expenses For Upcoming Year Forecasted Sales in units 500,000 VARIABLE FIXED AMOUNT ACCOUNT NAME COST COST Sales Revenue $2,525,000 Expenses Administrative Office Supplies 3,600 Administrative Staff, e.g accountant, etc. 125,000 Adminstrative Payroll and Fringe 119,400 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 169,650 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 88,000 Wages - Printing Department 8,250 Wages - Sewing Department 176,000 Webbing 540,000 Hint: There are 10 variable costs, including the variable portion of the mixed cost item. xxxx xx xx xx xxx T.O.T.E.S. CASE STUDY-WHITE CLOUDS PART 2: Complete the Contribution Format Income Statement with ALL accounts listed usme the Excel worksheet found on Blackboard Complete Exhibit Busine the totals from the Excel Pro Torma Contribution Format Income Statement. Verify all totals are correct PRIOR to starting art 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 $ 2,525 000 TOTAL VARIABLE COSTSX 1,616, 174 CONTRIBUTION MARGIN $ 908826 TOTAL FIXED COSTS X 756 826 OPERATING INCOME (LOSS) $ 152000 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 1. . 4 b. Total projected units from Exhibit 4-A 500,000 c. Total Variable Cost per unit (la + lb) $ 3.2323 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): sp(per unit) - VC (per unit) 5.05 3.2323 = 1.8177 3. Compute the CONTRIBUTION MARGIN RATIO (Round to 4 decimal places): contribution Margin 1.8177 = 0.3599 sales 5.05 26 T.O.T.E.S. CASE STUDY - WHITE CLOUDS 4. Compute the BREAKEVEN IN UNITS (Round up to the nearest whole number): Fixed expenses 7566826 CM per unit 1.8177 =416364-6366 2 4 16365 5. Compute the BREAKEVEN IN SALES DOLLARS (Round up to the nearest dollar): fixed expenses - 756826 -2102878.577. CM Ratio 0.3599 72102879 6. Compute the NUMBER OF UNITS THAT MUST BE SOLD to reach a targeted profit of S152,000 taking into consideration the total projected fixed costs (Round to the nearest whole number. Hint: Use the breakeven unit formula): Fixed cost - 756,826 + 152,000 - 499986.7965 CM per unit 1.8177 499987 - ORD 7. How MANY SALES DOLLARS MUST BE GENERATED to car an annual operating income of $300,000? (Round to the nearest dollar. Hint: Use Breakeven in Sales S formula) Fixed expenses - 7510,826 + 300,000 CM Ratio 10.3599 = 2936443.457 27 N 2936443 T.O.T.E.S. CASE STUDY-WHITE CLOUDS 8. Calculate the company's DEGREE OF OPERATING LEVERAGE based on the proposed budget (Round to 4 decimal places): CM Lsales - UE) - 900826 Net operating income- 152,000 -5.9791 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) = 5.9791 x 107 -0.59791 X 152,000 9088232 + 152,000 = 242882.32 7 242882 10. If sales in units decreased by 15% what would be the NEW PROJECTED OPERATING INCOME? (Round final answer to nearest dollar.) = 5.9791 X (15%) = -0.896865 X 152 000 --136323.48+ 152, ooo = 15676.52 15672 OPERATING BUDGETS LAB 6 As the current year comes to a close, the accounting department, with assistance from senior management and the sales department, prepares the various budgets necessary to prepare the cash forecast (Lab 7). A monthly production budget is developed using the sales forecast in units (20XX sales forecast is presented below in Exhibit 6-A) as provided by the marketing and sales staff. totes EXHIBIT 6-A: Sales Forecast for 20xx January 42,000 totes February 43,000 totes 46,000 47,000 totes March 44,000 totes totes July August September October November December 47,000 47,000 45,000 totes totes April May June totes 46,000 totes 46,000 totes 48,000 49,000 totes Raw material needs are determined based on the production budget and the company's desired inventory levels. INVENTORY ON DECEMBER 31, PRIOR YEAR: Company employees completed an inventory count on December 31 determining the following ending inventory balances: o 5,000 finished totes o 600 yards of fabric 7,000 yards of webbing T.O.T.E.S. CASE STUDY-WHITE CLOUDS Using the sales forecast (Exhibit 6-A), the inventory requirements described A), the inventory requirements described in the section wpany Description and Overview", and the Standard Cost Card (Exhibit 2-B) prepare the Production Budget oduction Budget (Exhibit 6-B), the Direct Materials Budget for fabric (Exhibit 6-C), the Direct Materials Budget for webbing (Exhibit 6-D), the Raw Materials Purchases Budget (Exhibit 6-) and the Projected Costs of Goods Manufactured Schedule for the 1M Quarter of 20XX (Exhibit 6-F). EXHIBIT 6-B: Production Budget for 1" Quarter 20XX T.O.T.E.S. Production Budget for the 1st quarter 20xx January February March April May Projected Sales in units (given) 42,000 43,000 44,000 45,000 46,000 Required Ending Inventory (20 %) 8600 8800 19000 9200 Total 50,600 V51,800 53.000 54,200 Less: Beginning Inventory 5000 8600 88009060 Total Required Production 45,606 43200 44,200 45,200 EXHIBIT 6-C: Raw materials Budget 20XX - FABRIC + T.O.T.E.S. Raw Materials Budget 20XX - FABRIC January February March April Projected Production Requirements in units 45.600 43,200 44.20045,200 from Exhibit 6-B) Yards of fabric per tote 10.25 0.25 0.25 0.25 11,400 10,800 11,050 11,300 540 553 565 * Required Ending Inventory ( 5 %) 11,940 11353|1,615 Less: Beginning Inventory 600 540 553 Total Required Fabric Purchases (yards) 11,340 10,813 11.062 Standard Cost per yard of fabric 15 3.60 3.60 3.60 Total cost of fabric (round to the dollar) s 46,824 $38 927 $ 39,823 Total 38 T.O.T.ES. CASE STUDY-WHITE CLOUDS EXHIBIT 6-D: Raw materials Budget 20xx - WEBBING T.O.T.ES. Raw Materials Budget 20% - WEBBING January February March April Projected Production Requirements in units 45,600 43,200 44,200 45,200 1.50 Yards of webbing per tote 1.50 1.50 1.50 Total of yards needed 68,400,64,800 166,30067,800 Required Ending inventory 10 29 6480 16630 6790 74,880 71,430, 73,080 Total Less: Beginning Inventory 7,000 6480 6630 Total Required Webbing Purchases (yards) 67,880 64,956 66,450 Is 0.72 s 0.72 s 0.72 Total cost of webbing (round to the dollar) $ 48,874 546,764 $47,844 EXHIBIT 6-E: Raw Materials Purchases Budget - Quarter 1, 20XX T.O.T.E.S. Raw Materials Purchases Budget - Quarter 1, 20% January February March Quarter Total Total Cost of Fabric (from Exhibit 6-0 $ 40,824 $ 38,927 $ 39,823 s119574 Total Cost of Webbing (from Exhibit 6D) $48,874 $ 46,764 $ 47,844 $143482 Total Cost of Direct Materials s 89698 5 85691 587667 s263056 EXHIBIT 6-F: Projected Cost of Goods Manufactured Schedule - Quarter 1, 20XX T.O.T.E.S. Project Cost of Goods Manufactured Schedule - Quarter 20XX January February March Required Production in units ( 0) 45,600 43,200 44,200 Cost per unit fround to Total Cost deco round the rest doller Direct Materials Cost perut $2.2800 5103968 3 98496 $100776 Direct Labor Cost per un s 0.5945 $24829 $23522 524667 Manufacturing Overhead (Cost perunt 30.6176 $28163 326680 $27298 Total Costs $3.4421 5156960 $148698 5152141 COMBINED CASH BUDGET LAB 7 INOW that the operating budgets are complete, Tonia and Tara prepare a combined cash budget for upcoming year. Based on the forecasted cash balances each month, the company can predict if they will have to borrow money at any time during the next quarter. Using information from Labs 1.4 and 6. prepare the cash forecasts for the months of January, February and March All sales are on account. Past history shows the following collection patterns: 10% in month of sale and 90% in the month following. Bad debts are negligible. December sales are projected to be $190,000. Direct labor and all salaries and wages are paid one-half in the month incurred and the second half in the following month. Wages and salaries for December were: Direct Labor $22,888 Other Salaries and Wages: Executive Salaries $13,838 Plant Manager $5,667 Plant Maintenance Supervisor $2,667 Sales Commissions $917 Sales Salaries $950 Rent is paid on the 10th of each month. Direct materials are paid on the 15th of the month following purchase. The total cost of direct materials in December was $25,000_Total insurance (both liability and plant) premiums of S23,009-are paid on January 10. All other variable and fixed overhead costs are paid in the month following. Other costs for the month of December are: Other variable costs $ 8,483 Other fixed costs $30,000 T.O.T.E.S. CASE STUDY-WHITE CLOUDS company maintains a cash balance of $130,000 at all times, even if it requires borrowing. The cash ce at the end of December is $135,000. If the forecasted cash balance is going to fall below the required minimum, the company has an operating line of credit, which it can draw on at any time. Kepayments are made on the first day of each quarter... January 1, April 1. July 1 and October Interest Expense can be ignored at this time. Prepare Exhibit 7-A using information from previous labs and the worksheets to help with calculations 42 T.O.T.E.S. CASE STUDY-WHITE CLOUDS Worksheets 7-1: Worksheets for Cash Budget Calculations Round all amounts to the nearest dollar except "Sales Price per bag." Worksheet 1 Calculate Sales Revenue per month Data from Company Overw tab ) January February March Sales Price per bag Total Sales in Dollars 5.05 5.05 5-05 22.100217.150222,200 January February March Worksheet 2: se information from Lab, Exhibit 6-F) Direct Labor Wages Earned in December Enter total for each month Determine monthly Direct Labor Costs Calculate Amount Paid in each month. For Previous Month For Current Month Total Direct Labor Paid Paid in lanuary Paid in February Paid in March Worksheet 3 Calculate monthly Salaries and Wages Expense other than Direct Labor Given in Lab December Executive Salaries 113.858 Plant Manager alat Plant Maintenance Supervisor Sales Commisions 12 Sales Salaries 950 Ecom.xd4-A Annual Budget NA 50 CAS,000 32,000 JO1000 12.000 Annual Total 392.650 Monthly Salary Cost 131488 Total for the month of December 140A Complete Exhibit 7-A through rent BEFORE continuing Worksheet 4 Other Variable costs 4-A) (Excluding all variable costs that have already been accounted for there are 4 variable costs remaining) Total Other Variable Costs + by 12 = Monthly Worksheets Other Fixed Costs (Exhibit 4-A) (Excluding all fixed costs that have already been accounted for there are 10 fixed costs remaining.) Total Fixed Costs + by 12 - Monthly 4200 +50,000 + 26,000+ T.O.T.ES. CASE STUDY - WHITE CLOUDS EXHIBIT 7-A: Combined Cash Budget 20xx T.O.T.ES Combined Cash Budget - 20xx January 135.000 February March 130.000 TL Beginning Cash: Sales Dollars Cash Receipts: Worksheet 1) December Sales 140,000 January Sales 1212,100 February Sales 215 150 March Sales 222, 200 Total Receipts Total Cash Available 71,000 904 21.210 190,890 9021 21715 102199.436 90 2222002 142210212605 217655 31988 31888 89,698 85.691 Cash Disbursements: Direct Labor (Worksheet2) Other Salaries & Wages (Worksheet 3) Direct Materials (Exhibit 6-E) Liability Insurance Mabilly and plant) Total Rent A Nint calculate monthly Other Variable Costs/Worksheet 4) Other Fixed Costs (Worksheet 5) Total Disbursements Ending Cash Balance before financing Plus: New borrowings Less: Payments Ending Cash Balance 23,000 95,000 8483 30,003 24,039 -12019.5. 27963.5 2277964 BC 31888 15944 130,000 - T.O.T.E.S. CASE STUDY-WHITE CLOUDS X X X EXHIBIT 1-A: Classify costs as Period or Product (DM, DL or MOH) T.O.T.E.S. CLASSIFY COST Estimated Revenues And Expenses For Upcoming Year futon in the correct column) Forecasted Sales in units 500,000 Period Product Cost AMOUNT Cost DM DL ACCOUNT NAME MOH Sales Revenue $2,525,000 Expenses Administrative Office Supplies 3,600 X Administrative Staff, e.g accountant, etc. 125,000 X Adminstrative Payroll and Fringe 119,400 X Advertising. Trade Shows, Travel & Entertainment 46,000 X 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 169,650 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 X Logo Ink 26,000 Plant Maintenance Supervisor 32,000 Plant Manager 68,000 Rent - Administrative Building 11,000 X 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 88,000 Wages - Printing Department 8,250 Wages - Sewing Department 176,000 Webbing 540,000 XXXX X XX Hint: There are 15 product costs, 5 of which are direct. T.O.T.E.S. CASE STUDY-WHITE CLOUDS Use Excelle for Lab 1 (found on Blackboard) BEFORE completing Exhibit 1-8. Bring file to lab so that you can correct any errors. Summarize the results on Exhibit 1-B below. 2,525,000 EXHIBIT 1-B: Operating Budget Summary HINTS: T.O.T.E.S. Use Excel file Operating Budget Summary - 20XX to determine totals. at Standard SALES REVENUES Include all I COST OF GOODS SOLD (COGS) PRODUCT Direct Materials 1.140,000 costs in COGS for a Direct Labor 272 250 traditional Manufacturing Overhead (MOH) 308,800, income statement. TOTAL COST OF GOODS SOLD (PRODUCT COSTS) 1,721,050 GROSS MARGIN SELLING & ADMINSTRATIVE COSTS Include all Total Selling 199 350 PERIOD expenses Total Administrative 452,600 TOTAL SELLING & ADMIN. COSTS (PERIOD COSTS) 651,950 OPERATING INCOME (LOSS) 152.000 803,950 JVC CONTRIBUTION MARGIN/COST/VOLUME/PROFIT ANALYSIS 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 1 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. Month January February March Utility Expenses for Prior Year Machine Cost of .. Machine Month Cost of Hours Utilities 400 $ 3,157 July 284 $ 2,611 415 $ 3,228 August 291 $ 2,644 420 $ 3,251 September 310 $ 2,734 450 $ 3,392 October 340 $ 2,875 283 $ 2,607 November 350 $ 2,922 260 $ 2,498 December 395 $ 3,133 April May June T.O.T.E.S. CASE STUDY - WHITE CLOUDS 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, eg machine-hours. Use the data from Utility Expenses for Prior Year. High Month: April Low Month: June Step 2: Calculate yariable cost per machine hour (round to the penny) using the high-low method. Total cost of hiah - Total cost of low Highest activity Unit - lowest actity unit 3,392-2,498 894 -4.715 / 450 - 260 190 Step 3: Calculate fixed cost per month (Round to nearest dollar) using the cost formula and monthly data. Write the cost formula FIRST. TC = VC TFC FC = Tc TVC = 3,392 - (4.71 X 450) = 3,392 - 2119.5 Loudon FC = 1273 Step 4: Calculate fixed cost per year (round to nearest dollar) using result from Step 3. = 1273 X 12 -19276 Step 5: Calculate the annual total variable cost using the cost formula (Round to nearest dollar). (Use BUDGET NOT prior year data to calculate.) 38,000 - 15276 2 = 22724 u T.O.T.ES, CASE STUDY-WHITE CLOUDS EXHIBIT 4-A: Determine Variable and Fixed Costs IDENTIFY IF COST IS T.O.T.E.S. VARIABLE OR FIXED (put an X in the correct column) Estimated Revenues And Expenses For Upcoming Year Forecasted Sales in units 500,000 VARIABLE FIXED AMOUNT ACCOUNT NAME COST COST Sales Revenue $2,525,000 Expenses Administrative Office Supplies 3,600 Administrative Staff, e.g accountant, etc. 125,000 Adminstrative Payroll and Fringe 119,400 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 169,650 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 88,000 Wages - Printing Department 8,250 Wages - Sewing Department 176,000 Webbing 540,000 Hint: There are 10 variable costs, including the variable portion of the mixed cost item. xxxx xx xx xx xxx T.O.T.E.S. CASE STUDY-WHITE CLOUDS PART 2: Complete the Contribution Format Income Statement with ALL accounts listed usme the Excel worksheet found on Blackboard Complete Exhibit Busine the totals from the Excel Pro Torma Contribution Format Income Statement. Verify all totals are correct PRIOR to starting art 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 $ 2,525 000 TOTAL VARIABLE COSTSX 1,616, 174 CONTRIBUTION MARGIN $ 908826 TOTAL FIXED COSTS X 756 826 OPERATING INCOME (LOSS) $ 152000 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 1. . 4 b. Total projected units from Exhibit 4-A 500,000 c. Total Variable Cost per unit (la + lb) $ 3.2323 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): sp(per unit) - VC (per unit) 5.05 3.2323 = 1.8177 3. Compute the CONTRIBUTION MARGIN RATIO (Round to 4 decimal places): contribution Margin 1.8177 = 0.3599 sales 5.05 26 T.O.T.E.S. CASE STUDY - WHITE CLOUDS 4. Compute the BREAKEVEN IN UNITS (Round up to the nearest whole number): Fixed expenses 7566826 CM per unit 1.8177 =416364-6366 2 4 16365 5. Compute the BREAKEVEN IN SALES DOLLARS (Round up to the nearest dollar): fixed expenses - 756826 -2102878.577. CM Ratio 0.3599 72102879 6. Compute the NUMBER OF UNITS THAT MUST BE SOLD to reach a targeted profit of S152,000 taking into consideration the total projected fixed costs (Round to the nearest whole number. Hint: Use the breakeven unit formula): Fixed cost - 756,826 + 152,000 - 499986.7965 CM per unit 1.8177 499987 - ORD 7. How MANY SALES DOLLARS MUST BE GENERATED to car an annual operating income of $300,000? (Round to the nearest dollar. Hint: Use Breakeven in Sales S formula) Fixed expenses - 7510,826 + 300,000 CM Ratio 10.3599 = 2936443.457 27 N 2936443 T.O.T.E.S. CASE STUDY-WHITE CLOUDS 8. Calculate the company's DEGREE OF OPERATING LEVERAGE based on the proposed budget (Round to 4 decimal places): CM Lsales - UE) - 900826 Net operating income- 152,000 -5.9791 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) = 5.9791 x 107 -0.59791 X 152,000 9088232 + 152,000 = 242882.32 7 242882 10. If sales in units decreased by 15% what would be the NEW PROJECTED OPERATING INCOME? (Round final answer to nearest dollar.) = 5.9791 X (15%) = -0.896865 X 152 000 --136323.48+ 152, ooo = 15676.52 15672 OPERATING BUDGETS LAB 6 As the current year comes to a close, the accounting department, with assistance from senior management and the sales department, prepares the various budgets necessary to prepare the cash forecast (Lab 7). A monthly production budget is developed using the sales forecast in units (20XX sales forecast is presented below in Exhibit 6-A) as provided by the marketing and sales staff. totes EXHIBIT 6-A: Sales Forecast for 20xx January 42,000 totes February 43,000 totes 46,000 47,000 totes March 44,000 totes totes July August September October November December 47,000 47,000 45,000 totes totes April May June totes 46,000 totes 46,000 totes 48,000 49,000 totes Raw material needs are determined based on the production budget and the company's desired inventory levels. INVENTORY ON DECEMBER 31, PRIOR YEAR: Company employees completed an inventory count on December 31 determining the following ending inventory balances: o 5,000 finished totes o 600 yards of fabric 7,000 yards of webbing T.O.T.E.S. CASE STUDY-WHITE CLOUDS Using the sales forecast (Exhibit 6-A), the inventory requirements described A), the inventory requirements described in the section wpany Description and Overview", and the Standard Cost Card (Exhibit 2-B) prepare the Production Budget oduction Budget (Exhibit 6-B), the Direct Materials Budget for fabric (Exhibit 6-C), the Direct Materials Budget for webbing (Exhibit 6-D), the Raw Materials Purchases Budget (Exhibit 6-) and the Projected Costs of Goods Manufactured Schedule for the 1M Quarter of 20XX (Exhibit 6-F). EXHIBIT 6-B: Production Budget for 1" Quarter 20XX T.O.T.E.S. Production Budget for the 1st quarter 20xx January February March April May Projected Sales in units (given) 42,000 43,000 44,000 45,000 46,000 Required Ending Inventory (20 %) 8600 8800 19000 9200 Total 50,600 V51,800 53.000 54,200 Less: Beginning Inventory 5000 8600 88009060 Total Required Production 45,606 43200 44,200 45,200 EXHIBIT 6-C: Raw materials Budget 20XX - FABRIC + T.O.T.E.S. Raw Materials Budget 20XX - FABRIC January February March April Projected Production Requirements in units 45.600 43,200 44.20045,200 from Exhibit 6-B) Yards of fabric per tote 10.25 0.25 0.25 0.25 11,400 10,800 11,050 11,300 540 553 565 * Required Ending Inventory ( 5 %) 11,940 11353|1,615 Less: Beginning Inventory 600 540 553 Total Required Fabric Purchases (yards) 11,340 10,813 11.062 Standard Cost per yard of fabric 15 3.60 3.60 3.60 Total cost of fabric (round to the dollar) s 46,824 $38 927 $ 39,823 Total 38 T.O.T.ES. CASE STUDY-WHITE CLOUDS EXHIBIT 6-D: Raw materials Budget 20xx - WEBBING T.O.T.ES. Raw Materials Budget 20% - WEBBING January February March April Projected Production Requirements in units 45,600 43,200 44,200 45,200 1.50 Yards of webbing per tote 1.50 1.50 1.50 Total of yards needed 68,400,64,800 166,30067,800 Required Ending inventory 10 29 6480 16630 6790 74,880 71,430, 73,080 Total Less: Beginning Inventory 7,000 6480 6630 Total Required Webbing Purchases (yards) 67,880 64,956 66,450 Is 0.72 s 0.72 s 0.72 Total cost of webbing (round to the dollar) $ 48,874 546,764 $47,844 EXHIBIT 6-E: Raw Materials Purchases Budget - Quarter 1, 20XX T.O.T.E.S. Raw Materials Purchases Budget - Quarter 1, 20% January February March Quarter Total Total Cost of Fabric (from Exhibit 6-0 $ 40,824 $ 38,927 $ 39,823 s119574 Total Cost of Webbing (from Exhibit 6D) $48,874 $ 46,764 $ 47,844 $143482 Total Cost of Direct Materials s 89698 5 85691 587667 s263056 EXHIBIT 6-F: Projected Cost of Goods Manufactured Schedule - Quarter 1, 20XX T.O.T.E.S. Project Cost of Goods Manufactured Schedule - Quarter 20XX January February March Required Production in units ( 0) 45,600 43,200 44,200 Cost per unit fround to Total Cost deco round the rest doller Direct Materials Cost perut $2.2800 5103968 3 98496 $100776 Direct Labor Cost per un s 0.5945 $24829 $23522 524667 Manufacturing Overhead (Cost perunt 30.6176 $28163 326680 $27298 Total Costs $3.4421 5156960 $148698 5152141