Question 4 *to be completed in excel The data file for Lab 5 Question 4 reports total manufacturing costs for Pope Industries Ltd. The plant facilities have a maximum production capacity of 350,000 units. Current production and sales are at 325,000 units at a selling price of $70 per unit. Required 1. Using the data file and the sum if formula, calculate the vidriable and fixed costs for each unit. Ensure to report the total cost per unit. 2. If the company received a one-time special order, what is the minimum bid price per unit? 3. The company has received two special orders, one from Green Company and the other from Blue Company. The company will not be able to fill both orders. Evaluate each order and determine which order the company should select. a. Green Company -requires 35,000 units and has offered to pay $65 per unit. The order will require additional machining time which will cost the company an additional variable cost of $15 per unit. b. Blue Company - requires 45,000 units and has offered to pay $40 per unit. Due to the nature of the order, the company estimates that on a per unit basis, variable costs will be reduced by 30%. The variable cost reduction is only for the 45,000-unit special order. Regular units will still incur the variable costs calculated in Requirement 1. rocio H7 fx D E B 1 Type of Cost Cost Description 2 Variable cost Manufacturing 3 Variable cost Non-Manufacturing 4 Fixed cost Plant, Utlities, Non-Manufacturing 5 Variable cost Non-Manufacturing 6 Variable cost Manufacturing 7 Variable cost Non-Manufacturing 8 Fixed cost Plant, Utlities, Non-Manufacturing 9 Variable cost Non-Manufacturing 10 Variable cost Non-Manufacturing 11 Variable cost Non-Manufacturing 12 Variable cost Non-Manufacturing 13 Fixed cost Plant, Utlities, Non-Manufacturing 14 Fixed cost Plant, Utlities, Non-Manufacturing 15 Fixed cost Plant, Utlities, Non-Manufacturing 16 Variable cost Non-Manufacturing 17 Variable cost Non-Manufacturing 18 Fixed cost Plant, Utlities. Non-Manufacturing 19 Variable cost Non-Manufacturing 20 Variable cost Manufacturing 21 Variable cost Non-Manufacturing 22 Fixed cost Plant. Utlities, Non-Manufacturing 23 Variable cost Non-Manufacturing 24 Variable cost Non Manufacturing 25 Variable cost Non-Manufacturing 26 Variable cost Manufacturing Cost $ 120,000 $ 3,600 $ 240,000 $ 6,000 $ 125,079 3,369 $ 32,540 $ 6,253 S 25,000 $ 43,750 25,000 22,000 39,540 31,270 17,500 s 18,100 22,000 20,000 S 225.000 $ 6.750 S 35.000 $ 3.000 S 11,500 s 20,500 S 317.000 U U U U Us on us. + e Search o contain Viruses. Unless you need to edit it's safer to st H7 D E F A B 27 Fixed cost Plant, Utlities, Non-Manufacturing 28 Fixed cost Plant, Utlities, Non-Manufacturing 29 Fixed cost Plant, Utlities, Non-Manufacturing 30 Variable cost Manufacturing 31 Variable cost Manufacturing 32 Fixed cost Plant, Utlities, Non-Manufacturing 33 Variable cost Manufacturing 34 Variable cost Manufacturing 35 Variable cost Manufacturing 36 Fixed cost Plant, Utlities, Non-Manufacturing 37 Variable cost Manufacturing 38 Variable cost Manufacturing 39 Variable cost Manufacturing 40 Variable cost Non-Manufacturing 41 Fixed cost Plant, Utlities, Non-Manufacturing 42 Fixed cost Plant, Utlities. Non-Manufacturing 43 Fixed cost Plant, Utlities, Non-Manufacturing 44 Variable cost Manufacturing 45 Variable cost Manufacturing 46 Fixed cost Plant, Utlities, Non-Manufacturing 47 Variable cost Non-Manufacturing 49 Variable cost Manufacturing 49 Variable cost Manufacturing 50 Fixed cost Plant, Utlities, Non-Manufacturing 151 Variable cost Manufacturing 52 Variable cost Manufacturing $ 19,000 $ 21,000 $ 11,000 $ 323,340 $ 281,000 $ 5,000 $ 190,000 $ 329,800 $ 286,620 $ 30,000 s 193,800 S 336,400 $ 884,270 $ 26,529 s 45,000 S 55,000 S 85,000 S 160.000 $ 150,000 $ 13,000 5 4,500 S 163.200 5 253,000 S 17.000 195.000 166, ESC Echapo 5 D E 53 Variable cost 54 Variable cost 55 Fixed cost 56 Fixed cost 57 Fixed cost 58 Variable cost 59 Variable cost 60 Variable cost 61 Variable cost 62 Fixed cost 63 Fixed cost 64 Fixed cost 65 Variable cost 66 Variable cost 67 Fixed cost 68 Variable cost 69 Variable cost 70 Variable cost 71 Variable cost 72 Fixed cost 73 Fixed cost 74 Fixed cost 75 Variable cost 76 Variable cost 77 Fixed cost 78 Vanable cost B Non-Manufacturing $ 4,994 Non-Manufacturing $ 150 Plant, Utlities, Non-Manufacturing $ 57,500 Plant, Utlities, Non-Manufacturing $ 344,300 Plant, Utlities, Non-Manufacturing $ 288,100 Manufacturing $ 400,110 Manufacturing S 366,000 Manufacturing S 255,000 Manufacturing $ 575,000 Plant, Utlities, Non-Manufacturing $ 85,000 Plant, Utlities, Non-Manufacturing S 61,300 Plant, Utlities, Non-Manufacturing S 74,000 Manufacturing $ 586,500 Manufacturing 750,000 Plant, Utlities, Non-Manufacturing 120,000 Non-Manufacturing S 22.500 $ 675 Non-Manufacturing Manufacturing s 765,000 Manufacturing $ 144.000 s Plant, Utlities, Non-Manufacturing 260.000 $ 780,000 plant, utlities. Non-Manufacturing s 350.000 plant, Ulities, Non-Manufacturing 5 14320 Non Manufacturine $ Non Manufacturing 41000 Plant utiles. Non-Manufacturing Non Manufacturing V> o p Search 12 ESC Ech C D E F G 79 Variable cost 80 Variable cost 81 Fixed cost 82 Variable cost 83 Variable cost 84 Fixed cost 85 Variable cost 86 Variable cost 87 Variable cost 88 Fixed cost 89 Fixed cost 90 Variable cost 91 Variable cost 92 Variable cost 93 Fixed cost 94 Variable cost 95 Variable cost 96 Fixed cost 97 Variable cost 98 Variable cost 99 Variable cost 100 Fixed cost 101 502 103 B Manufacturing Manufacturing Plant, Utlities, Non-Manufacturing Manufacturing Manufacturing Plant, Utlities, Non-Manufacturing Manufacturing Manufacturing Non-Manufacturing Plant, Utlities, Non-Manufacturing Plant, Utlities, Non-Manufacturing Non-Manufacturing Manufacturing Manufacturing Plant, Utlities, Non-Manufacturing Non-Manufacturing Manufacturing Plant, Utlities, Non-Manufacturing Manufacturing Non.Manufacturing Manufacturing Plant. Utlities. Non-Manufacturing $ 150,000 $ 366,000 $ 252,000 $ 212,000 $ 153,000 s 163,000 $ 533,500 $ 350,000 S 10,500 $ 74.000 S 85,000 $ 14.000 S 377.917 $ 285.000 s 96,000 s 18.550 S 175,000 $ 107.000 $ 109,000 5 45,000 $ 222.000 S 208.450 Sheet! F e Search ESC Edhe 6 5 7 8 4 3 7 2