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 $75,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 $1,500 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 another $1,000 per month as sales exceed $400,000 per year.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.

Sales will start at $200,000 for the first year, rise to $225,000 in the second year, and $300,000 in the third. In the fourth year Mocha's coffee shop has an incredible year with sales of $450,000. However, her success draws a great of attention as well as competitors. As a result year 5 sales drop to $250,000. Mocha is expecting you to help her estimate gross margin. You must explain your estimate.

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 inventory to turn six times per year, and seventy-five 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. Mocha's banker requires that she maintain a minimum of $5,000 cash balance.

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 Mocha had to borrow all the money she needs to get started? Prepare proforma financial statements if Mocha had to borrow $75,000.

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

I'm not really sure how to set up my income statement, balance sheet, or cash flow financial statements correctly with the information provided in this question.