INFO 165 - Virginia Commonwealth Univ PDF

Preview

Summary

Virginia Commonwealth Univ...

Description

CHAPTER 5

5.1.1 : Subtotal Data ● Sort the data by discipline and then by Area, both in Alphabetic order Data tab → Sort & Filter group → Sort → Click the Sort by Arrow → Discipline →Click Add level (on top) → Click the Then by Arrow → Area → OK ● Use the Subtotal feature to total the number of Wholesale Sales, Retail Sales, and Total Book Sales by Discipline. On the Data tab → Outline group → Click Subtotal → Click the Sales: wholesale checkbox → Click the Sales: Retail checkbox → Leave total book sales selected → OK 5.1.2 : Add a Second Subtotal ● Leave the existing subtotal values and add a second-level subtotal for Area. Data tab → Outline group → Subtotal → Click the At each change in arrow → Select Area → Click the Replace Current Subtotals checkbox → OK 5.1.3 : Collapse and Expand the subtotals ● Collapse the table to show the grand totals only. Click the 1 in the top-left outline area ● Expand the table to show the grand and discipline totals. Click the 2 in the top-left outline area ● Expand the table to show the entire table. Click the 4 in the top-left outline area 5.1.4 : Group and Ungroup Data ● Use the Auto Outline feature to group the columns. Data tab → Outline group → click the Group Arrow and select Auto Outline → OK ● Collapse the Total Book Sales. Click the Collapse button above column L ● Create a PivotTable using the Sum of Total Book Sales by Discipline

recommended PivotTable. Click cell A5 → Click the |Insert| tab → In the Tables group → Recommended PivotTables → Scroll to and click the Sum of Total Book Sales by Discipline thumbnail → OK ● Rename the new sheet as PivotTable. Double-click the Sheet1 tab → Type PivotTable → ENTER ● assign the name Total Book Sales to the PivotTable. PivotTable Tools → Analyze tab → Click in the PivotTable Name box in the PivotTable group → type Total Book Sales → ENTER 5.2.2 : Add rows, values, and columns ● Drag the Edition field to the Columns area in the PivotTable In the PivotTable Fields pane → Drag the Edition field under the columns area ● Add the Copyright field to display as a row between the Discipline and Book Title fields in the Rows area. Drag the copyright field under the row area ● Expand the Aging/Death discipline to display copyright years and book titles in the PivotTable. Click the Aging/Death Expand (+) button to the right of the row 5 heading 5.2.3 : Remove and Rearrange Fields ● Remove the Edition field from the PivotTable. In the PivotTable fields list → click the Edition checkbox to deselect it ● Remove the Book Title fields from the PivotTable. In the PivotTable fields list → click the Book Title checkbox to deselect it ● Move the Copyright field to the Columns area. Drag the copyright field from under the rows area to the column area 5.2.4 : Change the value field setting ● Click cell B5 and use the Value Field Settings dialog box to type the custom name Sales by Discipline for the values. Apply Accounting Number Format with zero decimal places.

Click cell B5 → On the pivotTable tools Analyze tab → In the Active Field group → click field settings → type Sales by Discipline in the Custom Name box → click number format → click Accounting in the CAtegory list → in the Decimal places box → type 0 → Click OK 5.2.5. : ● Display the Books Data Sheet Click the Books Data Worksheet tab → towards the bottom ● In cell B1 in the Books Data sheet, enter the GETPIVOTDATA function to cell F14 in the PivotTable sheet. Select cell B1 → type = → click the pivotTable worksheet tab → click cell F14 → ENTER ● In cell B2 in the Books Data sheet, enter the GETPIVOTDATA function to cell F8 in the PivotTable sheet. Select cell B2 → type = → click the pivotTable worksheet tab → click cell F8 → ENTER ● Change the Retail Price Rate to 125% in cell J1 in the Books Data worksheet. Click cell J1 → type 125% → ENTER ● Refresh the PivotTable. Click the PivotTAble worksheets tab → click the Pivot Table tools Analyze tab → Data group → Refresh ● View cell B1 in the Books Data sheet to see the updated results. Click the Books Data worksheet tab (at bottom) 5.3.1 : Add Filters ● Use the Edition field as a filter for the PivotTable and display only 1st and 2nd editions. In the PivotTable Fields List → drag the Edition field from the Choose fields to add under the filters area → Click the Edition filter arrow in cell B1 → click the Select Multiple Items check box → click the (All) check box to deselect it → click the 1 check box → click the 2 check box → OK ● Set a Copyright Year filter to display only 2020 and 2021. Click the copyright year filter arrow in cell B3 → click the (Select All) checkbox → click the 2020 checkbox → click the 2021 checkbox → OK

5.3.2 : Insert and Customize a Filter ● Insert a slicer for the Discipline field. On the PivotTable Tools Analyze tab → Filter group → click Insert Slicer → In the Insert Slicers dialog box → click the Discipline checkbox → OK ● Display two columns of buttons in the slicer, set the button width to 1.5 inches, and set the slicer height to 2 inches. Close any open task panes. On the Slicer Tools Options tab → Buttons group → click the columns spin box up arrow to display 2 → in the width box type 1.5 → ENTER → In the size group → select the value in the Height box, type 2 → ENTER ● Apply the Light Blue, Slicer Style Dark 1 style to the Discipline slicer. On the slicer tools options tab → slicer styles group → click More → click Light Blue, slicer style Dark 1 ● Cut the slicer and paste it in cell F1. With the slicer selected press CTRL + X → select cell F1 → press CTRL + V 5.3.3 : Create a calculated field ● Create a calculated field named Author Royalties that multiplies the Total Book Sales field by .1. Click cell C5 → click the PivotTAble Tools Analyze tab → Calculations group → click fields, items, sets → click calculated field → in the insert calculated field dialog box with the name box selected, type Author Royalties → in the fields list, scroll down → click Total Book Sales → click Insert field → with the insertion point in the formulas box → add on to the text and type *.1 → click ADD → OK ● Use the Value Field Settings to type the custom name Authors’ Royalties for the calculated field. Select cell C5 → PivotTAble Tools Analyze tab → Active Field group → click field settings → type Authors’ Royalties in the custom name box → 5.3.4 : Show values as calculations ● With cell B5 selected, show the book sales values as % of row total. On the PivotTAble Tools analyze tab → active field group → click field settings

→ click the show values as tab → click the Show values as arrow → select % of Row total → OK ● Show the values as % of grand total. PivotTAble Tools Analyze tab → Active Field Group → click Field Settings → click the show values as tab → click the show values as arrow → select % of Grand total → OK 5.3.5 : Change the PivotTable Style ● Apply the Light Blue, Pivot Style Medium 2 style to the PivotTable. Click the PivotTable Tools Design Tab → Click the more button (bottom arrow) in the pivottable styles group → select pivot style light blue… ● Apply banded columns to the PivotTable. PivotTable Tools Design Tab → Pivot Table Style Options group → click the banded columns check box 5.4.1 : Create Relationships ● Display the Disciplines sheet. Click the disciplines worksheet tab (bottom) ● Create a relationship between the BOOKS table using the Discipline Code field and the DISCIPLINE table using the Disc Code field. Close the Manage Relationships dialog box. Click the Data tab → In the Data Tools group click Relationships → In the Manage Relationships dialog box, click New → click the table arrow → click Worksheet Table: BOOKS → click the Column (Foreign) arrow → click Discipline Code → click the Related Table arrow → click Worksheet Table: DISCIPLINE → click the Related Column (Primary) arrow → click Disc Code → OK → Close 5.4.2 : Create a PivotTable from Related Tables ● From within the dataset on the Books sheet, create a PivotTable to add the data to the Data Model. Click the Insert tab → click PivotTable in the Tables group → Click the ‘Add this data to the Data Model’ checkbox to select it → OK

● Display all tables within the PivotTable Fields List and select the Total Book Sales from the BOOKS table. Click All at the top of the PivotTable Fields List → click BOOKS → scroll through the fields, and then click the Total Book Sales check ● Change the Field Settings for the Total Book Sales field in the PivotTable by applying Accounting format with zero decimal places. Close the Value Field Settings dialog box. PivotTable Tools Analyze tab → Active Field group → click field settings → click Number Format → click Accounting in the category list → change the decimal places to zero → click OK → OK ● Add the Discipline field from the DISCIPLINE table in the PivotTable Fields List to the Rows area. Scroll down and Click DISCIPLINE in the PivotTable fields list (right) → then click the Discipline check box to select it ● Add the Editor Last field from the EDITOR table in the PivotTable Fields List to the Rows area. Position the Editor Last field below the Discipline field. In the PivotTAble fields list, scroll down and click EDITOR → click the Editor Last check box to select it 5.4.3 : Create a PivotChart ● Copy the PivotTable sheet and keep the default name PivotTable (2) as the sheet name for the copied sheet. Right click the PivotTAble sheet tab → select Move or Copy → hit the ‘create a copy’ check box ● Create a clustered column PivotChart from the PivotTable (2) sheet. PivotTAble Tools → Tools group → Click PivotChart to open the Insert Chart dialog box → click OK ● Change the chart type to Pie Design tab → Type group → Click Change Chart Type → click Pie → OK 5.4.4 : Modify the PivotChart ● Filter the PivotChart to display only Family discipline data. Click the Discipline arrow within the PivotChart → click the (select all) check

box to deselect all values → click the Family checkbox → OK ● Change the chart title to Family Discipline Book Sales. Click Total in the chart title → type Family Discipline Book Sales → ENTER ● Sort the PivotTable from largest to smallest total book sales. Click cell B5 in the PivotTable → Data tab → Sort & Filter group → click Sort Largest to Smallest in the Sort & Filter group ● Add data labels to the PivotChart. Click the PivotChart → click chart elements on the right of the PivotChart (looks like a plus sign) → click the Data labels check box to select it ● Change the data labels from Values to Percentages and then close the Format Data Labels task pane. Double-click any data label on the piechart to display the Format Data Labels task pane → In the task pane with Label Options displayed, in the Label Contains section→ click to select the percentage check box → click to deselect the value check box → click the close (x) button in the task pane ● Position the PivotChart so that the top-left corner of the chart aligns with the topleft corner of cell C1. Select the PivotChart → press CTRL + X → click cell C1 → press CTRL + V CHAPTER 6 6.1.1 : Create a Range Name ● Click cell B2. Click the Name Box, type PurchasePrice, and press ENTER. Click cell B2 → eassyyy (the name box is the top left that says B2 after you click the cell) ● Create Range names for cells B9, B10, B11, B12, and B13 based on the names located in A9, A10, A11, A12, and A13. Select the range A9:B13 → click the Formulas tab → defined names group → select Create from Selection → ensure the Left Column is checked → OK 6.1.2 : Edit and Delete Range Names ● Use the name manager to delete the Monthly_Payment named range

Formulas tab → Defined Names group → click Name Manager → In the Name Manager dialog box → click the Monthly_Payment named range → click Delete → OK → Close ● Edit the named range DownPayment to Down_Payment. Formulas tab → Defined Names group → click Name manager → double click DownPayment → In the Edit Name dialog box, in the Name box, change the name to Down_Payment → OK → close ● Edit the named range PurchasePrice to Purchase_Price. Formulas tab → Defined Names group → click Name Manager → double click PurchasePrice → change the name to Purchase_Price → OK → Close 6.1.3 : Use a Range Name in a Formula ● Replace the cell references in the PMT function located in cell B12 with the appropriate named ranges. Click cell B12 → Click Insert Function on the Formula bar (the button that looks like ‘fx’ on top of all the cells ) → With the value in the rate box selected, click cell B10 → press TAB → click cell B11 → press TAB → type ‘-’ → click cell B9 → OK

6.1.4 : Insert a List of Range Names ● Create a new worksheet named Range Names. To the right of the Home Loan Worksheet tab, click New Sheet → double click the inserted sheet tab ‘sheet 1’ → type Range Names → Enter ● Insert a list of range names used in formulas in the workbook starting in cell A2. Click cell A2 → Formulas tab → Defined Names group → Use in Formula → select Paste Names → click Paste List ● AutoFit the widths of columns A and B. Select columns A:B (starting with the letter A and B on top) → click home tab → Cells group → click Format → select AutoFit column width 6.2.1 : ● Beginning in cell D4 on the Home Loan tab, complete the series of substitution

values ranging from 4% to 6% in increments of 0.25% vertically down column D. On the Home Loan worksheet → click cell D4 → Home tab → Editing group → click Fill and then click Series → Under series in, click Columns → select the text in the Step value box and type 0.25% → click in the Stop value box and type 6% → OK 6.2.2 : Add Formulas to a One variable Data table ● Insert a reference to the original monthly payment in cell E3. Click cell E3 → type ‘=B12’ → press CTRL + ENTER → ● Insert a reference to the Total to repay loan in cell F3. Select cell F3 → type ‘=B13’ → CTRL + ENTER 6.2.3 : Calculate results for a one-variable data table ● Use the reference and the substitution values created to complete a singlevariable data table in the range D3:G12. Select the range D3:G12 → click data tab → forecast group → what-if analysis → click Data Table → click in the column input cell box → click cell B4 → OK 6.2.4 : Format a one-variable Data Table ● Apply the Accounting Number Format to the range E4:G12. Select the range E4:G12 → home tab → number group → Accounting Number Format → ● Create a custom number format for cell E3 to display the word Payment. Click cell E3 → home tab → number group → click the Number format dialog box launcher → on the number tab, under Category, click Custom → on the type list, scroll up and select General → in the type box, select the word General and type ‘ “Payment” ‘ → OK 6.2.5 : Set up two-variable substitution values and add a formula to the data table ● Apply the Accounting Number Format to the range J3:L3. Select the range J3:L3 → Home tab → Number group → click Accounting Number Format ● Beginning in cell I4 on the Home Loan tab, complete the series of substitution

values ranging from 4% to 8% in increments of .25% vertically down column I. Click cell I4 → Home tab → editing group → Fill → click Series → In the series dialog box, click Columns → click in the step value box, type .25% → click in the stop value box, type 8% → OK ● select the range I4:I20, and then Increase the number of decimal places to two. Select the range I4:I20 → Home tab → Number group → click ‘Increase Decimal’ two times ● Insert a reference to the original monthly payment in cell I3. Click cell I3 → type ‘=B12’ → press CTRL + ENTER ● Simultaneously autofit the width of columns I:K. In the column heading area, click and drag to select columns I:K → Position the pointer on the right border of column I, and then double-click 6.2.6 : Calculate the Results of a Two-variable data table ● Complete the two variable data table using the range I3:L20 and reference cells B2 and B4. Select the range I3:L20 → click the Data tab → Forecast group → click What-if analysis → click DAta Table → with the Row input cell box selected, click cell B2 → click in the column input cell box, click cell B4 → OK ● Apply a custom number format to cell I3 to display APR. Click cell I3 → click Home tab → Number group → Number format Dialog box launcher → In the number tab, under Category, click Custom → Scroll up through the Type list and select General → select the word General in the type box and type ‘ “APR” ‘(include “”) → OK 6.3.1 : Determine Optimal Input values Using Goal seek ● In cell B12, use Goal Seek to determine how much you can spend on a home (B2) to maintain a 600 dollar monthly payment. Accept the solution. Click cell B12 → click the Data tab → in the forecast group → What-if Analysis → click Goal Seek → with B12 in the set cell box, click To value box and type 600 → click in the By changing cell box and type B2→ OK → OK 6.3.2 : Create a Scenario and Add Scenarios

● Create a What-If scenario named Best-Case Scenario. The scenario should change the range B2:B5. Assign 200000 to cell B2, 10000 to cell B3, 0.0225 to cell B4, and 15 to cell B5. Do not close Scenario Manager after completing this step. Data tab → Forecast group → what-if analysis → click Scenario Manager → click ADD → in the scenario name box, type Best-Case Scenario → select the text in the changing cells box, and then select the range B2:B5 with the text selected → OK → Type 200000 in the $B$2 box → TAB → type 10000 in the $B$3 → TAB → type 0.0225 in the $B$4 box → TAB → type 15 in $B$5 → OK 6.3.3 : View and Edit Scenarios ● Display the Most-Likely scenario. Edit the Most-Likely scenario by changing the third Changing Cell ($B$4) to the 0.0425 and display the scenario again. Data tab → forecast group → what-if analysis → click Scenario Manager → click most likely scenario → click show → click edit → OK → select the value in the third changing cell ($B$4) and type 0.0425 → OK → with the most likely scenario selected, click Show 6.3.4 : Generate A Summary Report ● Create a scenario summary report for the cell range B12:B14 to compare the three home loan scenarios In the scenario manager dialog box, click Summary → select the range b12:B14 to enter it into the Results cells box → OK ● Format the scenario summary report by deleting column A. Right-click the column A header, and from the shortcut menu, click Delete → ● In the summary report, delete row 1 [the remaining changes will autocomplete]. Click the Row 1 header → right click and from the shortcut menu → click Delete 6.4.1 : Load the Solver Add-in ● Load the Solver add-in. File tab → options → click Add ins → with Excel Add ins in the Manage box → click Go → In the Add-ins available list, click the Solver Add-in check box → OK

6.4.2 : Identify the Objective cell and Changing Cells ● Set Solver to solve the objective cell of B12 on the Home Loan worksheet to the value of 1000. Define the variable cell range as B2:B5. Leave the Solver dialog box open, as you will define the constraints in the next step. Data tab → analyze group → click Solver → with the set objective box selected, click cell B12 → click the ‘Value of’ option button → select the existing value, type 1000 in the Value of Box → click in the By changing Variable Cells Box, then click and drag to select the range B2:B5 6.4.3 : Define Constraints ● Create a constraint limiting cell B2 to less than or equal to 300000. Click add, then create a second constraint to ensure that cell B2 is greater than or equal to 100000. IN the Add constraint dialog box, click Add again In the solver parameter dialog box, click Add → in the Add Constraint dialog box, with the insertion point in the cell Reference box, click cell B2→ click in the constraint box, type 300000 → ADD → with the insertion point in the cell reference box, click cell B2 → click the operator down arrow and then click >= → click in the constraint box, type 100000 ● Set Solver to solve the objective cell of B12 on the Home Loan worksheet to the value of 1000. Define the variable cell range as B2:B5. Leave the Solver dialog box open, as you will define the constraints in the next step. Same as above steps 6.4.4 : Create a Solver Report ● Set Solver to solve the objective cell of B12 on the Home Loan worksheet to the value of 1000. Define the variable cell range as B2:B5. Leav...