5 6 17 8 19 10 11 12 13 14 15 16 17 18 0 Instructions: ACC 2302 - Lab #5 for Chapter 25 The Jones-Pitt Company has 3 separate branches (divisions): X manufactures utensils, Each branch (division) operates its own facility. The administrative offices are located in a separate building, and administrative-facility level costs are allocated among branches A, B and C. The branch income statements are presented for the year 2019; Branch C has experienced a Net Loss. Branch C has suffered losses in recent years, and the company expects C to continue to operate at a loss Income Statements by Branch: (Note: no formulas have been used to present the data.) Division A Division B Division C 2,000,000 1,600,000 1,710,000 Sales Less: Cost of Goods Sold Unit-level Manufacturing Costs Rent on Manufacturing Facility (1,100,000) (240,000) (580,000) (220,000) 800,000 (900,000) (450,000) 360,000 660,000 Gross Margin Less: Operating Expenses (60,000) (45,000) Unit-Level Selling and Admin. Expenses Division-Level Fixed Selling and Admin. Expenses Administrative Facility-Level Costs (140,000) (125,000) (150,000) (240,000) (80,000) (80,000) (80,000) Net Income (loss) 380,000 550.000 (110,000) Requirements - using the provided Excel file: a. Based on the information above, recommend whether Division C should be eliminated. Support your answer by preparing company-wide income statements - one with Division C and one without Division C. b. During 2019, Division C produced and sold 30,000 units of product. Would your recommendation in part (a.) change if sales and production increase to 45,000 units? Support your answer by comparing differential revenue and avoidable cost for Division C, assuming that 45,000 units are sold C. Suppose that the company could sublease Division C's manufacturing facility for $910,000. Would you operate the division at a production and sales volume of 45,000 units or would you sublease? Support your answer with appropriate computations using Excel. Note: you must show supporting schedules/calculations on the Excel worksheet. A 90 Lab 7 - Chapter 25 - Relevant Costing Excel Template Home Insert Page Layout Formulas Data Review View Share X Arial V 10 . AI = = ab Wrap Text General I Do L. LIX E28 Ou Paste I U B a. Av Delete V .00 00 0 $ %) * Insert Format 3 Merge & Center Conditional Format Cell Formatting as Table Styles v Sort & Filter Find & Select L M N o P Q R S T (1,100 Cut 04 4 x fx B D E F G H J K 1 Lab #5: Chapter 25 Name: 2 3 Use this spreadsheet to prepare solutions for the questions on the instructions file (Word). 4 5 Jones-Pitt Company Income Statement by Branch: 6 Division A Division B Division C 7 Sales 2,000,000 1,600,000 1,710,000 8 Less: Cost of Goods Sold 9 Unit-level Manufacturing Costs (580,000) (900,000) 10 Rent on Manufacturing Facility (240,000) (220,000) (450,000) 11 12 Gross Margin 660,000 800,000 360,000 13 Less: Operating Expenses 14 Unit-Level Selling and Admin. Expenses (60,000) (45,000) (150,000) 15 Division-Level Fixed Selling and Admin. Expenses (140,000) (125,000) (240,000) 16 Administrative Facility-Level Costs (80,000) (80,000) (80,000) 17 18 Net Income (loss) 380,000 550,000 (110,000) 19 (Note: no formulas have been used to present the data.) 20 21 Prepare your answers to parts a, b and c on the spreadsheet (below): 22 23 Division C 24 A. Sales 25 Unit-level manufacturing costs 26 Rent for manufacturing facility 27 Unit-level selling & administrative expenses 28 Division-level fixed selling & administrative expenses 29 Contribution to profit 30 31 32 33 Proof: Company wide income statements IF: 34 Keep Division C Eliminate Division C Lab 9 + Ready PHP B + 115% A 90 Lab 7 - Chapter 25 - Relevant Costing Excel Template Home Insert Page Layout Formulas Data Review View Share X Arial V 10 . AI = = ab Wrap Text General Do L. NT LIX E28 Ou Paste A V $ %) .00 0 Insert Delete Format Merge & Center Conditional Format Cell Formatting as Table Styles v Sort & Filter Find & Select 04 x fx A B D E F G H 1 J L M N o P Q R S T Proof: Companywide income statements IF: Keep Division C Eliminate Division C Cut 0 0 0 Sales Less: Cost of Goods Sold Unit-level Manufacturing Costs Renton Manufacturing Facility Gross Margin Less: Operating Expenses Unit-Level Selling and Admin. Expenses Division-Level Fixed Selling and Admin. Expenses Administrative Facility-Level Costs Net Income (loss) 0 0 B. Division C 30,000 assumed units 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 Per unit 0 0 Sales Unit-level Manufacturing Costs Renton Manufacturing Facility Unit-Level Selling and Admin. Expenses Division-Level Fixed Selling and Admin. Expenses Division C 0 0 0 0 0 0 # of units 30,000 30,000 fixed 30,000 fixed 0 . Assume 45,000 assumed units Sales Unit-level Manufacturing Costs Renton Manufacturing Facility Unit-Level Selling and Admin. Expenses Division-Level Fixed Selling and Admin. Expenses 0 0 0 0 0 Lab 9 + Ready B + 115% A 90 Lab 7 - Chapter 25 - Relevant Costing Excel Template Home Insert Page Layout Formulas Data Review View Share X Arial V 10 . AI = = ab Wrap Text General Do L. NT LIX E28 Ou Paste Av V $ %) .00 00 0 Insert Delete Format Merge & Center Conditional Format Cell Formatting as Table Styles v Sort & Filter Find & Select 04 fx B D E F G H 1 J K L M N o P Q R S T C. The Jones-Pitt Company could earn net $460,000 (910,000-450,000) from subleasing the facility. At the 45,000 units level for Division C - the division earns $300,000. The opportunity cost of $460,000 (subleasing and paying rent) compared to $300,000 projected earnings for running the division at 45,000 units yields a loss of 160,000 (300,000-460,000). Cut 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 Lab 9 + Ready PHP B + 115%