See attached
Operating Model Exercise – 90 Minutes Lightrock Enterprises is a widget manufacturer that is based in New Paltz, NY. Founded in 1983, the company has established itself as the leading producer of Prolight, its flagship product. At it’s peak, the company was shipping between 30,000-40,000 units of Prolight per month and had grown its top line at double digit growth rates since inception. However, the founder/visionary of Lightrock passed away three years ago and the market landscape has changed drastically with numerous competitors offering innovative, cutting edge products. Because of this, the company has seen its growth rate dwindle over the last few years. In an effort to reinvent itself, the company’s executive team has decided to launch a new product. The new product, Ultralight, has been in incubation for the last 12 months and is now ready for general availability. Create a three statement financial model that provides a monthly financial summary going out 36 months. Assume that the company will create a new shell corporation from this point forward. Following are the beginning balances for select balance sheet accounts: Cash & Equivalents 5,000,000 PP&E 15,000,000 Long-term debt 2,000,000 Owner’s Equity 18,000,000 The company employs a direct sales approach and currently has 200 sales people. It is projected that on average each sales rep will be able to sell 50 units of Prolight and 70 units of Ultralight each month. Following are unit-level details on each product: Prolight: • Retail value of $125 per unit • It takes one month from the time of sale to deliver the product to the customer. Revenue is recognized after delivery has taken place but customers pay for the units up front in full • The cost to manufacture each unit is $30 in payroll cost • There is an additional $12 of raw material costs that go into the production of each unit. Ultralight • Retail value of $150 per unit • It takes one month from the time of sale to deliver the product to the customer. Revenue is recognized after delivery has taken place but customers pay for the units up front in full. • The cost to manufacture each unit is $50 in payroll cost • There is an additional $10 of raw material costs that go into the production of each unit. Some other considerations are as follows: 1. 10% of sales are made on credit and Lightrock’s customers pay off any balances in full the following month (ie there is no bad debt expense or write-offs) 2. Sales reps are paid $25,000 per annum and a 20% one-time commission on each unit they sell. Commissions are paid at the time of sale. 3. Each month, 10 salespeople are added and 5 are terminated/leave the firm 4. There is one sales manager for every 50 sales people. Sales managers earn $80,000 per annum 5. The executive team consists of: a. CEO - $250,000 annual salary b. CFO - $180,000 annual salary c. VP Production - $175,000 annual salary d. VP Sales - $200,000 annual salary 6. For every 2,000 units produced, there is one production manager employed at an annual salary of $60,000. Products go into production immediately after they are sold. 7. Payroll taxes (employer portion) are paid monthly on all salaries and commissions paid out to employees at a rate of 7.5% (employer portion payroll taxes are tax deductible on the corporate level). 8. The long-term debt on the company bears 6% cash interest. Payments are interest only and made monthly. The note matures in 20 years. 9. The corporate tax rate is 35%. Do not consider any tax credits or NOL carryforwards if there are net operating losses in the forecast. 10. Raw material costs are paid on 60 day terms (ie assume that all raw material costs incurred in month 1 are paid in month 3). 11. There is $12,000 of depreciation expense each month. There are no additions to PP&E over the next three years.