Widget_for_Class_2

xlsx

School

Boston University *

*We aren’t endorsed by this school

Course

323

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

20

Uploaded by LieutenantFire13036

Report
ABC Widget Corporation Note: All cells highlighted in yellow are input parameters. Team Assignment Influence Chart Assumptions Revenue Variable Costs Fixed Mfg Costs COGS SG&A Marketing_Expenses Pre_Opening_Expenses Capital Expenditure Profit&Loss Cash Flow Sensitivity Analysis
Year 0 1 2 3 4 5 Sensitivity Segment Size 14,000,000 14,420,000 14,852,600 15,298,178 15,757,123 100.0% Segment Growth Rate 3% 3% 3% 3% 100.0% ACV Growth Rate / Repeat Rate Growth 20% 20% 20% 20% 100.0% Awareness Growth Rate NOT APPLICABLE Price $24.00 $23.50 $23.00 $22.50 $22.00 Direct Material Cost Reduction Rate 2% 2% 2% 2% 100.0% Marketing Expense Growth Rate 60% 60% 10% 15% Depreciation Major Equipme 10 Years, straight-line Tax Rate (combined Federa 25% Working Capital: Cash (% of COGS & expenses) 7.00% 7.00% 6.00% 6.00% 6.00% Accounts Receivable (% of sales) 3.00% 4.00% 5.00% 7.00% 8.00% Inventory (% of sales) 10.00% 10.00% 10.00% 10.00% 10.00% Accounts Payable (% of Direct Materials) 4.00% 5.00% 6.00% 7.00% 8.00% Discount Rate 25% Back to Index
Year 0 1 2 3 4 5 Segment Size 14,000,000 14,420,000 14,852,600 15,298,178 15,757,123 Purchase Intent (PI) 25.00% 25.0% 25.0% 25.0% 25.0% Distribution (ACV) 15.00% 18.0% 21.6% 25.9% 31.1% Awareness 12.50% 16.0% 20.00% 24.0% 32.0% % of units lost to competition 0% 0% 20% 30% 35% Trial HHLDS 65,625 103,824 160,408 237,917 392,088 Units Sold - 1 per trial HHLD 65,625 103,824 160,408 237,917 392,088 Total units 65,625 103,824 128,326 166,542 254,857 Price $24 $24 $23 $23 $22 Revenue $1,575,000 $2,439,864 $2,951,509 $3,747,197 $5,606,853 Back to Index
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Sensitivity 100% 100% 100% 100%
Year 0 1 2 3 4 5 Demand 65,625 103,824 128,326 166,542 254,857 Direct Labor per unit $7.00 $7.00 $7.00 $7.00 $7.00 Direct Material per unit $4.25 $4.17 $4.08 $4.00 $3.92 Transportation per unit $0.75 $0.75 $0.75 $0.75 $0.75 Total Variable Costs $787,500 $1,237,063 $1,518,320 $1,956,880 $2,974,197 Back to Index
Sensitivity 100% 100% 100%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Year 0 1 2 3 4 5 Overhead $50,000 $50,000 $75,000 $75,000 $75,000 Manufacturing Rent and Utilities $100,000 $100,000 $100,000 $100,000 $100,000 Fixed Manufacturing Costs $150,000 $150,000 $175,000 $175,000 $175,000 Back to Index
Sensitivity 100%
Year 0 1 2 3 4 5 Variable Costs $787,500 $1,237,063 $1,518,320 $1,956,880 $2,974,197 Fixed Mfg Costs $150,000 $150,000 $175,000 $175,000 $175,000 COGS $937,500 $1,387,063 $1,693,320 $2,131,880 $3,149,197 Back to Index
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Year 0 1 2 3 4 5 Admin. Salaries & Exp. $450,000 $525,000 $625,000 $725,000 $800,000 Rent $50,000 $50,000 $50,000 $50,000 $50,000 Total $500,000 $575,000 $675,000 $775,000 $850,000 Back to Index
Sensitivity 100% 100%
Year 0 1 2 3 4 5 Marketing Expenses $125,000 $200,000 $320,000 $352,000 $404,800 Back to Index
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Sensitivity 100%
Year 0 1 2 3 4 5 Rent $40,000 Salaries $125,000 Marketing $50,000 Total $215,000 Back to Index
Year 0 1 2 3 4 5 Major Equipment $400,000 $200,000 Back to Index
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Year 0 1 2 3 4 5 Revenue $1,575,000 $2,439,864 $2,951,509 $3,747,197 $5,606,853 COGS $937,500 $1,387,063 $1,693,320 $2,131,880 $3,149,197 Gross Margin $637,500 $1,052,801 $1,258,188 $1,615,316 $2,457,656 SG&A $500,000 $575,000 $675,000 $775,000 $850,000 Marketing $125,000 $200,000 $320,000 $352,000 $404,800 Depreciation $40,000 $40,000 $60,000 $60,000 $60,000 Total Exp. $215,000 $665,000 $815,000 $1,055,000 $1,187,000 $1,314,800 EBT -$215,000 -$27,500 $237,801 $203,188 $428,316 $1,142,856 Taxes $0 $0 $59,450 $50,797 $107,079 $285,714 Net Income -$215,000 -$27,500 $178,351 $152,391 $321,237 $857,142 Back to Index
Year 0 1 2 3 4 5 Net Income -$215,000 -$27,500 $178,351 $152,391 $321,237 $857,142 Depreciation $40,000 $40,000 $60,000 $60,000 $60,000 Note a simplifying assumpt Capital Expenditures -$400,000 $0 $0 -$200,000 $0 $0 Free Cash Flow -$615,000 $12,500 $218,351 $12,391 $381,237 $917,142 Terminal Value $1,834,284 Terminal Value of 2xFinal Y Total Cash Flow -$615,000 $12,500 $218,351 $12,391 $381,237 $2,751,426 NPV $598,831 IRR 46% Back to Index
tion is that there is no change in Net Working Capital, which does not reflect the Core requirements. Year Cash Flow is a placeholder and does not reflect the product lifecycle in Core.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Starting Segment Size Segment Growth Rate Purchase Intent Distribution (ACV) ACV Growth Rate Awareness Awareness Growth Rate Units lost to competition Direct Labor Year 1 Direct Material Year 1 Direct Material Cost Reduction Rate Transportation Cost Manufacturing Rent and Utilities Administrative Salaries Marketing Expenses Tax Rate Base Case NPV $598,831 Year 1 Value (Base case) Breakeven % Change Change in NPV with 1% increase Back to Index
Check Elasticities (% change due to 1% increase)