Project Description:
You were recently hired as the manager at the popular KoKo's 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.
Instructions
1. Start Excel. Open KoKosDeli.xlsx and save the workbook as KoKosDeli_LastName_FirstName.xlsx (Substituting your first and last name.)
Make sure the Deli Data worksheet is active.
2. While on the Deli Data worksheet, create a custom sort that will first sort by Quarter in alphabetical order, then by Weekday starting with Sunday.
3. Subtotal the data by Quarter to find the total sales for Lunches and Dinners. Create a page break after each Quarter 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.
4. 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 MUST be calculated by taking the Total income and subtracting the Income from credit.
5. Calculate the Variable Costs (K4 and K5). Remember to make sure you add in the cost per swipe and the Credit Card Processing Fees together.
Calculate the Net Profit in cell E12.
6. 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.
7. 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
B16 should display as
Total income, C16 should display as Variable Costs, and D16 should display as Net Profit (D16 will display as "-Net Profit")
8. Fill in a series cells G16:J16 starting with 3.00 and incrementing by 1.00 stop at 6.00 (This represents the Sales price).
Fill 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 (This will display as -Sliders Sold).
9. While still on the Overview Worksheet, use the scenario manager to add these three scenarios with the changing cells of $B$4:$B$6:
Name: Best Case B4: 6500 B5: 0.70 B6: 5.25
Name: Average Case B4: 2250 B5: 0.90 B6: 5.00
Name: Worst Case B4: 975 B5: 1.10 B6: 4.20Display the summary results of cells
E6, K6, and E12
Don't change any values on the new Scenario Summary worksheet
10. On the Mortgage worksheet, use the Present Value Function to determine the present value of the loan. Make the results display as a positive value in Cell A3.
Use the number of Payments Per Year to find the periodic rate and the Scheduled Number of Payments for the number of periods. Do not fill in the FV or Type AND the negative sign MUST go before the PV function.
11. Fill in the remaining loan amortization table using the correct formulas
Fill in ALL remaining dates by using date function; The interest column must use the IPMT function and the value MUST return a positive value with the negative sign before the function; The principle column must use the PPMT function and the value MUST return a positive value with the negative sign before the function
12. Calculate the Cumulative Interest column. You MUST use the CUMIPMT function and the "Type" will be 0 (Make the value return a positive number).
Calculate the Cumulative Principal column. You MUST use the CUMPRINC functionand the "Type" will be 0 (Make the value return a positive number).
13. On the Employee worksheet, In cell F6, determine if there is a correlation between Days Absent and employee satisfaction (Days column MUST go first in the formula).
Fill in the frequency cells using the frequency function.
14. Type 8843 in cell E2.
In cell F2, use an INDEX function with a MATCH function to find out the job satisfaction of the employee entered in cell E2.
Index array should include ONLY the data without headers.
15. Calculate the # of Employees with more than 5 days absent in cell F15 by using the COUNTIF function.
Find the average job satisfaction from employees with more than 5 days absent in cell F17 by using the AVERAGEIF function. Format F17 as a number with 1 decimal place.
Save the file.Make sure the worksheets are in this order:
16. Deli Data, Scenario Summary, Overview, Mortgage, Employee