Francesas Southwestern Cuisine Francesa Lewis has worked in the restaurants for over 20 years and has most recently been manager of a local restaurant in Williamsville, a town of about 30,000 in the Midwest. Francesa has decided to resign her position and go into business for herself. She plans to open a restaurant specializing in new southwestern cuisine. Currently, there are two Mexican restaurants in Williamsville, but neither of them offers the new southwestern cuisine that Francesa plans to offer. Francesa set out to develop the information needed to project revenues, expenses, and net income for her restaurant for the first five years of operation. Based on her long experience in the business, Francesa has been able to develop rather precise estimates of her operating expenses. She has also developed estimates of expected revenues, although she feels that her revenue estimates are much more judgmental and subject to error. The information that Francesa has been able to collect about her expected costs and revenues is summarized below. Since her operations will involve a variety of different items selling at different prices, Francesa has estimated the variable costs of operation as percentages of sales revenue. Francesas estimates of these costs are as follows: Food supplies 25% Nonfood supplies 6% Labor costs 20% Miscellaneous expenses 3% As variable cost elements change over time, Francesa expects to make pricing adjustments that will keep the costs relatively stable in percentage terms. Thus, she estimates that the variable cost percentages shown will remain constant over the five-year period. In addition, Francesa will face costs for the building and equipment for her restaurant and for utilities. Francesa views these as fixed costs. She does not plan to purchase either the building or the equipment. She will lease a site and rent the needed equipment based on the annual lease and rental agreements. She has identified three potential sites for her restaurant. One is on Market Street downtown, the second is on Lakeview Drive at the Western edge of town, and the third is on Highway 87 at the North end of town. The Market Street site is the most expensive, but it is also the most central location, likely to attract more business. The Highway 87 site, while at the north edge of town, is still readily accessible and quite visible. The Lakeview drive site has a substantially lower rent but is much farther from natural traffic patterns. Francesa plans to advertise extensively during the first year of operation in order to establish her restaurant in the minds of consumers. Because of its less prominent location, Francesa believes that she would need $21,000 in advertising to establish her business at the Lakeview location, while she would need only $12000 in the first year of advertising at each of the other locations. After the first year, she expects to do minimal advertising of $5,000 per year regardless of the site selected.

Francesa anticipates that the same amount of equipment will be needed at any location. Utilities costs are estimated to be $6,500 at the Main Street Location and $7,200 at the Riverview Drive location for the first year. She can sign a five-year lease for either site so that the lease cost will remain constant over the five-year period. However, most of her equipment is rented on an annual (or shorter) basis Francesa expects equipment rental costs to increase about 3% per year and she expects utility costs to increase by 2% per year. These rates of increase will be the same in either location. Her estimates of the annual costs for the building, equipment, and utilities are as follows: Site Lease: Market Street site: $98,000 Lakeview Drive site: $66,000 Highway 87 site: $87,000 Advertising First Year: Market Street site: $12,000 Lakeview Drive site: $21,000 Highway 87 site: $12,000 Advertising Year 2 through 5: $5,000 (per year) Equipment Rental: $24,000 (+3 % per year increase) Utilities: Market Street site: $12,500 (+2 % per year increase) Lakeview Drive site: $14,200(+2 % per year increase) Highway 87 site: $14,800 (+2 % per year increase) Francesa also has developed estimates of projected sales levels. She expects that she should be able to have sales of $525,000 at the Market Street site by the second year of operation. Because the Lakeview Drive site is more remote, she would expect only $375,000 in sales by the second year at the site. While she feels that the Highway 87 site is almost as good as Market Street and could generate a sales level of $500,000 by year 2. Francesa also has some definite ideas about how the level of sales will change over time. Her experience has shown that sales for this type of business in its first year are typically only about 65% of the level achieved in the second year. After the second year, she expects sales to grow at about 10% per year for the remainder of the first five years. Her revenue estimates can be summarized as follows: Base Sales Level (2nd year Sales): Main Street site: $525,000 Riverview Drive site: $375,000 Highway 87 site: $500,000 First-year sales percentage: 65% (% of base sales level) The growth rate for 3rd and succeeding Years: 10%

Francesa would like you to create a spreadsheet based on the estimates above. She wants the spreadsheet to provide projections of sales revenues, costs by category, and net pretax income for each of the first five years. She also wants these projections to be presented for each of the three potential sites for comparative purposes. Because many of the estimates she has given you are subject to uncertainty or error, she would like to be able to interact with the finished spreadsheet herself to see the impact of changes in any of the estimates she has made. Net pretax income is equal to sales revenue minus total cost. Total cost is equal to total fixed cost plus total variable cost. The other formulas to generate the needed projections have been described above. Assignment: 1. Based upon the application description provided develop a spreadsheet for Francesas Southwestern Cuisine restaurant that will help Francesa Lewis evaluate the revenue prospects of each potential site. Your spreadsheet should have parameter and reporting areas as described above and should allow Francesa to change selected parameters and see their impact on the projections. Test your application for accuracy and completeness. Add appropriate controls to allow Francesa to use the spreadsheet with minimal risk of loss or damage. 2. Write a memorandum to Francesa Lewis and attach a copy of the spreadsheet file you created for her. The spreadsheet should show projections for the base parameters she supplied and projections generated by at least two other sets of parameter values. Your memorandum should highlight key findings.