Reference no: EM133793764

Project 1

Ch05_HOE - Sociology 1.2

Project Description:
You are the vice president of the Sociology Division at Ivory Halls Publishing Company. Textbooks are classified by an overall discipline. Books are further classified by area. You will use these classifications to see which areas and disciplines have the highest and lowest sales. The worksheet contains wholesale and retail data. You want to analyze sales for books published in the Sociology Division. To do this, you will organize data by discipline and insert subtotal rows. You will also create PivotTables to gain a variety of perspectives of aggregated data. Finally, you will create a PivotChart to depict the aggregated data.

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Download and open the file named Exp22_Excel_Ch05_HOE_Sociology.xlsx. Grader has automatically added your last name to the beginning of the filename.
0
2 Your first task is to sort the dataset on the Books Subtotal worksheet.

Ensure the Books Subtotal worksheet is active. Sort the data by Discipline in alphabetical order and further sort it by Area in alphabetical order. 3
3 You want to subtotal the dataset by Discipline and then add a second set of subtotals by Area.

Use the Subtotal feature to insert subtotal rows by Discipline to calculate the totals for the Sales Wholesale, Sales Retail, and Total Book Sales columns. Without removing the first subtotals, insert subtotals by Area to calculate totals for the same columns. 5
4 Next, you want to apply an outline to the columns so that you can collapse or expand the Units Sold and Unit Price columns.

Create an automatic outline. Collapse the outline above columns H and K. 3
5 Next, you want to create a recommended PivotTable and then name it.

Display the Books Data worksheet and create a recommended PivotTable using the Sum of Units Sold thumbnail. Change the name of the worksheet to PivotTable. Name the PivotTable Total Book Sales. 5
6 You want to compare total book sales by discipline and copyright year.

Make sure these fields are in the respective areas. Remove extra fields. Place the Discipline field in rows, Total Book Sales field as values, and Copyright field in columns. 6
7 You will format the values in the PivotTable to look more professional and change the custom names that display as column headings.

Click or select cell B5, display the Value Field Settings dialog box, and type Sales by Discipline as the custom name. Apply Accounting Number Format with zero decimal places. 2
8 You want to replace the generic Row Labels and Column Labels headings with meaningful headings.

Type Discipline in cell A4 and Copyright Year in cell B3. Select the range B4:E4 and center the labels horizontally. 4
9 On the Books Data sheet, you want to insert functions that will display the total sales and the total Introductory discipline sales data from the PivotTable. You will change the retail unit price rate from 30% to 25% and then refresh the PivotTable.

Display the Books Data worksheet. In cell B1, enter the GETPIVOTDATA function to get the value from cell F10 in the PivotTable worksheet. In cell B2, enter the GETPIVOTDATA function to get the value from cell F7 in the PivotTable. Change the value in cell J1 to 125 in the Books Data worksheet, and then refresh the PivotTable. 5
10 You will add a field to the Filters area so that you can filter the list by Edition.

Add the Edition field to the Filters area. 4
11 Because you plan to distribute the workbook to colleagues, you will insert a slicer to help them set filters.

Insert a slicer for Discipline. Move the slicer so that the top-left corner is just inside the corner of cell A13. 6
12 After inserting the slicer, you want to change the dimensions and appearance of it.

Change the slicer to display 2 columns. Change the button width to 1.5 inches. Change the slicer height to 2 inches. Apply Light Blue, Slicer Style Dark 1. 5
13 You want to calculate the amount of the sales returned to the authors as royalties.

Create a calculated field with the name Author Royalties. Multiply Total Book Sales by *.1. In cell C5, use the Field Settings to type the custom name Authors' Royalties. 5
14 Format cells C5, E5, G5, and I5 with Center horizontal alignment and wrap text. Change the height of row 5 to 30. Change the width of columns C, E, G, and I to 12. 4
15 You want to display the Total Book Sales as a percentage of the grand total.

Select the Total Book Sales and show the values as a percentage of the grand total. 6
16 To enhance the readability of the sociology textbook PivotTable, you will change the PivotTable style.

Apply Light Blue, Pivot Style Medium 2 to the PivotTable. Display banded columns. 4
17 The Books, Disciplines, and Editors worksheets contain tables You will create relationships between the Books table and the other two tables.

Display the Books worksheet. Create a relationship between the BOOKS table using the Discipline Code field and the DISCIPLINE table using the Disc Code. Create a relationship between the BOOKS table using the Editor ID field and the EDITOR table using the Editor ID field. 5
18 Now that you built a relationship between the tables, you can create a PivotTable using fields from all three tables.

Create a blank PivotTable from within the Books worksheet and add the data to the data model. Name the worksheet as Relationships. 5
19 You are ready to add fields from the three tables to the PivotTable.

Display all tables within the PivotTable Fields pane. Place the Total Book Sales field from the BOOKS table in the Values area. Place the Discipline field from the DISCIPLINE table in the Rows area. Place the Editor Last field from the EDITOR table below the Discipline field in the Rows area. 5
20 Format the Total Book Sales field with Accounting Number Format with zero decimal places. 2
21 You want to create a PivotChart to display percentages in a pie chart.

Create a PivotChart from the PivotTable you just created. Change the chart type to a pie chart. Cut the chart and paste it just inside the top-left corner of cell C1. 5
22 The PivotChart has too much detail. You will filter the chart to display only one discipline.

Filter the PivotChart by displaying only the Family discipline. Sort the PivotTable by the Total Book Sales from largest to smallest. 5
23 The chart needs a meaningful title.

Change the chart title to Family Discipline Book Sales. 2
24 You want to add data labels to provide more details to the chart.

Display data labels in the best fit position with only the Percentage labels. Deselect the Values data labels. 4
25 Save and close Exp22_Excel_Ch03_ HOE_Sociology.xlsx. Exit Excel. Submit the file as directed. 0

Project 2
Exp22_Excel_Ch06_HOE - Mortgage

Project Description:
After several years of living with friends after college, you have decided to purchase your first home. After doing some preliminary research on prices, you developed a spreadsheet to calculate your monthly mortgage payment, the total amount to repay the loan, and the total amount of interest you will pay. Your total budget for the home is $150,000 including taxes, closing costs, and other miscellaneous fees. You plan to take $10,000 out of your savings account for a down payment. You are currently investigating loan interest rates at various banks and credit unions. You realize that you may need to find a less expensive home or increase your down payment to reach a monthly payment you can afford. Although you know a larger down payment will change the monthly payment, you want to be able to see the comparisons at the same time. In addition, you want to look at your budget to review the impact of purchasing a new home on your income and expenses.

You will use Excel to help create a worksheet to analyze the variables that affect the mortgage payment, the total amount to repay the loan, and the total interest paid. To help you make a decision, you will use several what-if analysis tools, each with specific purposes, benefits, and restrictions. With these tools, you will have a better understanding of how a mortgage payment will affect your overall budget.

Steps to Perform:

Step Instructions Points Possible
1 Download and open the file named Exp22_Excel_Ch06_HOE_Mortgage.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 As you complete your mortgage calculations, you would like to use range names to make the formulas easier to understand.

Create the range name PurchasePrice for cell B2 and DownPayment for cell B3. 3
3 You have decided to utilize an alternative method of creating range names for the remainder of the variables.

Create range names for the range A9:B13 using the create from selection method. 6
4 You would like to edit the previously created named ranges. To do so, you will use the Name Manager.

Delete the Monthly_Payment named range. Edit the DownPayment named range so it appears as Down_Payment and edit PurchasePrice to appear as Purchase_Price. 2
5 You would like to apply the newly created named ranges to the existing formulas in the worksheet.

Apply defined names to the existing formulas on the Home Loan worksheet. Once completed, named ranges should appear in the cells B12 and B14. 5
6 You would like to document the named ranges by inserting them as a list on a new worksheet.

Create a new worksheet named Range Names. Insert the previously created range names on the worksheet starting in cell A2. Set the width of columns A:B to AutoFit. 8
7 As you continue your analysis, you would like to create a one-variable data table to explore the impact of various interest rates on the payment, total interest, and total amount repaid. To complete the table, you will first create substitution values.

On the Home Loan sheet, beginning in cell D4, enter substitution values from 4% to 6% increasing in .25% increments, completing the range D4:D12. 5
8 To continue the construction of the one-variable data table, you will add formulas to the table structure.

Add a reference to the cell the contains the monthly payment (B12) in cell E3. Add a reference to the cell that contains the total to repay loan in cell F3 (B13), and add a reference to the cell that contains total interest paid (B14) in cell G3. 3
9 Complete the one-variable data table using the APR in cell B4 as the column input. 5
10 You would like to format the one variable data table to add clear labels that are easy to understand.

Apply Accounting Number Format to the range E4:G12. Type APR in cell D3. Next, create the following custom formats for the range E3:G3.

Cell E3 custom format = Payment
Cell F3 custom format = Total Repaid
Cell G3 custom format = Total Interest 3
11 You would like to create a two-variable data table to explore the impact of changing interest rates and home costs on the monthly payment. To begin creating the structure of the table, you will enter substitution values for the home cost and interest rate.

Enter 150000, 175000, and 200000 in the range J3:L3. Format these values with Accounting Number Format with two decimals. Expand the column width as needed if pound signs (#) appear.

Start in cell I4. Complete the series of substitution values ranging from 4% to 8% in increments of .25% vertically down, stopping in cell I20. 5
12 Enter a reference to the monthly payment (B12) in cell I3 and complete the two-variable data table using the cost of the home (cell B2) as the Row Input and the APR (cell B4) as the Column Input. 6
13 As your last step, you would like to create a custom number format for cell I3.

In cell I3, apply a Custom number format to display APR. Center and bold the contents in cell I3. 3
14 To continue your analysis, you will use Goal seek to determine the optimal purchase price to obtain the desired monthly payment of $600 without changing the down payment or APR.

Use Goal Seek to set the monthly payment (cell B12) to $600 by changing the purchase price (cell B2). 0
15 To enhance your analysis you will use Scenario Manager to create best, most likely, and worst case scenarios that can be documented in a scenario summary report. To begin this step, you will start with creating scenarios.

Create the following scenarios using Scenario Manager.

Best-Case Scenario
Changing cells B2:B5
B2 = 150000
B3 = 10000
B4 = 2.25%
B5 = 15

Worst-Case Scenario
Changing cells B2:B5
B2 = 150000
B3 = 45000
B4 = 6%
B5 = 45

Most Likely Scenario
Changing cells B2:B5
B2 = 150000
B3 = 30000
B4 = 42.5%
B5 = 30 6
16 After creating the three scenarios, you notice an error. You will edit the Most Likely Scenario to correct the mistake.

Edit the Most Likely Scenario to reflect the correct interest rate of 4.25% in cell B4. 1
17 You would like to create a Scenario Summary report to document the three scenarios.

Create a Scenario Summary report that reflects the range B12:B14. 5
18 You would like to format the Scenario Summary report using the formatting best practices discussed in the text.

Delete Column A:B, Row 1, Current Values column.

Enter the following values in the respective cells:
A5 = Cost of Home
A6 = Down Payment
A7 = APR
A8 = Years
A10 = Monthly Payment
A11 = Total to Repay Loan
A12 = Total Interest Paid 4
19 Although Goal Seek and Scenario Manager were helpful in further analyzing your home purchase, you want to ensure the spreadsheet model imposes constraints on the situation. Therefore, you will continue your analysis by using Solver.

Switch back to the Home Loan worksheet. If necessary, load the Solver Add-in. Set Solver to use cell B12 as the Objective and set it to the value of 1000. 3
20 Set Cost of home (B2), Down Payment (B3), APR (B4), and years financed (B5) as the Changing Variable cells, (B2:B5). 4
21 Set constraints that ensure the cost of the home (B2) is between $100,000 and $300,000 (greater than or equal to 100000 and less than or equal to 300000). 4
22 Set constraints to ensure the down payment (B3) is between $5,000 and $10,000 (greater than or equal to 5000 and less than or equal to 10000). 4
23 Set constraints to ensure the APR (B4) is between .04 and .06 (greater than or equal to .04 and less than or equal to .06). Be sure to enter decimal values for these constraints. 4
24 Set constraints to ensure the years financed (B5) are between 15 and 30 and are whole numbers. 6
25 Click or press Solve, retain the solution values, and generate an Answer Report. 5
26 Save and close Exp22_Excel_Ch06_HOE_Mortgage.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100

Project 3 Ch05_CumulativeAssessment_Travel

Project Description:
You are the manager of an information technology (IT) team. Your employees go to training workshops and national conferences to keep up-to-date in the field. You created a list of expenses by category for each employee for the last six months. Now you want to subtotal the data to review total costs by employee, and then create a PivotTable to look at the data from different perspectives.

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Download and open the file named Exp22_Excel_Ch05_CumulativeAssessment_Travel.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 Before using the Subtotal command, you must first sort the data.

On the Subtotals worksheet, sort the data by Employee and further sort by Category, both in alphabetical order. 5
3 You want to subtotal the data to calculate expenses by employee.

Use the Subtotals feature to insert subtotal rows by Employee to calculate the total expense by employee. Select Summary below data. 5
4 Now you want to hide the details for two employees and focus on details for the remaining employees.

Collapse the Davidson and Miller sections to show only their totals. Leave the other employees' individual rows displayed.
5
5 For more detailed analysis, you are ready to create a PivotTable.

Use the Expenses worksheet to create a blank PivotTable on a new worksheet named Summary. Name the PivotTable Categories. 6
6 You want to include the Category and Expense fields in the PivotTable.

Use the Category and Expense fields, enabling Excel to determine where the fields go in the PivotTable. 6
7 You want to display average expenses instead of totals.

Modify the Values field to determine the average expense by category. Change the custom name to Average Expense. 5
8 Format the Values field with Accounting Number Format. 4
9 You want to display a meaningful label and select a different layout.

Type Category in cell A3 and change the Grand Totals layout option to On for Rows Only.
5
10 Apply Light Blue, Pivot Style Dark 2 and display banded rows. 5
11 To make it easy to filter the PivotTable results, you will insert and format a slicer.

Insert a slicer for the Employee field, change the slicer height to 2 inches and apply the Light Blue, Slicer Style Dark 5. Move the slicer so that the upper-left corner is in cell A10. 6
12 You want to insert another PivotTable to analyze the data on the Expenses worksheet.

Use the Expenses worksheet to create another blank PivotTable on a sheet named Totals. Name the PivotTable Employees. Add the Employee to the Rows and add the Expense field to the Values area. Sort the PivotTable from largest to smallest expense.
10
13 Change the name for the Expenses column to Totals and format the field with Accounting Number Format.
5
14 Insert a calculated field to subtract 2659.72 from the Totals field. After creating the field, change the custom name to Above or Below Average and apply Accounting number format.
10
15 Set 10.86 (approximate) as the width for column B and 13.71 for column C, change the row height of row 3 to 30, and apply word wrap to cell C3. 4
16 Insert the GETPIVOTDATA function in cell F1 in the Expenses worksheet. It should retrieve the value from cell B9 in the Totals worksheet. 5
17 Now, you want to create a PivotChart to depict the data visually.

Create a clustered column PivotChart from the PivotTable. Move the PivotChart to a new chart sheet named Chart. Hide all field buttons in the PivotChart, if necessary. 8
18 Add a chart title above the chart and type Expenses by Employee. Change the chart style to Style 14. 2
19 Apply 11 pt font size to the value axis and display the axis as Accounting Number Format with zero decimal places.
4
20 Save and close Exp22_Excel_Ch05_CumulativeAssessment_Travel.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100

Project 4

Ch06_Cumulative_AutoSales

Project Description:
You are the finance manager for Grossman Auto Sales. Your company offers in-house financing and as part of your duties, you create reports detailing purchase options. You want to create a report to determine the optimal purchase price of a vehicle based on your customer's budget. You also want to create one- and two-variable data tables to provide detailed payment options based on varying interest rates and down payments.

Steps to Perform:

Step Instructions Points Possible
1 Download and open the file named Exp22_Excel_Ch06_Cumulative_AutoSales.xlsx. Grader has automatically added your last name to the beginning of the filename. 0
2 Create appropriate range names for Purchase Price (cell C5), Sales Tax (cell C6), Down Payment (cell C7), Months Financed (C8), APR (cell C9), Down Payment Amount (cell C12), Amount Financed (cell C13), and Tax Owed (cell C14). Create the range names using the Create from Selection method, using the values in the left column. 6
3 Edit the existing name range Tax_Owed to display as Tax. Note, Mac users, in the Define Name dialog box, add the new named range, and delete the original one. 4
4 Apply the range names to the existing formulas and functions in the worksheet. 4
5 Create a new worksheet labeled Range Names, paste the newly created range name information in cell A1, and then resize the columns as needed for proper display. 6
6 Use Goal Seek to determine the optimal purchase price to reach a $500 monthly payment with all other variables remaining unchanged. 2
7 Start in cell E5. Complete the series of substitution values ranging from $20,000 to $75,000 at increments of $5,000 vertically down column E. Apply Comma Style format to the range E5:E16 with two decimal points and preserve the thick bottom border on row 16. 3
8 Enter references to the Down Payment Amount, Tax Owed, and Monthly Payment (Cells F4, G4, and H4) in the correct location for a one-variable data table. Use range names where indicated. 3
9 Complete the one-variable data table and format the results with Comma Style with no decimal places. 5
10 Apply Custom number formats to cell F4:H4 that displays the text Down Payment in cell F4, Tax in G4, and Monthly Pmt in cell H4. Bold and center the headings and substitution values. 3
11 Copy the purchase price substitution values from the one-variable data table and paste the values starting in cell J5. Adjust the width of column J as needed. 3
12 Type 5000 in cell K4. Complete the series of substitution values from $5,000 to $15,000 at $5,000 increments. Apply Accounting Number Format to the range K4:M4. 3
13 Enter the reference to the Monthly Payment function in the correct location for a two-variable data table. 3
14 Complete the two-variable data table and apply Comma Style to the range K5:M16 with no decimal places. 9
15 Apply a Custom number format to make the formula reference display as the descriptive column heading Purchase Price. Bold the headings in cells J4:M4. 4
16 Create a scenario named Best Case, using Purchase Price and Months Financed. Enter these values for the scenario: 40000, and 36. 4
17 Create a second scenario named Worst Case, using the same changing cells. Enter these values for the scenario: 50000, and 72. 4
18 Create a third scenario named Most Likely, using the same changing cells. Enter these values for the ­scenario: 45000, and 60. 4
19 Generate a Scenario Summary report based on Monthly Payment. Be sure to use best practice in formatting the report by deleting Column A:B, Row 1, and the Current Values column. 5
20 Load the Solver add-in if it is not already loaded. Set the objective to calculate a Monthly Payment of $500. 5
21 Use Purchase Price and Months Financed as changing variable cells. 4
22 Set constraints to ensure the Purchase Price is less than or equal to $50,000, greater than or equal to $30,000, and a whole number. 5
23 Set constraints to ensure months financed are less than or equal to 72, greater than or equal to 24, and a whole number. 6
24 Solve the problem and save the results. Generate the Answer Report. If you get an internal memory error message, remove Solver as an add-in, close the workbook, open the workbook, add Solver in again, and finish using Solver. 5
25 Save and close Exp22_Excel_Ch06_Cumulative_AutoSales.xlsx. Exit Excel. Submit the file as directed. 0
Total Points 100