A friend, Mocha, has decided to start a coffee shop, Mocha Specialty Coffees, Inc., and has asked your help (you are getting an MBA) in developing projected financial statements for the first five years. Mocha’s banker is requesting five years of projected financial statements as part of her business plan in order to evaluate Mocha’s loan application.

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

Mocha estimates that sales will start at $225,000 for the first year and estimates 20% growth per year. Mocha is expecting you to help her estimate gross margin and inventory turnover. You must explain your estimates. Mocha will have to spend $100,000 on fixtures and equipment, and she will depreciate these assets over five years using the straight line method; all assets will have zero salvage value at the end of their lives. She 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. Mocha 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 year.

Any money borrowed will cost 10% for interest.

Required: 

1). Prepare preformat financials for the first five years of operation. (must be done in Excel)

2). What if Mocha had to borrow all the money she needs to get started? Prepare preformat financial statements if Mocha had to borrow $50,000.

3). Discuss the differences you observe between (1) and (2).