D E F G H Insert formulas and cell references into the "gold" cells below. 2 Product Lines Nut Blueberry Strawberry Raisin Original Mixed Total 5 Sales Revenues E 718,080 E 1,795,200 E 1,705,440 E 1,346,400 E 2,154,240 E 1,256,640 E 8,976,000 6 Variable Expenses: Variable Manufacturing Expense E 502,656 E 1,256,640 E 1,193,808 E 942,480 E 1,507,968 E 879,648 E 6,283,200 8 Variable Sales Expense E 16,896 E 42,240 E 40,128 E 31,680 E 50,688 E 29,568 E 211,200 Contribution Margin E 198,528 E 496,320 E 471,504 E 372,240 E 595,584 E 347,424 E 2,481,600 10 Fixed Expenses: 11 Direct Labor E 76,800 E 192,000 E 182,400 E 144,000 E 230,400 E 134,400 E 960,000 12 Administrative Expenses $ 6,000 E 6,000 $ 6,000 E 6,000 E 6,000 E 6,000 E 36,000 13 Advertising E 16,157 E 40,392 E 38,372 E 30,294 E 48,470 E 28,274 E 201,960 14 Depreciation - Building E 10,000 E 10,000 E 10,000 E 10,000 E 10,000 E 10,000 E 60,000 15 Depreciation - Mfg Equipment E 40,000 E 40,000 E 40,000 E 40,000 E 40,000 E 40,000 E 240,000 16 Research & Development E 24,000 E 24,000 E 24,000 E 24,000 E 24,000 E 24,000 E 144,000 17 Sales Salaries E 70,000 E 80,000 E 80,000 E 70,000 E 100,000 E 80,000 E 480,000 18 Transportation Costs E 14,000 E 14,000 E 14,000 E 14,000 E 14,000 E 14,000 E 84,000 19 Net Income E 58,429 E 89,928 E 76,732 E 33,946 E 122,714 E 10,750 E 275,640 20 21 Decision 1 22 23 Product Lines 24 Nut Blueberry Strawberry Raisin Original Mixed Total 25 Sales Revenues Variable Expenses: 27 Variable Manufacturing Expense 28 Variable Sales Expense 29 Contribution Margin 30 Fixed Expenses: 31 Direct Labor 32 Administrative Expenses 33 Advertising 34 Depreciation - Building 35 Depreciation - Mfg Equipment 36 Research & Development 37 Sales Salaries 38 Transportation Costs 39 Net Income 40 41 Difference in Net Income if Nut Muesli is Dropped [1] (E 275,640)B C D E F G H 43 44 Decision 2 45 Relevant Costs 46 # of Boxes Costs 47 Cost of purchasing large boxes 576,000 E 0.28 [4] 48 49 Cost of manufacturing large boxes 50 Warehouse space 51 Labor per month E 12,000 52 Equipment purchase E 52,000 53 Cardboard 990 E 12.00 54 Labels E0.01 55 Fixed manufacturing overhead E 1.61 56 Total cost to manufacture boxes 57 58 Cost to manufacture vs. cost to purchase [4] 59 60 [5] 61 [6] 62 63 Decision 3 Special Order . Relevant per Order Total Special Order Order Total Standard 64 Standard box [7] [7] per box [8] [8] Order [9] 65 order quantity 32,000 66 Selling Price (7.90 67 Current costs of making Mixed Fruit Muesli 68 Raw Materials E 5.30 69 Direct Labor E 0.65 70 Variable Manufacturing Overhead E 0.03 71 Variable Selling Costs E 0.20 72 Fixed Manufacturing Overhead 73 (assigned using a POHR/MH) E 1.20 74 Special labels: set up fee E 500 75 per label E 0.02 76 77 Net Profit E 0.52 78 [7] [7] [8] [8] [9] 79A B C D E F G H 79 80 81 Decision 4 82 Quantities: Each 8,000 24,000 83 1 Kg Boxes of Mixed Fruit Muesli: 84 Selling Price 67.90 E 63,200.00 85 Costs of making Mixed Fruit Muesli Common Joint Costs: 86 wheat E 1.84 E 14,720.00 87 oats 6 0.96 E 7,680.00 88 fruit and nuts E 1.45 E 11,600.00 89 direct labor E 0.65 E 5,200.00 90 packaging E 0.40 E 3,200.00 91 Total Manufacturing Costs E 5.30 E 42,400.00 [12] 92 Profit E 2.60 E 20,800.00 93 94 Granola bars: Costs to Process Further: 95 Selling Price E 70,800.00 96 Added Costs 97 honey E 6,480.00 98 peanutbutter E 1,800.00 99 packaging E 4,800.00 100 Total Added Manufacturing Costs E 13,080.00 [12] 101 Profit E 57,720.00 102 103 Sales Revenue from Processing Further (Granola Bars) 104 Less Common Joint Costs 105 Less Costs to Process Further 106 Profit [12] 107 108 Difference in Profit (Process Further - Sell at Split Off) (E 20,800.00) [12] 109 110 Decision 5 500g Mixed 1Kg Mixed 111 Muesli Muesli Granola Bars 112 Selling Price E 5.40 67.90 E 3.95 1 13 Variable Costs E 2.63 E 4.85 E 0.00 114 Contribution Margin E 2.77 E 3.05 E 3.95 15 Machine Hours 0.0015 0.0015 0.0005 16 Contribution Margin per MH [14] 17 In what order should they be produced? [14] 118 (number these 1 through 3) 19