Jay Corporation has provided data from a two-year period to aid in planning. The Controller has asked you to prepare a contribution format income statement. Use the information included in the Excel Simulation and the Excel functions described below to complete the task. 国日High-Low Method; Contribution Format Income Statement - ExcelFILEHOMEINSERTPAGE LAYOUTFORMULASDATAREVIEWVIEWSign InCalibri-11A APasteBIU -Alignment NumberConditional Format as CellCellsEditingFormatting -Table- StylesClipboardFontStylesЕ11 To ald in planning, Jay Corporation is preparing a contribution format income statement.Budgeted information for Quarter l of Year 3:Sales in units17,000Sales price per unit48.007 After analyzing expenses, the company has determined the following cost patterns.8 Cost of goods sold9 Sales commissions29.00 per unit9.50% per dollar of sales1011 Administrattve salaries12 Rent expense13 Depreciation expense45,000.00 per quarter27,000.00 per quarter36,000.00 per quarter1415 Management has concluded that shipping oxpense is a mixed cost. Units shipped and the related shippling cost overthe last cight quarters are:16JOtal Shipping Cost67,000.0017 Year 118 Quarter 119 Quarter220 Quarter 321 Quarter 422 Year 223 Quarter 124 Quarter225 Quarter 326 Quarter 4Lnits12,50094,000.0021,00089,800.0013,80092,600.0020,00072,500.0013,70080,000.0084,000.0014,00014,300100,000.0022,5002728 Use the data to anawer the following2930 1. Using the high low method, determine a cost formula for shipping expenses31Sheet1......READY+10%Attempt(s)Hint囲 High-Low Method; Contribution Format Income Statement - ExcelSign InFILEHOMEINSERTPAGE LAYOUTFORMULASDATAREVIEWVIEWCalibri-11APasteBIU -Alignment Number Conditional Format asCellCellsEditingFormatting - Table - Styles-ClipboardFontStylesЕ132Total Shipping CostUnits33 High level af activity34 Low level of activity35 Change in activity3637 Variable costper unit3839 Compute the fxed cost portion using the high level of activity.40 Total cost at high level of activity41 Less: Variable cost element42 Total fixed cost4344 2. Prepare a contribution format income statement for Quarter 1 of Year 3.4546Jay CorporationBudgetnd Contribution Format Income StatementFor Year 3, Quarter 1474849so Sales51 Variable cxpenses:52 Cost of goods sold53 Sales commissions54 Shipping expense55 Total variable expenses56 Contributon margin57 Fed expenses:58 Administrattve salaries50 Rent expense60 Shipping expense61 Depreciation expense62 Total fed expenses63 Net operatting incomeSheet1...READY10%Attempt(s)Hint囲

Contribution margin=Sales-Total variable expensesNet operating income=Contribution margin-Total…

E1 1 To ald in planning, Jay Corporation is preparing a contributon format income statement. 3 Budgeted Information for Quarter 1 of Year 3: Sales in units 17,000 5 Sales price per unit 48.00 7 After analyzing expenses, the company has determined the following cost patterns. 8 Cost of goods sold 29.00 per unit Sales commissions 9.5O% per dollar of sales 10 Administrative salaries Rent oxpense 11 45,000.00 per quarter 12 27,000.00 per quarter 13 Depreciation expense 36,000.00 per quarter 15 Management has concluded that shipping oxpense is a mixed cost. Units shipped and the related shipping cost over the last eight quarters are: 16 17 Year 1 Jotal Shipping Cont 67,000.00 Units 18 Quarter 1 19 Quarter2 20 Quarter 3 12,500 94,000.00 21,000 89,800.00 13,800 21 Quarter 4 92,600.00 20,000 22 Year 2 23 Quarter 1 24 Quarter2 72,500.00 13,700 80,000.00 14,000 25 Quarter 3 34,000.00 14,300 26 Quarter 4 27 100,000.00 22,500 28 Use the data to anower the folowing 29 30 1. Using the high low method, determine a cost formula for shipping expenses 31 SSS

E1 1 To ald in planning, Jay Corporation is preparing a contributon format income statement. 3 Budgeted Information for Quarter 1 of Year 3: Sales in units 17,000 5 Sales price per unit 48.00 7 After analyzing expenses, the company has determined the following cost patterns. 8 Cost of goods sold 29.00 per unit Sales commissions 9.5O% per dollar of sales 10 Administrative salaries Rent oxpense 11 45,000.00 per quarter 12 27,000.00 per quarter 13 Depreciation expense 36,000.00 per quarter 15 Management has concluded that shipping oxpense is a mixed cost. Units shipped and the related shipping cost over the last eight quarters are: 16 17 Year 1 Jotal Shipping Cont 67,000.00 Units 18 Quarter 1 19 Quarter2 20 Quarter 3 12,500 94,000.00 21,000 89,800.00 13,800 21 Quarter 4 92,600.00 20,000 22 Year 2 23 Quarter 1 24 Quarter2 72,500.00 13,700 80,000.00 14,000 25 Quarter 3 34,000.00 14,300 26 Quarter 4 27 100,000.00 22,500 28 Use the data to anower the folowing 29 30 1. Using the high low method, determine a cost formula for shipping expenses 31 SSS

Quickbooks Online Accounting

3rd Edition

ISBN:9780357391693

Author:Owen

Publisher:Owen

Chapter8: Budgets And Bank Reconciliations

Section: Chapter Questions

Problem 2.4C

See similar textbooks

Similar questions

Integrated Excel: Preparing a Contribution Format Income Statement Using the High-Low Method Jay Corporation has provided data from a two-year period to aid in planning. The Controller has asked you to prepare a contribution format income statement. Budgeted information for Quarter 1 of 2024: Sales in units Sales price per unit After analyzing expenses, the company has determined the following cost patterns. Cost of Goods Sold (per unit) Sales Commissions (per dollar of sales). Administrative Salaries (per quarter) Rent Expense (per quarter) Depreciation Expense (per quarter) Shipping has been determined to be a mixed cost with the following total costs and units: 2022 Quarter 1 Quarter 2 Quarter 3 Quarter 4 2023 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total Cost $ 67,000 $ 94,000 $ 89,800 $ 92,600 $ 72,500 $80,000 $ 84,000 $ 100,000 17,000 $ 48.00 Units 12,500 21,000 13,800 20,000 13,700 14,000 14,300 22,500 $29.00 9.50€ $ 45,000 $ 27,000 $ 36,000 Required: Use the data to answer the…
Creat an initial budget sheet showing the sistribution of $100,000 use the example's format on EXCEL.
Howard Cooper, the president of Campbell Computer Services, needs your help. He wonders about the potential effects on the firm's net income if he changes the service rate that the firm charges its customers. The following basic data pertain to fiscal Year 3. Standard rate and variable costs Service rate per hour $ 85.00 Labor cost 37.00 Overhead cost 7.10 Selling, general, and administrative cost Expected fixed costs Facility maintenance Selling, general, and administrative 3.80 $522,000 142,000 Required: a. Prepare the pro forma income statement that would appear in the master budget if the firm expects to provide 45,000 hours of services in Year 3. b. A marketing consultant suggests to Mr. Cooper that the service rate may affect the number of service hours that the firm can achieve. According to the consultant's analysis, if Campbell charges customers $80 per hour, the firm can achieve 50,000 hours of services. Prepare a flexible budget using the consultant's assumption. c. The same…
Required A Required B Prepare the pro forma income statement in contribution format that would appear in a flexible budget. SOLOMON MANUFACTURING COMPANY Pro Forma Income Statement Flexible Budget $ 0
Finance management PnA calculators. The following information was extracted from the budget for the yearended 31 December 2023: $Sales 10 400Selling price per calculator350Variable production cost per unit216Fixed production costs154 000Variable selling and administrative costs per unit29Fixed selling and administrative costs82 000 Calculate the following:a) Break-even quantityb) Break-even value using the marginal income ratioc) Margin of safety (in terms of units)d) Expected total marginal income and net profit
Question Content Area Given the following information, determine the activity rate (rounded to the nearest cent) for setups. Activity Total Activity-Base Usage Budgeted Activity Cost Setups 10,000 $57,200 Inspections 23,400 $149,700 Assembly (dlh) 73,800 $394,500 a. $5.61 b. $5.35 c. $6.40 d. $5.72
7.3 Shown below are the totals from 2018 period budgets. Revenue budget Materials usage from production budget Labor cost budget Manufacturing overhead budget General and administrative budget Capital expenditure budget P100,000 15,000 20,000 20,000 30,000 20,000 32 How Work-in-Progress Inventories: Beginning of 2018 End of 2018 ndilet b rsM x) 1E valM 10,000 lsa 5,000 PLS Finished Goods Iventory: Beginning of 2018 End of 2018 Pla 15,000 10,000 Tax Rate 40% hen dees20 oldibsllonU Instructions: nongt od Prepare a forecasted Income Statement for the year. bas sldigilgon
Given the following information, determine the activity rate (rounded to the nearest cent) for setups. Activity Total Activity-Base Usage Budgeted Activity Cost Setups 11,000 $58,100 Inspections 25,200 $147,200 Assembly (dlh) 72,000 $375,700 a. $5.22 O b. $5.28 c. $5.84 O d. $5.37
Panther Corporation appeared to be experiencing a good year. Sales in the first quarter were one-third ahead of last year, and the sales department predicted that this rate would continue throughout the entire year. The controller asked Janet Nomura, a summer accounting intern, to prepare a draft forecast for the year and to analyze the differences from last year's results. She based the forecast on actual results obtained in the first quarter plus the expected costs of production to be completed in the remainder of the year. She worked with various department heads (production, sales, and so on) to get the necessary information. The results of these efforts follow: PANTHER CORPORATION Expected Account Balances for December 31, Year 2 Cash 6,500 Accounts receivable 337,000 Inventory (January 1, Year 2) Plant and equipment Accumulated depreciation Accounts payable Notes payable (due within one year) Accrued payables 240,000 605,000 $ 181,000 197,000 217,000 110,000 Common stock 450,000…
BUS 203: INTRODUCTION TO COST AND MANAGEMENT ACCOUNTING PROJECT: CASH BUDGETING ASSOCIATE DEGREE YEAR 2: SEMESTER 1 (2020/21)Ronstadt Limited’s budget for the four months from January to April includes the following data:1. MonthSalesMaterialsWagesOverheads $000$000$000$000January615115.030360February636120.033390March690135.036420April684130.040425 2. One-third of sales revenue is received one month after sale and the remainder is received two months after sale. The sales in the previous two months were: November $600 000; December $540 000.3. One-quarter of purchases of materials are paid for in the month of purchase. The remainder are paid for two months later. Purchases in the previous two months were: November $108 000; December $106 000.4. Two-thirds of the wages are paid in the month in which they are earned, and the balance is paid in the following month. The wages for the previous December amounted to $30 000.5. One-half of the overhead expenditure is paid in the month in…
Given the following information, determine the activity rate for setups. Budgeted Activity Activity Setups Inspections Assembly (dlh) Oa. $5.74 Ob. $5.99 Oc. $5.68 Od. $5.61 Total Activity-Base Usage 9,800 23,100 72,000 Cost $58,700 $132,600 $404,100
a) Why do successful companies tend to use the bottom-up approach to establish a master budget? b) Green Tea’s data show the following information for the financial year, beginning July 2020: July Aug. Sept. Oct. Nov. Estimated sales (units) 25,000 25,000 27,000 27,500 28,000 Sales price per unit $31 $31 $31 $31 $31 Direct labour per unit $1.75 $1.75 $1.50 $1.50 $1.50 Labour rate per hour $21 $21 $24 $24 $24 New machinery will be added in September. This machine will reduce the labour required per unit and increase the labour rate for those employees qualified to operate the machinery. Finished goods inventory is required to be 20% of the next month’s requirements. Direct material requires 2.5 kg per unit at a cost of $5 per kg. The ending inventory required for direct materials is 20% of the next month’s needs. In July, the beginning inventory is 3,750 units of finished goods and 13,125 kg of…