Chat with us, powered by LiveChat Excel question | Abc Paper
+1(978)310-4246 credencewriters@gmail.com
  

Please read the WORD document then try to finish the Excel work. (Only the red part, named “Shock to assumption).
For EXERCISE the objective will be to quantify the impact of a credible worst-case pandemic risk scenario developed during the FMEA interview. To quantify the scenario, input the impacts into the red portion of the spreadsheet model.
FMEA Interview Documentation
Risk:
Description: Pear Inc. is a company that belongs to the handheld electronic device market, it manufactures SmartPhones and similar devices, and sells them mainly through the retail outlets in the United States. It also provides customers with the service of InfinityG, which allows for better reception and faster data transfer rates.
Scenario: Mutation explodes in the U.S. and the vaccines are found not to be effective, the effectiveness level drops considerably below the level considered safe. And we end up having new infections staying at a level that’s pretty elevated. If quarantine continues, the sales platform would be negatively impacted. Pear relies heavily on in-store sales and the company was behind the curve in its online platform. Customer Service is not good. Sales are largely limited to online distribution,
Likelihood: 15%-25%. Midpoint is 20%
Financial Impact:
· The sales retention rate is 12% lower in salespeople beyond that would ordinarily lose this year. In year 2 and 3, the sales retention rate is 8% lower, 6% lower in year 4, 4% lower in year 5, 2% lower in year 6, and 0 after year 6.
· The sales recruitment is 100 lower this year for new people hired, 50 lower in year 2, and another 25 lower between through year 3 to year 6.
· The average amount of sales per salesperson(production rate) is 30% lower this year, 20% lower in year 2, and 10% lower in year 3. Then it goes back to normal.
· Might decide to cut the price of phones, 15% lower than baseline this year, 10% lower in year 2, 5% lower in year 3.
· The earned rate in the invested asset is 5% lower this year, 3% lower next year, 1.5% lower in year 3, then goes to normal rates.
· R&D spending increases 7.5M each year through year 1 to year 3.
· For Additional SG&A expenses Pear Inc. is a company that belongs to the handheld electronic device market, manufactures SmartPhones and similar devices, and sells them through retail outlets in the United States. It also provides customers with the service of InfinityG, which allows for better reception and faster data transfer rates. In addition to the R&D expenditures, there would be another 40M in medical costs, anther 25M in additional IT and sales training, so that’s another 65M in sales and general administration expenses; next year, there would be another 20M for medical cost, and 5M in advertisement, so another 25M in sales and general expenses; in 2023, expect only 5M in additional medical cost.
· Costs of expense are not impacted because we have a stable supply chain.
· Tax rate and inflation rate remain unchanged.
· Fixed expenses remain unchanged because the company still plans to keep stores open, but they’ll continue to look at that.
· The interest rate on debt would be 1% lower than baseline through 2021 to 2023.

Sheet1

Last Yr Model Yr 1 Model Yr 2 Model Yr 3 Model Yr 4 Model Yr 5 Model Yr 6 Model Yr 7 Model Yr 8 Model Yr 9 Model Yr 10 Model Yr 11 Model Yr 12 Model Yr 13 Model Yr 14 Model Yr 15 Model Yr 16 Model Yr 17 Model Yr 18 Model Yr 19 Model Yr 20

Baseline Assumptions BASELINE ASSUMPTIONS

# Salespeople Hired (Assume Beg of Yr) 225 305 385 465 545 625 705 785 865 945 1,025 1,105 1,185 1,265 1,345 1,425 1,505 1,585 1,665 1,745 1,825

Average Retention of Salespeople 87.50% 86.00% 84.50% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00%

Average Price per Unit 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300

Average Units Sold per Salesperson 685 700 715 730 730 730 730 730 730 730 730 730 730 730 730 730 730 730 730 730 730

Net Earned Rate of Assets 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00%

Cost of Goods Sold as % of Sales 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60%

Additional One-Time R&D Spending – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0

Inflation on R&D and Fixed Expenses 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25%

Additional One-Time SG&A Expenses – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0

Int Rate on Debt 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00%

Tax Rate 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50%

Discount Rate 10.50%

Shock to Assumptions SHOCK TO ASSUMPTIONS

# Salespeople Hired

Average Retention of Salespeople

Average Price per Unit

Average Units Sold per Salesperson

Net Earned Rate of Assets

Cost of Goods Sold as % of Sales

Additional One-Time R&D Spending

Inflation on R&D and Fixed Expenses

Additional One-Time SG&A Expenses

Int Rate on Debt

Tax Rate

Discount Rate

Assumptions for Risk Scenario(s) Run ASSUMPTIONS FOR RISK SCENARIO(S) RUN

# Salespeople Hired 305 385 465 545 625 705 785 865 945 1,025 1,105 1,185 1,265 1,345 1,425 1,505 1,585 1,665 1,745 1,825

Average Retention of Salespeople 86.00% 84.50% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00% 82.00%

Average Price per Unit 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300

Average Units Sold per Salesperson 700 715 730 730 730 730 730 730 730 730 730 730 730 730 730 730 730 730 730 730

Net Earned Rate of Assets 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00% 4.00%

Cost of Goods Sold as % of Sales 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60% 75.60%

Additional One-Time R&D Spending – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0

Inflation on R&D and Fixed Expenses 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25% 2.25%

Additional One-Time SG&A Expenses – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0

Int Rate on Debt 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00%

Tax Rate 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50% 22.50%

Discount Rate 10.00%

PROJECTION (millions)

Salespeople (End of Yr) 1,000 1,122 1,274 1,426 1,616 1,838 2,085 2,353 2,639 2,939 3,250 3,571 3,900 4,236 4,576 4,921 5,269 5,620 5,974 6,330 6,687

Net Growth in Salespeople 12.2% 13.5% 11.9% 13.3% 13.7% 13.5% 12.9% 12.1% 11.4% 10.6% 9.9% 9.2% 8.6% 8.0% 7.5% 7.1% 6.7% 6.3% 6.0% 5.6%

INCOME STATEMENT REVENUE 18.8% 15.3% 15.0% 12.7% 13.5% 13.6% 13.1% 12.5% 11.7% 11.0% 10.2% 9.5% 8.9% 8.3% 7.8% 7.3% 6.9% 6.5% 6.1% 5.8%

Sales 188 223 257 296 333 378 430 486 547 611 678 747 818 891 965 1,040 1,116 1,192 1,270 1,347 1,425

Net Investment Income 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7

Total Revenues 194 230 264 303 340 385 437 493 554 618 685 754 825 898 972 1,047 1,123 1,200 1,277 1,354 1,432

Revenue Growth 18.9% 14.8% 14.6% 12.4% 13.3% 13.3% 12.9% 12.3% 11.6% 10.8% 10.1% 9.4% 8.8% 8.2% 7.7% 7.2% 6.8% 6.4% 6.1% 5.8%

EXPENSES

Cost of Goods Sold (CGS) 136 168 194 223 252 286 325 367 413 462 512 565 619 673 729 786 844 901 960 1,019 1,078

R&D 10 10 10 11 11 11 11 12 12 12 12 13 13 13 14 14 14 15 15 15 16

Additional R&D Expenses – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0

Selling, General & Admin. Expenses (SG&A) 18 19 19 20 20 21 21 21 22 22 23 23 24 25 25 26 26 27 27 28 29

Additional SG&A Expenses – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0

Interest on Debt 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6

Total Expenses 170 203 230 260 289 324 363 407 453 502 554 607 662 717 774 832 890 949 1,008 1,068 1,128

Earnings Before Income Tax (EBT) 23 27 34 43 51 62 74 87 101 116 131 147 164 181 198 215 233 251 269 287 305

Pre-Tax Profit Margin 12.1% 11.6% 13.0% 14.2% 15.1% 16.0% 16.9% 17.6% 18.2% 18.7% 19.1% 19.5% 19.8% 20.1% 20.4% 20.6% 20.7% 20.9% 21.0% 21.2% 21.3%

INCOME TAX (Assume GAAP=TAX Amounts) 7 6 8 10 12 14 17 19 23 26 30 33 37 41 45 48 52 56 60 64 69

Net Income (Assume = shareholder dividends)
: For simplicity, assume any negative net income is funded by a call on shareholders, rather than reducing assets and equity 16 21 27 33 40 48 57 67 78 90 102 114 127 140 153 167 181 194 208 222 236

Post-Tax Profit Margin 8.5% 9.0% 10.1% 11.0% 11.7% 12.4% 13.1% 13.6% 14.1% 14.5% 14.8% 15.1% 15.4% 15.6% 15.8% 15.9% 16.1% 16.2% 16.3% 16.4% 16.5%

Shares Outstanding 105 105 105 105 105 105 105 105 105 105 105 105 105 105 105 105 105 105 105 105 105

eps 0.16 0.20 0.25 0.32 0.38 0.46 0.54 0.64 0.74 0.85 0.97 1.09 1.21 1.33 1.46 1.59 1.72 1.85 1.98 2.12 2.25

VALUATION & ANALYSIS FREE CASH FLOW (Assume no non-cash items) 16 21 27 33 40 48 57 67 78 90 102 114 127 140 153 167 181 194 208 222 236

5-Year 5-Year

Company
Value
: Assume company closes with terminal value equal to equity after 20 years

: For simplicity, assume any negative net income is funded by a call on shareholders, rather than reducing assets and equity Rev CAGR Net Income
CAGR

Baseline 696.9 14.8% 23.9%

Scenario Run 696.9 14.8% 23.9%

% Change 0.0% 0.0% 0.0%

Absolute Value of Change – 0 0.0% 0.0%

Actual Market Valuation

Date Today

Stock Price $ 6.50

Market Capitalization 682.5

P/E ratio 33.1

“Under-Valuation” 2.1%

BALANCE SHEET ASSETS

Invested Assets 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180

Other Assets (Non-depreciating) 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40

Total Assets 220 220 220 220 220 220 220 220 220 220 220 220 220 220 220 220 220 220 220 220 220

LIABILITIES

Debt 120 120 120 120 120 120 120 120 120 120 120 120 120 120 120 120 120 120 120 120 120

Other 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60

Total Liabilities 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180 180

EQUITY 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40

error: Content is protected !!