Reference no: EM131433675
Analyzing Sales
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 CIN506_Excel Assignment_LaSala.xlsx.
2) Save the spreadsheet as Excel Assign_LastName_SXXXX_LaSala.xlsx. (Replace "Last Name" with your last name and SXXXX with your Student ID number.)
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 in cell 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.
15) Copy the VLOOKUP formula to cell K10.
16) 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.
17) 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).
18) Widen columns as needed.
19) Enter the new sales for Chicago in January in cell B18. Remember to enter the cell address, not the value.
20) Format the worksheet in Landscape orientation and select options to ensure the worksheet prints on one page.
21) Save your file and submit your assignment via Moodle Assignment 2 submission box.