Assignment 6

Pro forma financial statements for Jack Sprats Restaurant

Start with the Year 1 income statement from the case. I have provided an Excel spreadsheet on Laulima. In class we will create an operating cash flow statement for Year 1 based on the information in the case.

Question 1: Suppose the revenue is lower than Chris has projected. He may need additional capital to cover his expenses. Say the first months revenue is only half of his projection. Then monthly sales grow 5% per month until they reach his projected amount in month 11 (multiply his projection for month 1 by .5, for month 2 by .55, for month 3 by .6, , and for months 11 and 12 by 1.0). Assume his employee wages are at least 10,000 per month. Will he need additional capital? If so, how much?

Question 2: Chris is beginning his planned expansion in Year 2. Your assignment is to create both income and operating cash flow statements for Year 2, which will show projected expansion and growth, consistent with Chriss vision. A major issue for growing firms is how to finance the growth. Can it be financed from cash flow, or is more outside capital needed? The pro forma statements can help answer that question.

After you create the pro forma statements for Year 2, answer the question: How much additional financing does Chris need for his expansion in Year 2? Do not make any assumptions about obtaining the new financing. Do not include new financing in your Year 2 cash flow projection. Just show how much he will need.

Guidance for creating the pro forma statements for Year 2:

Start with your adjusted numbers from Question 1 with lower revenue than Chris had projected.

Assume Year 1 includes a stand-alone location and a hosted location. Also assume the stand-alone location produces 2/3 of the sales revenue for Year 1.

To project revenue for Year 2, assume Chris opens 2 new stand-alone locations, and each performs like Year 1 for the first stand-alone location (using the lower numbers from Question 1). Also, assume both of the original locations see a 25% increase in sales for Year 2 over hisprojection for Year 1 (not the lower numbers from Question 1).

Make appropriate assumptions about expenses to project Year 2 income and cash flow. Your assumptions are a key part of your financial projections. Please state your assumptions for each line item on the spreadsheet. Do not simply assume all expenses will increase by the same fixed percentage.Look at each expense and estimate how much it would increase with two new locations.

The cash flow statement should show startup costs for the two new locations. You will have to create a table of startup costs for the new locations for Year 2 and include them in the Year 2 cash flow. Make reasonable assumptions about the startup costs for the new locations based on the startup costs from Year 1. Again, state your assumptions for each of the startup costs on your spreadsheet. And, again, do not simply assume all startup costs will increase by the same fixed percentage. Look at each startup cost and consider how much you would spend for the two new locations. For simplicity, you may enter the Year 2 startup costs in the Year 2 income and cash flow statement in a pre-opening period, as we did in Year 1.

All the statements should show monthly amounts and annual totals.

What to turn in: Write out your answers to Questions 1 and 2 above. You may put them on the spreadsheet or in a separate document. Turn in your pro forma income and cash flow statements for Year 1 for Question 1 and Year 2 for Question 2. Print your results with one statement per page in landscape format, that is, first year income statement on one page, first year cash flow statement on one page, and so on. Use the fit to page feature. Also print out your table of startup expenses for Year 2. Be sure to show your assumptions for Year 2 expenses and startup costs, either directly on the spreadsheet or in a separate document.