Jasmine has been the manager of J&J’s Convenience Store for the past two years. Her store sells the usual array of candy, soft drinks, water, chips, lottery tickets, etc. Jasmine’s Point of Sale system provides data on the number of customers and each customer’s purchase value (Average Sale). Jasmine has been analyzing the number of customers and the average spend per customer (ie Sales per Customer). She is interested in whether she should try to get each customer to purchase more each visit or to attract more customers.

She has asked you to develop a spreadsheet to answer these questions.

1. Open the J&J worksheet.

2. Currently, the store has an average of 11,000 customers per month. In the Income Statement, input the data below and calculate the total lines that are highlighted in green on the worksheet: Revenue Customers per month 11,000 Thursday 4 Average sale per customer $5.00 Variable Expenses COGS percentage of revenue 60% Fixed Expenses Maintenance $500 Salary $6,300 Store lease $1,000. Insurance 800

3. Use Goal Seek to calculate the number of Customers per month required to break even. In cells B27 to B29, enter the three cell references or numbers that you used in Goal Seek. In cell B30 enter the number of customers that returns the breakeven value. Return the Customers per Month to 11,000.

4. Create a one-variable data table in the shaded area E5:H15. Enter Customers, Revenue, Total Expenses, and Net Income in the top row. Enter Customers between 3,000 and 11,000, in increments of 1,000 in the left column. Complete the table with appropriate input cells and format the table with commas, no decimal places.

5. In the box below the data table (E18:K27), create a two-variable data table that displays Net Income for monthly Customers between 7,000 and 14,000, in increments of 1,000 units in E20:E27, and for Average Sales per Customer from $4.00 to $6.50 in increments of $0.50 in F19:K19. Complete the table with appropriate input cells and format, so that it is easy to read.

6. Using the custom number format, rename the cell at the top left of the table “Customers”.

7. Assign names to the following cell ranges: B6:B7, B15:B18, B22:B24, using the text contained in column A to the left.

8. Set up four scenarios with the following data: Status Quo Increased Sales Value Increased Customers Increased Customers & Sales Value Customers 11,000 11,000 16,000 16,000 Avg Sale / customer $5.00 $6.50 $5.00 $6.50 Maintenance $500 $600 $750 $750 Salary 6300 6300 7000 7000 Lease 1000 1000 1500 1500

9. In the “Comment” box of Scenario Manager, enter your name and today’s date. 

10. Create a Scenario Summary Report, for the four scenarios above that shows total revenue, total expenses, and net income