Description
Tutorial 3 Production Test 3 Instructions
Eva Lorraine needs to analyze the monthly costs for the chain of LaSala restaurants thatshe manages. She has created a spreadsheet in Excel but is unsure as to how to createformulas. She has done most of the formatting work on the spreadsheet but needs yourhelp in figuring out the numbers.Calculating the Costs1)Start Excel and open Production Test 3 Spreadsheet.2)Save the spreadsheet asSales 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 functionto calculate the total for each month, and then widen the columns so the totals arevisible.6)In cell H4, use the SUM function to calculate the total revenue for Januarythrough June’s revenue for Chicago, and then copy the formula for the otherrestaurant locations and for the total in row 11.7)Format the range H4:H11 with the Accounting Number format and verify that thetotal is $816,479.00.8)In cell B14, enter the correct function for calculating the average for Januarythrough 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 listedfor 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 listedfor 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 thencopy 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 celladdresses in the table absolute by pressing the F4 function key. Copy theVLOOKUP formula to cell K10.15)In cell L4, use the IF function to enter “Yes” if the value in cell J4 is greater thanor 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 thevalue 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 thecell address, not the value.19)Format the worksheet in Landscape orientation and scale the sheet to print allcolumns on one page.20)Insert a heading with your name on the left, the filename centered, and the sheetname on the right.Use codes when possible.21)Insert a footer with the page number and number of pages (Page # of #).Usecodes when possible.22)Save your file and submit it to your instructor.
