Exploring Excel 2013 Exam 2 Ch. 5 - 8 J complete solutions correct answers key

Exploring Excel 2013 Exam 2 Ch. 5 - 8 J complete solutions correct answers key

Project Description:

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.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

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.

 

0.000

2

Assign the range name WeekDay to the weekday range. Assign the name range Quarters to the quarter breakpoint range.

4.000

3

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>
 

7.000

4

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>
 

7.000

5

While still on the Restaurant Data worksheet, create a custom sort that will first sort by Quarter in alphabetical order, then by Weekday starting with Sunday.

6.000

6

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.

8.000

7

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.
 

7.000

8

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.

6.000

9

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>

8.000

10

Fill in a series cells G16:J16 starting with 3.00 and incrementing by 1.00 stop at 6.00
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.

8.000

11

While still on the Overview Worksheet, use the scenario manager to add these three scenarios with the changing cells of B4, B5, B6:
Name: Best Case    B4: 2200    B5: 0.60   B6: 5.25
Name: Average Case   B4: 1150   B5: 0.75    B6: 4.90
Name: Worst Case   B4: 750    B5: 1.10    B6: 4.25

Display the summary results of cells
E6, K6, and E12
<hint> Don
’t change any values on the new Scenario Summary worksheet <hint>

8.000

12

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.
<hint> 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. <hint>

4.000

13

Fill in the remaining loan amortization table using the correct formulas
<hint> The remaining dates must use the
date function; The interest column must use the IPMT function; The principle column must use the PPMT function. <hint>

8.000

14

On the Employee worksheet, calculate the Max Satisfaction, Sample Size, and Average Satisfaction.
In cell E7, determine if there is a correlation between Days Absent and employee satisfaction.
 

5.000

15

Complete the
frequency cells F10:F13.
 

4.000

16

While still on the Employee sheet, Use the descriptive statistics to display the Satisfaction and Days absent summary statistics in cell E17 on the same sheet. Make sure the input range is B1:C51 and Labels in first row is selected.

8.000

17

Save the file.
Make sure the worksheets are in this order:


Restaurant Data, Lookup Tables, Scenario Summary, Overview, Mortgage, Employee
 

0.000

Total Points

98.000