dw ab it
In cell G3, on the e09AnnualSales workbook, create a formula to link to cell F3 in the open e09OnlineSales workbook. Change the reference to cell F3 to a relative cell reference, and then use the fill handle to copy the formula down through cell G10
With the e09AnnualSales workbook displayed, click cell G3, and then type = (equal sign). Point to the Excel icon on the task bar, and then click the e09OnlineSales thumbnail. With the Summary worksheet displayed, click cell F3, and then press CTRL+ENTER. With cell G3 selected in the e09AnnualSales workbook, click in the Formula Bar and position the insertion point at the end of the cell reference. Press F4 three times, and then press CTRL+ENTER. Drag the fill handle in cell G3 down to cell G10. Release the mouse button.
Sort the data first by category, and then by course title, both in alphabetical order.
1. Click the Data tab 2. In the sort and filter group, click Sort. 3. In the Sort dialog box, click the sort by arrow, and then click category. 4. Click ADD LEVEL 5.Click the Then by arrow, and select Course Title. 6. Click OK
Apply Accounting Number Format to the range C15:H17.
Click and drag to select cells C15:H17. Click the Home tab. In the Number group, click Accounting Number Format.
Replace the cell references in the function located in cell B10 with the named ranges created in the prior step
Click cell B10. Select the reference to cell B4 and click cell B4. Select the reference to cell B5 and click cell B5. Select the reference to cell B6 and click cell B6 Press ENTER (or CTRL+ENTER or TAB or click the Enter button on the Formula bar).
1) Group the Qtr1 through Qtr4 worksheets. Use AutoSum to simultaneously total the values of the data in the range B3:E11. Simultaneously format the ranges B3:E3 and B11:E11 using the Accounting Number Format.
Click the Qtr1 sheet tab, hold SHIFT, and then click the Qtr4 sheet tab. Select the range B3:E11 and on the Home tab, in the Editing group, click Sum. Select the range B3:E3, hold CTRL, and then select the range B11:E11. On the Home tab, in the Number group, click Accounting Number Format.
1) Display the Qtr2 sheet.
Click the Qtr2 worksheet tab.
1) Create a scenario summary report using the range B9:B11 as the results.
In the Scenario Manager dialog box, click Summary. Select the range B9:B11 to enter it into the Results cells box. Click OK
1) Complete the Solver model, keep the Solver Solution, and generate an Answer Report.
In the Solver Parameters dialog box, click Solve. In the Solver Results dialog box, click Answer in the Reports list. Click OK.
1) Create a relationship between the COURSES table using the Category ID field and the CATEGORIES table using the CatID field.
On the Data tab, in the Data Tools group, click Relationships. In the Manage Relationships dialog box, click New. In the Create Relationship dialog box, click the Table arrow, and then click Worksheet Table: COURSES. Click the Column (Foreign) arrow, and then click Category ID. Click the Related Table arrow, and then click Worksheet Table: CATEGORIES. Click the Related Column (Primary) arrow, and then click CatID. Click OK. Close the Manage Relationships dialog box, if necessary.
Use the macro recorder to create a macro that simultaneously deletes the contents of cells D2, D4:D8, B11:D20. Name the macro ClearInvoice, and add the following text in the description box: This macro clears the values in the existing template. (include the period). Stop recording the macro.
On the Developer tab, in the Code group, click Record Macro. In the Record Macro dialog box, with the default Macro name selected, type ClearInvoice. Click in the Description box, and then type This macro clears the values in the existing template. (include the period). Click OK. Select cell D2, hold CTRL, select the range D4:D8, hold CTRL, select the range B11:D20, and then press DELETE. On the Developer tab, in the Code group, click Stop Recording.
1) Trace Dependents for cell C3 on the Summary worksheet.
On the Summary worksheet, click cell C3, if necessary. On the Formulas tab, in the Formula Auditing group, click Trace Dependents.
Click cell D2, click the Review tab, and click Delete in the Comments group.
Select cell D2, if necessary. On the Review tab, in the Comments group, click Delete.
Create Range names for cells B4, B5, B6, and B7 based on the names located in A4, A5, A6, and A7.
Select the range A4:B7 and click the Formulas tab. In the Defined Names group, select Create from Selection. Ensure Left column is checked and click OK.
Create a one-variable data table for the range B14:H17 using the unit output from column B as the Row input cell.
Select the range B14:H17. On the Data tab, in the Forecast group, click What-If Analysis, and then click Data Table. Click in the Row input cell box, and then click cell B6. Click OK.
Move the Instructor field to the Filter area, and then filter the PivotTable to display the totals for instructors Belka and Turner
1. In the PivotTable Fields List, drag the Instructor field from the Choose fields to add to report to the filters area. 2. Click the Instructor filter arrow in cell B1, then clock the Select Multiple Items check boc, if necessary. 3. Click the (All) check boc to deselect it. 4. Click the Belka and Turner check box(es). 5. OK
Perform a goal seek analysis to determine what the sales price in column B needs to be if you wanted the operating income in column B to be $3,500. Accept the solution.
Click cell B11 then click the Data tab. On the Data tab, in the Forecast group, click What-If Analysis and click Goal Seek. With B11 in the Set cell box, click in the To value box and type 3,500. Click in the By changing cell box and type B7. Click OK. In the Goal Seek Status box, click OK.
1) With the Summary worksheet active, insert a hyperlink in cell B2 to the range E2:E11 in the Qtr1 worksheet.
Click cell B2, and then click the Insert tab. In the Links group, click Link. In the Insert Hyperlink dialog box, in the Link to box, click Place in This Document. In the Type the cell reference box, select the existing text, and then type E2:E11. In the Or select a place in this document box, click 'Qtr1'. Click OK.
1) In cell B3, insert a relative reference to cell E3 on the Qtr1 worksheet. Use the fill handle to copy the formula through cell B10.
Click cell B3. Type = (equal sign), and then click the Qtr1 sheet tab. Click cell E3, and then press CTRL+ENTER. Click and drag the fill handle in cell B3 down to cell B10. Release the mouse button.
1) Type <4/1/2021 in cell B56.
Click cell B56. Type <4/1/2021. Press ENTER.
1) Type John Doe in cell D4.
Click cell D4, if necessary. Type John Doe. Press ENTER.
Search for the Invoice with tax calculation template in the template gallery. Click the template thumbnail with the same name and click Create.
Click in the Search for online templates box, type Invoice with tax calculation, and then press ENTER. Click the second template in the results. In the Invoice with tax calculation dialog box, click Create.
Remove the Course Title field fro the PivotTable
Click the Course Title check box to deselect it.
Click the Data tab and click From Text/CSV in the Get & Transform Data group. Browse to the location of the file e10h1BrokerInfo.txt, select the file, and click Import. Select Tab as the delimiter, review the data for accuracy, and then click Load.
Click the Data tab. In the Get & Transform Data group, click Get Data, point to From File, and then click From Text/CSV. Click the file e10h1BrokerInfo.txt, and then click Import. Click the Delimiter arrow, and then click Tab. Click Load.
1) Load the Solver add-in.
Click the File tab. Click Options. Click Add-Ins. With Excel Add-ins selected in the Manage box, click Go. In the Add-ins available list, click the Solver Add-in check box. Click OK.
Save the file as an Excel Template using the name e12h1RenovationInvoice_LastFirst.
Click the File tab. Click Save As. On the left, click Browse, and then with This PC selected, click Documents, if necessary. In the Save As dialog box, click the Save as type arrow. Click Excel Template. Click in the File name box. Type e12h1RenovationInvoice_LastFirst, if necessary. Click Save.
1) Mark the workbook as final.
Click the File tab. In Backstage view, click the Protect Workbook button, and then click Mark as Final. In the message box, click OK. In the second message box, click OK
In the e12h1RenovationInvoice_LastFirst file, run the Document Inspector.
Click the File tab. On the Info tab, click Check for Issues, and then click Inspect Document. In the Document Inspector dialog box, click Inspect.
Click the Insert tab, click the My Add-ins arrow in the Add-ins group, select Manage Other Add-ins. Select COM Add-ins from the Manage box, and click Go. Select Microsoft Power Map for Excel and click OK.
Click the Insert tab. In the Add-ins group, click the My Add-Ins arrow, and then click Manage Other Add-ins. In the Excel Options dialog box, click the Manage arrow, and then click COM Add-ins. Click Go. Click Microsoft Power Map for Excel. Click OK.
Click the Insert tab, click the My Add-ins arrow in the Add-ins group, select Manage Other Add-ins. Select COM Add-ins from the Manage box, and click Go. Select Microsoft Power Pivot for Excel and click OK.
Click the Insert tab. In the Add-ins group, click the My Add-Ins arrow, and then click Manage Other Add-ins. In the Excel Options dialog box, click the Manage arrow, and then click COM Add-ins. Click Go. Click Microsoft Power Pivot for Excel. Click OK.
Copy the PivotTable sheet, positioned to the left of PivotTable. The copied sheet should be PivotTable (2).
Click the PivotTable sheet tab, if necessary. On the Home tab, in the Cells group, click Format, and then click Move or Copy Sheet. In the Move or Copy dialog box, click the Create a copy check box. Click OK.
Display the Power Pivot window. On the Home tab in the Power Pivot window, click PivotTable. Click Existing Worksheet, click the Collapse icon, and click the worksheet named Sheet1. Click cell A1, click OK, and click OK again.
Click the Power Pivot tab. In the Data Model group, click Manage. On the Home tab, in the PivotTable group, click PivotTable. Click the Existing Worksheet option. Click the Collapse icon, click the worksheet named Sheet1. Click cell A1 in the Sheet1 sheet. Click OK. Click OK.
Create a calculated field named AUTHOR ROYALTIES that multiplies the Total Book Sales field by .1
1. Click C5 2. click the pivottable tools analyze tab 3. In the Calculations group, click fields, items, sets and then calculated field. 4. with the Name box selected, type AUTHOR ROYALTIES. 5.In the list, scroll down 6. click total book sales 7. click insert field 8. type .1 in the formula box *.1 9. click add and OK
Insert a Slicer for the Instructor field. use the slicer to deselct the instructor Belka to limit the results to the totals for Turner.
1. On the Pivot Table Tools Analyze tab, in the Filter group, click Insert Slicer. 2. In the Insert Slicers dialog box, click the Instructor check box. 3. OK 4. IN the slicer hold CTRL, then click Belka
Add the Instructor field to the column area
Drag the Instructor field
Create a relationship between the Transactions table using the Broker_ID field and the e10BrokerInfo table using the Broker_ID field. Close the Manage Relationships dialog box.
On the Data tab, in the Data Tools group, click Relationships. In the Manage Relationships dialog box, click New. In the Create Relationship dialog box, click the Table arrow, and then click Data Model Table: Transactions. Click the Column (Foreign) arrow, and then click Broker_ID. Click the Related Table arrow, and then click Data Model Table: e10BrokerInfo. Click the Related Column (Primary) arrow, and then click Broker_ID. Click OK. Close the Manage Relationships dialog box.
Create a scenario named Best-Case Scenario using the range B4:B7 as the changing cells. Enter these values for the scenario: 15000, 0, 1000, and 45, respectively. Do not close Scenario Manager after completing this step.
On the Data tab, in the Forecast group, click What-If Analysis, then click Scenario Manager. In the Scenario Manager dialog box, click Add. In the Scenario name box, type Best-Case Scenario. Select the text in the Changing cells box and select the range B4:B7. Click OK. Type 15000 in the Fixed_Cost box. Press TAB. Type 0 in the Variable_Cost_per_unit box. Press TAB. Type 1000 in the Unit_Output box. Press TAB. Type 45 in the Sales_Price box. Click OK.
Click the Data tab and click Get Data. Click From Database, select From Microsoft Access Database, and browse to the file e10Transactions.accdb. Click Import. Select the Transactions table in the Navigation Pane and click Load
On the Data tab, in the Get & Transform Data group, click Get Data, point to From Database, and then click From Microsoft Access Database. In the Import Data dialog box, click the e10Transactions.accdb file, and then click Import. In the Navigator dialog box, click Transactions. Click Load.
1) Group the Qtr1, Qtr2, Qtr3, Qtr4 and Summary sheets.
Right-click any worksheet tab, and from the shortcut menu, click Select All Sheets.
1) In the Brokers worksheet, insert the TEXTJOIN function in cell E2 to use a space as the delimiter to join the text in the range B2:D2. Set the Ignore_empty argument to TRUE.
Select cell E2. On the Formulas tab, in the Function Library group, click Text, scroll down and then click TEXTJOIN. In the Delimiter box type " ". In the Ignore_empty box, type TRUE. In the Text1 box, type B2:D2 (one value in each text box in that order). Click OK.
Convert the data in the range G2:G6 using the Delimited data type. Select Comma as the delimiter character, deselect other delimiter characters, and then click Finish.
Select the range G2:G6. On the Data tab, in the Data Tools group, click Text to Columns. In the Convert Text to Columns Wizard, click Delimited and click Next. Click to deselect the Tab check box. Click the Comma check box to select it. Click Finish.
