cel Assignment 2 Chapters 4-0 1 Assign indirect costs to products or services using activity based costing Help Sar 1 OHS Accoproduced FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW AA Wie Test AT BTV A. Merge Center 5. Conditional Formatas Cel Forming Title Styles In Delta Formu Sert F Art Net Sys A1 Sundial Co, has assigned 571000 in total overhead cost to four different activity controle. The stivity requirements for its two product lines, Economy 11 D 1 1 X 1. Sundial Co has assid 5710.000 in total overhead cost to four different activity coupool. The activity requirements for its two products lines, Economy and Deluxe, we shown below 2 Input formulas to solve for the various unknown in the table below. The formulas may vary for each cost pool depending on wholms are known and knows Activity Requirements by Product 4 Activity Activity.Cost Pool Overhead Cou Activity Deluxe Proportion Activity Cost Driver Activity Rate Economy Model Proportion (6) Medel 5 Material Handling $ 300.000 5000 Material moves per move 2.000 3.000 6 Machine Set-Ups 5 210.000 Setops per set up 2.00 200 7 Engineering Support Engineering hours 12.50 per hour 2.000 205 Power and Space 20,000 square feet per 50 Total overhead cost $ 710 DOO ances 10 Il se formulas to assign overhead to each product using both activity rates and activity proportion (%). 12 Create ifthen statements to verify that the two methods produce the same results 13 14 Overhead Assigned Using Activity Rates Overhead Assigned Using Activity Proportions (6) Verification Create iften statements to determine if the two methods produce identical results by indicating pusor "fail Economy Delut Total Economy Delane Total Economy Deluxe Total 15 Activity Cost Pool 16 Material Handling 17 Machine Set-Ups I Engineering Support 19 Power and Space 1. Analyze a mixed cost using the high-low method. 2. Prepare an income statement for a merchandising company using the contribution format 3 5 ? High-Low Method: Contribution Format Income Statement - Excel PAGE LAYOUT FORMULAS DATA REVIEW VIEW FILE HOME INSERT Sign in 86 Calon - 11 -A A 96 M Paste BIU Cells Editing Alignment Number Conditional Format as Cell Formatting Table Styles Styles sk Clipboard Font A1 v Jay Corporation has decided to prepare contribution income statements for ht D E nces 4 5 11 B Jay Corporation has decided to prepare contribution income statements for internal planning, 2 3 Budgeted information for Quarter 1 of Year 3: Sales in units 17.000 Sales price per unit $ 48.00 6 7 After analyzing expenses, the company has determined the following cost patterns. 8 Variable Cost of Goods Sold (per unit) $ 29.00 9 Sales Commissions (per dollar of sales) 9.50% 10 Fixed Manufacturing Overhead (per quarter) $ 45,000.00 12 Administrative Salaries (per quarter) $ 27.000.00 13 Depreciation Expense (per quarter) $ 36,000.00 14 15 Shipping Cost has been determined to be mixed with the following costs for the past two years: 16 17 Year 1 Total Cost Units 18 Quarter 1 $ 67,000.00 12.500 19 Quarter 2 $ 94,000.00 21.000 20 Quarter 3 $ 89,800.00 13,800 21 Quarter 4 $ 92,600.00 20,000 22 Year 2 23 Quarter 1 $ 72,500.00 13,700 24 Quarter2 $ 80,000.00 14.000 Sheet1 3 E 100% READY Attempt(s) 2 1. Analyze a mixed cost using the high-low method. 2. Prepare an income statement for a merchandising company using the contribution format. - 8.33 points ebook A Print A 14 References High-Low Method: Contribution Format Income Statement - Excel 7 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In Colon -A 96 Ba Paste BIU- - A - Alignment Number Conditional Formatas Cell Cells Editing Formatting Table Styles Clipboard Font Styles AI Jay Corporation has decided to prepare contribution income statements for B D E 24 Quarter2 $ 80,000.00 14.000 25 Quarter 3 $ 84.000.00 14.300 26 Quarter 4 $ 100.000,00 22,500 27 28 Use the data to answer the following 29 30 1. Using the high-low method, determine a cost formula for shipping expenses 31 32 Total Shipping Cost Units 33 High level of activity 34 Low level of activity 35 Change in activity 36 37 Variable shipping cost per unit 38 39 Fixed shipping cost: (Note: please use the highest activity point to compute the total fixed cost) 40 Total shipping cost at high level of activity Less: Variable shipping cost at high level of activity 42 Total fixed shipping cost 44 2. Prepare a contribution margin income statement for Quarter 1 of Year 3. 46 Jay Corporation Rudete Income Statement Sheet1 READY Attempt.) per unit 41 43 45 100% 1. Analyze a mixed cost using the high-low method. 2. Prepare an income statement for a merchandising company using the contribution format. High-Low Method: Contribution Format Income Statement - Excel ? 3 - 5 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign in X Es Calibri - 11 AA 96 M Paste BIU- Cells Editing Alignment Number Conditional Format as Cell Formatting Table Styles Styles Clipboard Font A1 Lo Jay Corporation has decided to prepare contribution income statements for B D E F 46 Jay Corporation 47 Budgeted Income Statement 48 For Year 3. Quarter 1 49 50 Sales 31 Variable expenses: 52 Cost of goods sold 53 Sales commissions 54 Shipping expense (Variable portion) 55 Total variable expenses 56 Contribution margin 57 Fixed expenses 58 Fixed Manufacturing Overhead 59 Administrative Salaries 60 Shipping expense (Fixed portion) 61 Depreciation expense 62 Total fixed expenses 63 Net operating income 64 65 66 67 Sheet1 READY Attempt() 100% Hint 3 JPL, Inc. has provided its sales and expense data for the most recent period The Controller has asked you prepare a spreadsheet that shows the related CVP Analysis computations. Use the information included in the Excel Simulation and the Excel functions described below to complete the task 34 oints Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below. If in a blank cell, *=85" was entered the formula would output the result from cell 85, or 75 in this example. Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys (plus sign to add). - (minus sign to subtract), " (astorisk sign to multiply), and/ (forward slash to divide). From the Excel Simulation below, if in a blank cell-B5+86' was entered the formula would add the values from those cells and output the result, or 120 in this example, if using the other math symbols the result would output an appropriate answer for its function eBook 7 Print Sign in = M References Editing KB 6 CVP Analysis Exoni PRE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW X Calibri 11 AA 96 Paste BTU A Alignment Number Conditional Format as Cell Cells Formatt Table Styles Clipboard Font Styles A1 Given the following information complete a CVP analysis A B D E Given the following information complete a CV analysis 2 for JPL, Inc. 3