Assignment 1: Proforma Financial Statements

A friend of yours (Mocha) decides to start a coffee shop, Mocha Specialty Coffees, Inc.,

and has asked your help in developing projected financial statements for the first five years. Her

banker will not consider making a loan without five year projections of balance sheets, income

statements, and cash flows.

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

corporate 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

$410,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 and defend your estimate.

Mocha will have to spend $70,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 one-half of sales

will be on credit; the average collection period for stores like this usually is sixty days. All

suppliers expect payment for merchandise in thirty days.

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.

I need help understanding where each part goes for the Balance sheet, Income statements, and Cash flows