The Excel Sheet will be used as the simulation. You will be required to enter information into the spreadsheet and make decisions for the company. The aim is to reach the highest earning per earnings per share you achieved.

File Edit View Insert Format Data Tools Extensions Help Last edit was 1 hour ago 1 6 7 100% - $ % .0 .00 123- Callbri F24 - F | =SUM(F21: F23) A B C D Ketchup Simulation G H I K L M N O Income Statement 4/23/2022 Account Organic Core Brand Private Label Total Sales Volume (Units) 5,837,151 52,673,889 11,621,332 70,132,372 Gross Sales 6,960,803 $ 43,060,904 9,500,439 $ 59,522,146 Less: Promotions (600,000) (700,000) (700,000) S (2,000,000) Net Sales 6,360,803 $ 42,360,904 8,800,439 S 57,522,146 Variable COGS (6,537,609) $ (46,353,022) $ (8,715,999) $ (61,606,630) Fixed COGS (665,844) $ (6,008,511) $ (1,325,645) (8,000,000) Obsolescence - Overproduced Inventory in to ur (0) $ (O 17 Total COGS (842,650) $ (10,000,629) $ (1,241,205) $ (12,084,485) 19 MS&D Expenses (5% of NS) (318,040) $ (2,118,045) $ (440,022) $ (2,876,107) 20 Operating Income (1,160,691) $ (12,118,674) $ (1,681,227) $ (14,960,592) 21 Interest expense - net (3% of NS) (190,824) $ (1,270,827) $ (264,013) $ (1,725,664) 22 Income Before Income Taxes (1,669,555) $ (15,507,546) $ (2,385,263) $ (19,562,364) 23 Income taxes (26%) 434,084 4,031,962 $ 620,168 $ 5,086,215 24 Net Income (1,426,295) $ (12,746,411) $ (2,029,107) $ (16,201,813) 25 -23.1% -28.2% % of NS -22.4% -30.1% 26 5,000,000 27 Outstanding Shares 28 Earnings Per Share (EPS) (3.24) 29 30 31 Incom's Statement Sheet2 Manufacturing Decisions Product Decisions ~ Pricing & Promotion Decision + 9 M Cexcel with formula File Edit View Insert Format Data Tools Extensions Help Last edit was 1 hour ago n n 8 7 100% - $ % .0 .00 123- Default (Ca.. - 11 - B IS A 9. BB 3 - = - 1 - H - V - co Q M Y - E. H17 A C Ketchup Simulation K L M N O P Q R S T 2 Mfg Decisions U V W 4/23/2022 Projected Financial Results: Organic 5 Core Brand Private Label Total Volume (Units) Total 6 5,837,151 52,673,889 Net Sales 11,621,332 6,360,803 70,132,372 42,360,904 8,800,439 Net Income (1,426,295) 57,522,146 $ (12,746,411) S 8 (2,029,107) Margin % (16,201,813) -22.4% -30.1% -23.1% EPS 28.2% 10 (3.24) Sales Volume Forecasted 5,837,151 52,673,889 11,621,332 70,132,372 11 - 412 Production Decision: 413 414 Plant Capacity (Units): 415 Total Available Units 100,000,000 416 Units to Produce 70,132,372 417 418 Under/(Over) Capacity 29,867,628 UNDER CAPACITY 419 0 420 In-House Organic Core Brand Private Label 421 Units: 422 Units to Produce 5,837,151 52,673,889 11,621,332 423 Sales Volume Forecasted 5,837,151 52,673,889 11,621,332 424 Over/(Under Produced) 425 Reduction (Over Capacity) 426 Adjusted Units to Produce 5,837,151 52,673,889 11,621,332 Total Produced 70,132,372 427 428 Overhead Allocation: 429 Includes labor, depreciation, utilities, etc. 430 431 Plant Overhead: 432 Total Overhead Cost 8,000,000 433 Core Brand 434 Organic Private Label 435 In-House 0.11 0.11 0.11 436 Overhead Rate $ 665,844 6,008,511 1,325,645 437 Overhead Dollars Applied 565,844 $ 6,008,511 $ 1,325,645 438 Total OH Dollars 439 Sheet2 - + E Product Decisions Pricing & Promotion Decision Manufacturing Decisions Income Statement ~ Mexcel with formula - Google She x G 30% of 2000000 - Google Search x + C A docs.google.com/spreadsheets/d/1_uOFeqxKBHeo4NE9jac1rm2RHHqJyjq/TT4A2k2580g/edit#gid=285513224 excel with formula * G File Edit View Insert Format Data Tools Extensions Help Last edit was 1 hour ago 100% - $ % .0 .00 123- Calibrl H19 EX 1.09 - 11 " BISA Q BE - - 1 - H - - GO H Y - E. D E F G H Ketchup Simulation K L N O P Q R S T U V W TRY Pricing & Promotion Decisions 4/23/2022 Projected Financial Results: Organic Core Brand Private Label Total Volume (Units) Total 5,837,151 52,673,889 11,621,332 Net Sales 70,132,372 S 6,360,803 S 42,360,904 8,800,439 Net Income S 57,522,146 (1,426,295) (12,746,411) 8 (2,029,107) (16,201,813) Margin % -22.4% -30.1% 9 -23.1% 28.2% EPS 10 (3.24) Sales Volume Forecasted 5,837,151 52,673,889 11,621,332 70,132,372 11 12 Pricing Decision: 13 Please enter a selling price for each product. 15 Organic Core Brand Private Label 16 Selling Price (per Unit): 17 Wholesale Price 1.19 0.82 0.82 18 Avg Retail Margin 25% 25% 25% 19 On-Shelf Price $1.59 $1.09 $1.09 20 21 Promotion Decision: 22 Please enter a promotion plan for each item. Total Promotion Budget: 25 Total To Spend 2,000,000 26 Total Spent 2,000,000 27 Under/(Over) Spent 28 29 Organic Core Brand Private Label 30 Promotion: 31 $ 600,000 700,000 $ 700,000 Promotion Allocation 0.06 0.10 0.01 32 Per Unit Cost 33 34 35 36 37 38 39 Income Statement Sheet2 ~ Manufacturing Decisions Pricing & Promotion Decisions + E Product Decisions MH excel with formula . Google She x G 30% of 2000000 - Google Search x + - C docs.google.com/spreadsheets/d/1_uOFeqxKBHeo4NE9jac1rm2RHHqJyjqITT4A2k2580g/edit#gid=159104382 excel with formula * 0 File Edit View Insert Format Data Tools Extensions Help Last edit was 1 hour ago 100% - $ % .0 .09 123- Calibrl 11 - B IS A Q. B E - E - 1 - H - - GO P Q Y - E- F26 x Plastic A D E G H K L Ketchup Simulation M N O P Q R S T U V W rev 0 of Product Decision 4/23/2022 Projected Financial Results: Organic Core Brand Private Label Total Total Volume (Units) 5,837,151 52,673,889 11,621,332 70,132,372 Net Sales S 6,360,803 42,360,904 8,800,439 $ 57,522,146 Net Income (1,426,295) S (12,746,411) (2,029,107) $ (16,201,813) Margin % -22.4% -30.1% -23.1% -28.2% EPS (3.24) 10 Sales Volume Forecasted 5,837,151 52,673,889 11,621,332 70,132,372 20 21 Packaging Decision: 22 Please select one of each for Bottle, Lid, & Label. 23 24 Organic Core Brand Private Label 25 26 Bottle Coice: Glass Plastic Plastic 27 28 Lid Choice: Screw On Lid Screw On Lid Pop Up Cap 29 30 Label Choice: Black & White Black & White Black & White 31 32 Total Packaging Cost: 0.29 0.14 0.21 33 34 Ingredients Decision: 35 Please select one of each for Tomato & Sweetener 36 37 Organic Core Brand Private Label 38 Non-Organic 39 Tomato: Organic - Frest Organic - Frest 40 Sugar HF Corn Syrup HF Corn Syrup 41 Sweetener: 42 0.74 0.54 43 Total Ingredients Cost: 0.83 44 0.88 0.75 1.12 $ 45 Total Product Cost: 46 47 AR Income Statement Sheet2 - Pricing & Promotion Decision - Manufacturing Decisions + Product DecisionsFile Edit View Insert Format Data Tools Extensions Help Last edit was seconds ago 100% |% .0 .00 123- Callbri 11 BIS x = Income Statement . I SF$28 C LL I Z Ketchup Simulation Product Decision 4/23/2022 Projected Financial Results: Organic Core Brand Private Label Total Total Volume (Units) 12,776,410 30,000,000 10,977,078 53,753,488 Net Sales 20,185,254 46,325,000 11,766,884 78,277,138 Net Income $ (34,230,291) 3,792,285 (4,025,486) (34,463,493) CO Margin % -169.6% 8.2% -34.2% 44.0% EPS (6.89) Sales Volume Forecasted 12,776,410 51,851,449 10,977,078 75,604,937 20 Packaging Decision: Please select one of each for Bottle, Lid, & Label. 24 Organic Core Brand Private Label Bottle Coice: Glass Glass Plastic 28 Lid Choice: Screw On Lid Screw On Lid Screw On Lid 29 Label Choice: Full Color Black & White Black & White Total Packaging Cost: 0.35 0.29 0.14 Ingredients Decision: 35 Please select one of each for Tomato & Sweetener. 36 37 Organic Core Brand Private Label 38 39 Tomato: Organic - Frest Organic - Frest Non-Organic 40 IF Corn Syrup Sweetener: Sugar HF Corn Syrup 0.83 0.74 0.54 Total Ingredients Cost: n 0.68