A friend, Lugo, has decided to start a coffee shop, Lugo Specialty Coffees, Inc., and has asked your help (business major) in developing projected financial statements for the first five years. Lugos banker is requesting five years of projected financial statements as part of his business plan in order to evaluate Lugos loan application.

Lugo will use the $50,000 he inherited from his grandfather to help start the business, and he will borrow from the bank the remaining money if any, he needs. Lugo has found a location in a strip mall that will rent him space for $2,000 per month and estimates that utilities will cost another $1,500 per month. Salaries and wages are estimated to cost approximately $5,000 per month, but the total of these costs will raise an additional $1,000 per month for every $100,000 in sales growth. Last, Lugo estimates that advertising and promotion will run at 3% of gross sales. Use an income tax rate of 25%, combined both federal and state.

Lugo estimates that sales will start at $200,000 for the first year and estimates 20% growth per year for the first two years followed by 5% annual sales growth thereafter. Lugo is expecting you to help him estimate gross margin and inventory turnover. You must explain your estimates.

Lugo will have to spend $150,000 on fixtures and equipment, and will depreciate these assets over five years using the straight-line method; all assets are estimated to have zero salvage value at the end of their lives. He expects ninety percent of sales will be on credit and debit cards. Assume a credit/debit card collection fee of 2%. All suppliers expect payment for merchandise in thirty days. There is a minimum cash balance of $5,000 required by the bank. Lugo will borrow if necessary to maintain the minimum balance. Assume the borrowing occurs on the first of the year and interest is paid at the end of the year. Assume loan repayments happen at end of the year.

Any money borrowed will cost 10% for interest.

Required:

1. Prepare proforma financials for the first five years of operation. (must be done in Excel)
2. What if Lugo had to borrow all the money he needs to get started? Prepare proforma financial statements if Lugo had to borrow $50,000.
3. Discuss the differences you observe between (1) and (2).