Modeling assignment

Create a working three statement model with assumptions page.Model should be a 5 year annual model.

Assumptions:

1)Company manufactures widgets.Each widget has 4 components.Each component costs $20.Company produces and sells 500 units per month and volume increases 9% per year.

2)The widgets sell for $300 each and the price increases 4% per year.

3)Each widget requires 25 minutes of labor with a loaded labor rate of $28/hr.Labor rate increases 5% per year.

4)Plant overhead runs $22,000 per month.Overhead increases 6% per year.

5)There is a finished goods scrap rate of 2%

6)SG&A is $120,000 per year and increases 3% per year.

7)R&D is $75,000 per year and increases 8% per year.

8)Tax rate is 40%

9)Opening balance sheet in year 0 has $100,000 of equity, $50,000 of debt, inventory of $80,000, fixed assets of $100,000 and cash of $50,000

10)AR& AP have 30 day terms.Inventory turns 4x per year

11)Debt has 4% interest rate and 5-year amortization.Cash earns 0% interest.

12)Fixed Assets have 10-year life - straight line depreciation.

13)Company spends $10,000 per year on capex

One the model is complete, please write a short description of the business that discusses pricing, growth, margins, cash flow generation and any other metrics you feel are relevant.