Tutorial 3 Production Test 3 Instructions

Eva Lorraine needs to analyze the monthly costs for the chain of LaSala restaurants that she manages. She has created a spreadsheet in Excel but is unsure as to how to create formulas. She has done most of the formatting work on the spreadsheet but needs your help in figuring out the numbers.

Calculating the Costs

1) Start Excel and open Production Test 3 Spreadsheet.

2) Save the spreadsheet as Sales Analysis.

3) Use the TODAY function to enter the current date in cell A2.

4) Use the fill handle to complete the months of the year from February to June.

5) Select the range B4:G10, use the Quick Analysis tool to enter the SUM function to calculate the total for each month, and then widen the columns so the totals are visible.

6) In cell H4, use the SUM function to calculate the total revenue for January through June's revenue for Chicago, and then copy the formula for the other restaurant locations and for the total in row 11.

7) Format the range H4:H11 with the Accounting Number format and verify that the total is $816,479.00.

8) In cell B14, enter the correct function for calculating the average for January through June's revenue based on the values in the range H4:H10.

9) In cell B15, enter the correct function for calculating the minimum amount listed for January through June's revenue based on the values in the range H4:H10.

10) In cell B16, enter the correct function for calculating the maximum amount listed for January through June's revenue based on the values in the range H4:H10.

11) Revise the formula in cell I4 to make cell B17 an absolute reference, and then copy the formula for the remaining cities and for the total row.

12) Change the value in cell B17 to 15%; widen columns as needed.

13) In cell J4, enter the formula to add cells H4 and I4, copy the formula to cell J11, and then apply the Accounting Number format and widen the column.

14) In cell K4, use the VLOOKUP function to enter a ranking for the total incell J4. Use the table in the range D14:E18 as the array. Note that you must make the cell addresses in the table absolute by pressing the F4 function key. Copy the VLOOKUP formula to cell K10.

15) In cell L4, use the IF function to enter "Yes" if the value in cell J4 is greater than or equal to $100,000 and "No" if the value is less than $100,000.

16) In cell H11, use Goal Seek to set the value in cell H11 to 900,000 by changing the value in cell B4 (the January sales for Chicago).

17) Widen columns as needed.

18) Enter the new sales for Chicago in January in cell B18. Remember to enter the cell address, not the value.

19) Format the worksheet in Landscape orientation and scale the sheet to print all columns on one page.

20) Insert a heading with your name on the left, the filename centered, and the sheet name on the right. Use codes when possible.

21) Insert a footer with the page number and number of pages (Page # of #). Use codes when possible.

22) Save your file and submit it to your instructor