Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Describe into words what the below Excel sheet measures. Specifically, please do the following: In a sentence for each category (category examples: Research and Development,
Describe into words what the below Excel sheet measures. Specifically, please do the following:
In a sentence for each category (category examples: Research and Development, New supply network, and etc.) , put results into presentation form (into words). For example This project has a net present value of $XXXX. This indicates
0 1 2 3 5 6 7 8 Year Acq stage Assumptions: Research and development $ Initial promotion, updates to me $ New supply network $ Employee training $ Product sampling $ Additional investment in WC-7 WC balance $ Incremental investment in wort $ (4,600,000.00) $4.6M in product research and development (R&D team of 20 $130,000 salary, plus $2M in resources) https://www.salary.com/rese (31,460,000.00) $1,000 promotion package cost x 12 mo x 2,580 stores = $30,960,000 and $500,000 in updates to menu and digital media (3,000,000.00) $3M in additional supply and packaging (309,600.00) 6 employees per store x $20 per hour x 1 hrs x 2,580 stores ($309,600) (2,000,000.00) $2M in promotional product sampling (77 of $10 kits for 2580 stores) 7% 7% 7% 7% 7% 7% 7% 7% 70% 3,910,458.30 $ 4,184,190 $ 4,477,084 $ 4,790,480 $ 5,125,813 $ 5,484,620 $ 5,868,543 $ 6,279,342 $ 6,718,895 (3,910,458.30) $ (273,732.08) ######## ######## ######## ######## ######### ######### $ (451,203.00) $ $ Operating stage Assumptions: Additional sales of new product Lost sales of existing product Variable cost of new product (% of sales) Variable cost of old menu product (%) Additional fixed cost of new product Reduction in fixed cost of old product Tax rate 55,863,690.00 (5,000,000.00) 60% 60% (500,000.00) 10,000.00 28% 0.02 ######## 60% 60% ######## $ 10,000.00 28% 0.02 ######## 60% 60% ######## $ 10,000.00 28% 0.02 ######## 60% 60% ######## $ 10,000.00 28% 0.02 0.01 ######## ######### 60% 60% 60% 60% ######## ######### $ 10,000.00 $ 10,000.00 28% 28% 0.01 0.01 ######### ########## 60% 60% 60% 60% ######### $ 250,000.00) $ 10,000.00 $ 10,000.00 28% 28% $ $ Disposition / Continuing stage 5 times the last forecasted year's net operating stage cash flow 0 1 2 3 4 5 6 7 8 Year Acquisition stage cash flow: Research and development $ Initial promotion Expenses related to the new su: $ Training $ Vendor testing $ Additional investment in WC $ Total acquisition stage cash flov $ (4,600,000.00) (31,460,000.00) (3,000,000.00) (309,600.00) (2,000,000.00) (3,910,458.30) $ (45,280,058.30) $ (273,732.08) (273,732.08) ######## ######## ######## ######## ######## ######## ######## ######## ######### ######### ######### $ (451,203.00) ######### $ (451,203.00) ######## ######## ######### ########## Operating stage cash flow: Additional sales of new product $ Lost sales of existing product $ Variable cost of new product (% of sales) $ Variable cost of old menu product (%) $ Additional fixed cost of new product (incl. advertisemen $ Reduction in fixed cost of old product $ Taxable income $ Taxes $ After tax income $ 55,863,690.00 (5,000,000.00) (33,518,214.00) 3,000,000.00 (500,000.00) 10,000.00 19,855,476.00 (5,559,533.28) 14,295,942.72 ######## ######## ######## ######## $ 10,000.00 ######## ######## ######## ######## ######## ######## ######## ######## $ 10,000.00 ######## ######## ######## ######## ######## ######## ######## ######## $ 10,000.00 ######## ######## ######## ######## ######## ######## ######## $ 10,000.00 ######## ######## ######## ######### ######### ######### ######### ######### $ 10,000.00 ######### ######### ######### ######### ########## ######### ########## ######### $ 3,000,000.00 ######### $ (250,000.00) $ 10,000.00 $ 10,000.00 ######### ########## ######### ########## ######### ########## 0 1 2 3 4 5 6 7 8 Years Continuing value ########## 14,022,210.64 ######## ######## ######## ######## ######### ######### ########## Total cash flow 8% cost of capital NPV Index IRR $ $ ($45,280,058.30) $ 129,955,967.64 84,675,909.34 2.87 35% Total cash flow Accumulated cash flow Payback period ($45,280,058.30) $ 14,022,210.64 ######## ######## ($45,280,058.30) ($31,257,847.66) ######### ######### 3.15 years ######## ######## ######## ######## ######### ######### ######### ######### ########## ########## $ Total cash flow Present value Accumulated present value Present value payback period 1 2 0 3 4 5 6 7 8 (45,280,058.30) $ 14,022,210.64 ######## ######## ######## ######## ######### ######### ########## (S45,280,058.30) $12,983,528.37 ################## ######### ######### ########## ########## $52,691,332.26 ($45,280,058.30) ($32,296,529.93) ######### ######### ######### ######### ########## ########## $84,675,909.34 3.76 years 0 1 2 3 5 6 7 8 Year Acq stage Assumptions: Research and development $ Initial promotion, updates to me $ New supply network $ Employee training $ Product sampling $ Additional investment in WC-7 WC balance $ Incremental investment in wort $ (4,600,000.00) $4.6M in product research and development (R&D team of 20 $130,000 salary, plus $2M in resources) https://www.salary.com/rese (31,460,000.00) $1,000 promotion package cost x 12 mo x 2,580 stores = $30,960,000 and $500,000 in updates to menu and digital media (3,000,000.00) $3M in additional supply and packaging (309,600.00) 6 employees per store x $20 per hour x 1 hrs x 2,580 stores ($309,600) (2,000,000.00) $2M in promotional product sampling (77 of $10 kits for 2580 stores) 7% 7% 7% 7% 7% 7% 7% 7% 70% 3,910,458.30 $ 4,184,190 $ 4,477,084 $ 4,790,480 $ 5,125,813 $ 5,484,620 $ 5,868,543 $ 6,279,342 $ 6,718,895 (3,910,458.30) $ (273,732.08) ######## ######## ######## ######## ######### ######### $ (451,203.00) $ $ Operating stage Assumptions: Additional sales of new product Lost sales of existing product Variable cost of new product (% of sales) Variable cost of old menu product (%) Additional fixed cost of new product Reduction in fixed cost of old product Tax rate 55,863,690.00 (5,000,000.00) 60% 60% (500,000.00) 10,000.00 28% 0.02 ######## 60% 60% ######## $ 10,000.00 28% 0.02 ######## 60% 60% ######## $ 10,000.00 28% 0.02 ######## 60% 60% ######## $ 10,000.00 28% 0.02 0.01 ######## ######### 60% 60% 60% 60% ######## ######### $ 10,000.00 $ 10,000.00 28% 28% 0.01 0.01 ######### ########## 60% 60% 60% 60% ######### $ 250,000.00) $ 10,000.00 $ 10,000.00 28% 28% $ $ Disposition / Continuing stage 5 times the last forecasted year's net operating stage cash flow 0 1 2 3 4 5 6 7 8 Year Acquisition stage cash flow: Research and development $ Initial promotion Expenses related to the new su: $ Training $ Vendor testing $ Additional investment in WC $ Total acquisition stage cash flov $ (4,600,000.00) (31,460,000.00) (3,000,000.00) (309,600.00) (2,000,000.00) (3,910,458.30) $ (45,280,058.30) $ (273,732.08) (273,732.08) ######## ######## ######## ######## ######## ######## ######## ######## ######### ######### ######### $ (451,203.00) ######### $ (451,203.00) ######## ######## ######### ########## Operating stage cash flow: Additional sales of new product $ Lost sales of existing product $ Variable cost of new product (% of sales) $ Variable cost of old menu product (%) $ Additional fixed cost of new product (incl. advertisemen $ Reduction in fixed cost of old product $ Taxable income $ Taxes $ After tax income $ 55,863,690.00 (5,000,000.00) (33,518,214.00) 3,000,000.00 (500,000.00) 10,000.00 19,855,476.00 (5,559,533.28) 14,295,942.72 ######## ######## ######## ######## $ 10,000.00 ######## ######## ######## ######## ######## ######## ######## ######## $ 10,000.00 ######## ######## ######## ######## ######## ######## ######## ######## $ 10,000.00 ######## ######## ######## ######## ######## ######## ######## $ 10,000.00 ######## ######## ######## ######### ######### ######### ######### ######### $ 10,000.00 ######### ######### ######### ######### ########## ######### ########## ######### $ 3,000,000.00 ######### $ (250,000.00) $ 10,000.00 $ 10,000.00 ######### ########## ######### ########## ######### ########## 0 1 2 3 4 5 6 7 8 Years Continuing value ########## 14,022,210.64 ######## ######## ######## ######## ######### ######### ########## Total cash flow 8% cost of capital NPV Index IRR $ $ ($45,280,058.30) $ 129,955,967.64 84,675,909.34 2.87 35% Total cash flow Accumulated cash flow Payback period ($45,280,058.30) $ 14,022,210.64 ######## ######## ($45,280,058.30) ($31,257,847.66) ######### ######### 3.15 years ######## ######## ######## ######## ######### ######### ######### ######### ########## ########## $ Total cash flow Present value Accumulated present value Present value payback period 1 2 0 3 4 5 6 7 8 (45,280,058.30) $ 14,022,210.64 ######## ######## ######## ######## ######### ######### ########## (S45,280,058.30) $12,983,528.37 ################## ######### ######### ########## ########## $52,691,332.26 ($45,280,058.30) ($32,296,529.93) ######### ######### ######### ######### ########## ########## $84,675,909.34 3.76 yearsStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started