Exploring Excel 2013 Exam 2 Ch. 5 - 8 J
You were recently hired as the manager at the popular Crane Dale Market Deli. You were given a workbook that is incomplete and need to update it by adding lookup functions, profit calculations, payment information, and employee analytics. Because you are a recent hire, your job depends on your accuracy. Once completed, your boss will look over it for final approval before it goes to the owner.
For the purpose of grading the project you are required to perform the following tasks:
Start Excel. Open CraneDaleExam2.xlsx and save the workbook as CraneDaleExam2_LastName_FirstName.xlsx(Substituting your first and last name.)Make sure the Lookup Tables sheet is active.
Assign the range name WeekDay to the weekday range. Assign the name range Quarters to the quarter breakpoint range.
On the Restaurant Data Worksheet, use a lookup function to display the day of the week in column B<hint> You will need to use a nested function to first find the week day number, then use the table names saved from the first step <hint>
On the Restaurant Data Worksheet, use a lookup function to display which quarter the month is (If the month is 12 then you are in quarter 4).<hint> You will need to use a nested function to first find the month, then use the table names saved from the first step <hint>
While still on the Restaurant Data worksheet, create a custom sort that will first sort by Quarter in alphabetical order, then byWeekday starting with Sunday.
Subtotal the data by Quarter to find the total sales for Lunches and Dinners. Create a page break after eachQuarter and have the summary display below the data.Collapse the data to only display the Subtotals and the Grand Total. Then adjust the column width of Column E to 13.00.
On the Overview Worksheet, create a formula to determine the Income from cash and the income from credit (E4 and E5). The Income from credit is calculated by taking the total income multiplied by the percentage of credit card sales per month. The Income from cash is calculated by taking the Total income and subtracting the Income from credit.Calculate the Variable Costs (K4 and K5). Remember to make sure you add in the cost per swipe and the cost per transaction for the Credit Card Processing Fees in K5.Calculate the Net Profit in cell E12.
Use the goal seek to determine the break-even point by changing the number of sliders sold. Change B4 to the Number formatting with 0 decimal places.
Fill in the One-Variable data table by making a reference to the Total Income in cell B16, the Total Variable Costs in Cell C16, and the Net Profit in cell D16.Display the results in the Accounting format with NO decimal places.Change the values to display the text values for each of the references in cells B16:D16<hint> B16 should display as Total income, C16 should display as Variable Costs, and D16 should display as Net Profit <hint>
Fill in a series cells G16:J16 starting with 3.00 and incrementing by 1.00 stop at 6.00Fill in the Two-Variable data table by making a reference to the Net Profit in cell F16.Display the results in the accounting format with NO decimal places.Change the value to display the text values for Cell F16 as Sliders Sold.