AACS3763 Managing Information Systems

Practical 1
PRACTICAL 1:
At the end of this practical student should be able to:
 Using advanced functions
 Analysing table data
 Performing What-if Analysis and Using Scenario

Using advanced functions

 Steps:
Open Advanced Functions.xlsx file. Display the Insurance worksheet. Select rates
from the Name box to view the lookup table.

Use the VLOOKUP function to lookup the Age in the rates table and retrieve the Rate
% from the second column.

1. Select the cell in which you want the result of the Click cell F6
VLOOKUP function to appear.

2. Type =VLOOKUP and an open parenthesis ( ( ). Type =VLOOKUP(

=VLOOKUP( appears in the cell and on the formula bar.

As you start typing a function, a Screen Tip is displayed to
help you enter valid arguments.
3. Select the cell containing the value you want to look up Click cell B6
and a comma.

A dash marquee appears around the cell and its address

appears in the VLOOKUP function.
4. Type the name or address of the lookup table and a Type rates,
comma.

The text appears in the VLOOKUP function.

5. Enter the column index number and if necessary type a Type 2
comma and indicate the range lookup as FALSE for exact
match.

The column index number appears in the VLOOKUP

function.
6. Type the closing parenthesis ( ) ). Type )

The closing parenthesis ( ) ) appears in the VLOOKUP

function.
7. Press [ENTER]. Press [ENTER]

Copy the formula to the range F7:F15. Then, click anywhere in the worksheet to

1
AACS3763 Managing Information Systems

Practical 1
deselect the range.
1.2 Using the HLOOKUP Function

 Steps:

Open Advanced Functions.xlsx file. Display the Insurance worksheet.

Select rates from the Name box to view the lookup table. Use the HLOOKUP function to
lookup the Category in the charges table and retrieve the Loading Charge from the third
row.

1. Select the cell in which you want the result of the Click cell G6
HLOOKUP function to appear.

The cell is selected.

2. Type =HLOOKUP and an open parenthesis ( ( ). Type =HLOOKUP(

=HLOOKUP( appears in the cell and on the formula bar.

As you start typing a function, a Screen Tip is displayed to
help you enter valid arguments.
3. Select the cell containing the value you want to look up Click cell C6
and a comma.

A dash marquee appears around the cell and its address

appears in the HLOOKUP function.
4. Type the name or address of the lookup table and type a Type charges,
comma.

The text appears in the HLOOKUP function.

5. Enter the row index number and if necessary type a Type 3, FALSE
comma and indicate the range lookup as FALSE for exact
match.

The column index number appears in the HLOOKUP

function.
6. Type the closing parenthesis ( ) ). Type )

The closing parenthesis ( ) ) appears in the HLOOKUP

function.
7. Press [ENTER]. Press [ENTER]

The result of the HLOOKUP function appears in the cell.

Copy the formula to the range G7:G15. Then, click anywhere in the worksheet to
deselect the range.

2
AACS3763 Managing Information Systems

Practical 1

1.3 Using the IF Function

 Steps:

Open Advanced Functions.xlsx file. Display the Reduction worksheet.

Use the IF function to determine the Discount Reduction amount based on the Insurance
Claims status. If the status is No, then calculate 35% of the Loading Charge, else display
0.

1. Select the cell in which you want the result of the IF Click cell H6
function to appear.

The cell is selected.

2. Type =IF and an open parenthesis ( ( ). Type =IF(

=IF( appears in the cell and on the formula bar. As you

start typing a function, a Screen Tip is displayed to help
you enter valid arguments.
3. Type the logical test and a comma. Type D6=”No”,

The text appears in the cell and on the formula bar.

4. Type the action to be taken if the logical test is true and Type G6*35%,
type a comma.

The text appears in the cell and on the formula bar.

5. Type the action to be taken if the logical test is false. Type 0

The text appears in the cell and on the formula bar.

6. Type the closing parenthesis ( ) ). Type )

The closing parenthesis ( ) ) appears in the IF function.

7. Press [ENTER]. Press [ENTER]

Copy the formula to the range H7:H15. Then, click anywhere in the worksheet to
deselect the range.

3
AACS3763 Managing Information Systems

Practical 1
1.4 Using Date Function

 Some common date related functions:

 Steps:

Open Advanced Functions.xlsx file. Display the Invoice worksheet.

Insert a function that updates automatically to display the current date.

1. Select the cell in which you want the result of the IF Click cell B1
function to appear.

The cell is selected.

2. Type =TODAY() Type =TODAY()

=TODAY() appears in the cell and on the formula bar.

3. Press [ENTER]. Press [ENTER]

The result of the IF function appears in the cell.

 Practice
1. Select cell F4.
2. Enter the function =MONTH(B4) to extract the month number from the Inv Date.
3. Copy the function to the cell range F5:F18.
4. Select cell G4.
5. Enter the function =YEAR(B4) to extract the year number from the Inv Date.
6. Copy the function to the cell range G5:G18.

4
AACS3763 Managing Information Systems

Practical 1
1.5 Using the COUNTIF Function

 Steps:

Open Advanced Functions.xlsx file and display the Invoice worksheet.

Insert a function to count the number of invoices with amount of 10000 and above.

1. Select the cell in which you want the result of the Click cell E20
COUNTIF function to appear.

The cell is selected.

2. Type =COUNTIF and an open parenthesis ( ( ). Type =COUNTIF(

=COUNTIF( appears in the cell and on the formula bar

and a Screen Tip appears.
3. Select the range of cells you want to count and type a Select D4:D18,
comma.

The cell range is selected

4. Type the number, text or expression that defines the data Type “>=10000”
you want to count.

The criteria appears in the cell and on the formula bar.

5. Type the closing parenthesis ( ) ). Type )

The closing parenthesis ( ) ) appears in the cell and on the

formula bar.
6. Press [ENTER]. Press [ENTER]

The result of the COUNTIFfunction appears in the cell.

 Practice

1. Select cell E22.

2. Enter the function =COUNTIF(E4:E18,””) to count the number of unpaidinvoices
(blank cells).

5
AACS3763 Managing Information Systems

Practical 1

1.6 Using SUMIF Function

 Steps:

Open Advanced Functions.xlsx file and display the Invoice worksheet.

Insert a function to sum the total amount for Sail City.

1. Select the cell in which you want the result of the Click cell E24
SUMIF function to appear.

The cell is selected.

2. Type =SUMIF and an open parenthesis ( ( ). Type =SUMIF(

=SUMIF( appears in the cell and on the formula bar, and

a Screen Tip appears.
3. Select the range of cells you want evaluated by criteria Select C4:C18,
and type a comma.

The cell range is selected

4. Type the number, text or expression that defines the data Type “Sail City”,
you want to sum and a comma.

The criteria appears in the cell and on the formula bar.

5. Select the range of cells you want to add. Select D4:D18

The cell range is selected.

6. Type the closing parenthesis ( ) ). Type )

The closing parenthesis ( ) ) appears in the cell and on the

formula bar.
7. Press [ENTER]. Press [ENTER]

The result of the SUMIF function appears in the cell.

6
AACS3763 Managing Information Systems

Practical 1
1.7 Using RANK Function

 Steps:

Open Advanced Functions.xlsx file and display the Ranking worksheet.

Insert a function to rank each page against the named range visitors (B5:B11).

1. Select the cell in which you want the result of the RANK Click cell C5
function to appear.

The cell is selected.

2. Type =RANK and an open parenthesis ( ( ). Type =RANK(

=RANK( appears in the cell and on the formula bar and a

Screen Tip appears.
3. Select the range of cells you want to rank and type a Select B5,
comma

The cell range is selected

4. Select the range of cells or enter the range name to rank Type visitors
against.

The cell range appears in the cell and on the formula bar.
5. Type the closing parenthesis ( ) ). Type )

The closing parenthesis ( ) ) appears in the cell and on the

formula bar.
6. Press [ENTER]. Press [ENTER]

The result of the RANK function appears in the cell.

7
AACS3763 Managing Information Systems

Practical 1

Practical Exercise:
Open Football.xlsx file and display the Teams worksheet.

1. Enter a function in B4 to remove extra spaces from the player’s name in A4.
2. Copy the function to the cell range B5:B15.
3. In cell E4, assign a team to each player based on the Total Points column using the
lookup table in the cell range I5:K9.
4. Copy the function to the cell range E5:E15.
5. Enter a function in F4 to rank each player based on their Total Points against the named
range points in descending order.
6. Copy the function to the cell range F5:F15.
7. Enter a function in G4 to extract the year number from the Birth Date in C4.
8. Copy the function to the cell range G5:G15.
9. Enter a function in C17 to count the number of players born in the year 1992.

8
AACS3763 Managing Information Systems

Practical 1
Analysing table data
2.1 Filter Table

 Steps:

Open Projects.xlsx file and display the 2017 Projects worksheet.

1. Click the Project list arrow.

2. In the list of projects for the Project field, click Select All to clear the checks marks
from the projects, scroll down the list of projects, click Cheetah, and then click OK.

3. Move the pointer over the Project list arrow.

4. Click the Project list arrow, then click Clear Filter From “Project”.

5. Click the Places Reserved list arrow, point to Number Filters, click Top 10, select
10 in the middle box, type 5, click the Items list arrow, click Percent, and then
click Ok.

6. On the Home Tab, click the Sort & Filter button in the Editing group, and then
click Clear.

7. Click the Depart Date list arrow, point to Date Filters, point to All Dates in the
Period, and then click March.

8. Click the Sort & Filter button in the Editing group, click Clear.

Save the workbook.

9
AACS3763 Managing Information Systems

Practical 1
2.2 Create a Custom Filter

 Steps:

Open Projects.xlsx file and display the 2017 Projects worksheet.

1. Click the Depart Date list arrow, point to Date Filters, and then click Custom
Filter.

2. Click the left text box list arrow on the first line, click is before, then type 3/1/2017
in the right text box on the first line.

3. Click the Or option button to select it, click the left text box list arrow on the
second line, select is after, and then type 12/1/2017 in the right text box on the
second line.

4. Click Ok.

5. Click the Depart Date list arrow, click Clear Filter From “Depart Date”, click
the Depart Date list arrow, point to Date Filter, and then click Custom Filter.

6. Click the left text box list arrow on the first line, click is after, then type 2/15/2017
in the right text box on the first line.

7. Click the left text box list arrow on the second line, click is before, then type
4/15/2017 in the right text box on the second line, and then click Ok.

8. Click the Depart Date list arrow, click Clear Filter From “Depart Date”, and
then add your name to the center section of the footer.

10
AACS3763 Managing Information Systems

Practical 1
2.3 Filter Table with the Advanced Filter

 Steps:

Open Projects.xlsx file and display the 2017 Projects worksheet.

1. Select table rows 1 through 6, then click the Insert list arrow in the Cells group.

2. Click Insert Sheet Rows; click cell A1, type Criteria Range, and then click the
Enter button on the formula bar.

3. Select the range A7:H7, click the Copy button in the Clipboard group, click cell
A2, click the Paste button in the Clipboard group, and then Press [Esc].

4. Click cell B3, type >6/1/2017, click cell F3, type <2000, and then click .

5. Click any cell in the table, click the Data tab, then click the Advanced button in the
Sort & Filter group.

6. Click the Criteria range text box, select the range A2:H3 in the worksheet, then
click Ok.

2.4 Extract Table Data

 Steps:

Open Projects.xlsx file and display the 2017 Projects worksheet.

1. In cell A3 enter African Wild Dog, and then in cell A4 enter Great White Shark.

2. Copy the criteria in cells B3:F3 to B4:F4.

3. If necessary, click the Data tab, and then click Advanced in the Sort & Filter group.

4. Under Action, click the Copy to another location option button to select it, click
the Copy to text box, and then type A75.

5. Edit the contents of the Criteria range text box to show the range $A$2:$H$4,
click Ok, then if necessary scroll down until row 75 is visible.

6. Press [Ctrl][Home], and then click the Clear button in the Sort & Filter group.

Save the workbook.

11
AACS3763 Managing Information Systems

Practical 1

2.5 Look up values in a Table

 Steps:

Open Projects.xlsx file and display the Lookup worksheet.

1. Click the Formulas tab in the Ribbon, and then click the Name Manager Button in
the Defined Names Group.

2. Click Close.

3. Click cell M1, enter 754Q, click cell M2, click the Lookup & Reference button in
the Function Library group, and then click VLOOKUP.

4. With the insertion point in the Lookup_ value text box, click cell M1, click the
Table_array text box, and then type Table2.

5. Click the Col_index_num text box, type 2, click the Range_lookup text box, and
then enter FALSE. Click Ok.

6. Click cell M1, type 335P, and then click the Enter button on the formula bar.

6. Press [Ctrl][Home], and then Save the workbook.

2.6 Summarize Table Data

 Steps:

Open Projects.xlsx file and display the Lookup worksheet.

1. Review the criteria range for the Rhino project in the range L5:L6.

2. Click cell M8, click the Insert Function button in the Function Library group, in the
Search for a function text box type database, click Go, scroll to the click DSUM
under Select a function, and then click Ok.

3. In the Function Arguments dialog box, with the insertion point in the Database text
box, move the pointer over the upper-left corner of cell A1 until the pointer changes
to , click once, and then click again.

4. Click the Field text box, then click cell G1, Places Available; click the Criteria text
box and select the range L5:L6. Click Ok.

12
AACS3763 Managing Information Systems

Practical 1

5. Click cell M9, click the Insert Function button on the formula bar, in the
Search for a function text box type database, click Go, and then double-click
DCOUNTA in the Select a function list.

6. With the insertion point in the Database text box, move the pointer over the upper-
left corner of cell A1 until the pointer changes to , click once, and then click again
to include the header row, click the Field text box, click cell B1, click the Criteria
text box and select the range L5:L6, and then click Ok.

6. Click cell L6, type Dolphin, and then click the Enter button on the formula bar.

 Common database functions:

Function Result
DGET Exacts a single record from a table that matches criteria you specify.
DSUM Totals numbers in a given table column that match criteria you specify.
DAVERAGE Average numbers in a given table column that match criteria you specify.
DCOUNT Counts the cells that contains numbers in a given table column that match
criteria you specify.
DCOUNTA Counts the cells that contains nonblank data in a given table column that
match criteria you specify.

13
AACS3763 Managing Information Systems

Practical 1

Practical Exercise:
Open Human Resources.xlsx file.
1. Filter a table
a. Click the Compensation worksheet, filter the table to list only records for employees
in the Dallas branch.
b. Clear the filter, then add a filter that displays the record for employees in the Dallas
and LA branches.
c. Redisplay all employees, then use a filter to show the three employees with the
highest annual salary.
d. Redisplay all the records.

2. Create a custom filter

a. Create a custom filter showing employees hired before 1/1/2015 or after 12/31/2016.
b. Create a custom filter showing employees hired between 1/1/2014 and 12/31/2015.
c. Enter your name in the worksheet footer, then preview the filtered worksheet.
d. Redisplay all records.
e. Save the workbook.

3. Filter and extract a table with the advanced filter.

a. Retrieve a list of employees who were hired before 1/1/2017 and who have an annual
salary of more than $75,000 a year. Define a criteria range by inserting six new rows
above the table on the worksheet and copying the field names into the first row.
b. In cell D2, enter the criterion <1/1/2017, then in cell G2 enter >75000.
c. Click any cell in the table.
d. Open the Advanced Filter dialog box.
e. Indicate that you want to copy to another location, enter the criteria range A1:J2,
verify that the list range is $A$7:$J$17, then indicate that you want to place the
extracted list in the range starting at cell A20.
f. Confirm that the retrieved list meets the criteria.
g. Save the workbook, then preview the worksheet.

4. Look up values in a table

a. Click the Summary worksheet. Use the Name Manager to view the table names in the
workbook, then close the dialog box.
b. Prepare to use a lookup function to locate an employee’s annual compensation; enter
the Employee Number 1578 in cell A18.
c. In cell B18, use the VLOOKUP function and enter A18 as the Look_value, Table2 as
the Table_array, 10 as the Col_index_num, and FALSE as the Range_lookup;
observe the compensation displayed for that employee number, then click it against
the table to make sure it is correct.
d. Replace the escisting Employee Number in call A18 with 1998, and view the annual
compensation for that employee.
e. Format cell B18 with Accounting format with the $symbol and no decimal places.
f. Save the workbook.

14
AACS3763 Managing Information Systems

Practical 1

5. Summarize table data.

a. Click the Summary worksheet.
b. Prepare to enter a database function to average the annual salaries by branch, using
the LA branch as the initial criterion. In cell E18, use the DAVERAGE function, and
click the upper-left corner of cell AL twice to select the table and its header row as the
Database, select cell G1 for the Field, and select range D17:D18 for Criteria. Verify
that the average LA salary is 56400.
c. Test the function further by entering the text Dallas in cell D18. When the criterion is
entered, cell E18 should display 75000.
d. Format cell E18 in Accounting format with the $ symbol and no decimal places.
g.

15
AACS3763 Managing Information Systems

Practical 1
Performing What-if Analysis and Using Scenario
3.1 Track What-if analysis with scenario manger

 Steps:

Open Sales.xlsx file and display the Projected Sales worksheet.

1. Select range C3:E3, click the Data tab, click the What-If Analysis button in the
Forecast group, then click Scenario Manager.

2. Click Add, drag the Add Scenario dialog box to the right if necessary until columns
A and B are visible, then type Original Sales Figures in the Scenario name text box.

3. Click Ok to confirm the scenario range. Click Ok.

4. Click Add; in the Scenario name text box type Increase Feb, Mar, Apr, by 5000;
verify that the Changing cells text box reads C3:E3, then click Ok; in the Scenario
Values dialog box, change the value in the $C$3 text box to 80189, change the value
in the $D$3 text box to 76423, change the value in the $E$3 text box to 89664, then
click Add.

5 In the Scenario name text box type Increase Feb, Mar, Apr, by 10000 and click
Ok; in the Scenario Values dialog box, change the value in the $C$3 text box to
85189, change the value in the $D$3 text box to 81423, change the value in the $E$3
text box to 94664, then click Ok.

6. Make sure the Increase Feb, Mar, Apr, by 10000 scenario is still selected, click
Show, notice that the percentage the New York sales in cell I3 changes from 31.38%
to 32.91%; click Increase Feb, Mar, Apr, by 5000, click Show, notice that the New
York sales percentage is now 32.30%; Click Original Sales Figures, click Show to
return to the original values, then click Close.

Save the workbook.

16
AACS3763 Managing Information Systems

Practical 1
3.2 Generate a Scenario Summary

 Steps:

Open Sales2.xlsx file and display the Projected Sales worksheet.

1. Select range B2:I3, click the Formulas tab, click the Create from Selection button
in the Defined Names group, then click the Top row check box to select it if
necessary, then click Ok.

2. Click the Name Manager button in the Defined Names group.

3. Click Close in the Name Manager dialog box, click the Data tab, click the What-If
Analysis button in the Forecast group, click Scenario Manager , then click
Summary in the Scenario Manage dialog box.

4. With the contents of the Result cells text box selected, click cell H3 on the
worksheet, type , (a comma), click cell I3, type , (a comma), then click cell H7.
Click Ok.

5. Right click the Column D heading, then click Delete in the shortcut menu.

6. Select the range B13:B15, press [Delete], select cell B2, edit its contents to read
Scenario Summary for New York Sales, click cell C10, and then edit its contents
to read Total New York Sales.

7. Click cell C11, edit its contents to read Percent New York Sales, click cell C12,
edit its contents to read Total R2G Sales, then click cell A1.

8. Change the page orientation to landscape, then save the workbook.

17
AACS3763 Managing Information Systems

Practical 1

3.3 Project Figures using a data file

 Steps:

Open Sales2.xlsx file and display the Projected Sales worksheet.

1. Enter Total N.Y. Sales in cell K1, widen column K to fit label, in cell K2 enter
419921, in cell K3 enter 469921, select the range K2:K3, drag the fill handle to
select the range K4:K6, then format the range using the Accounting format with zero
decimal places.

2. Click cell L1, type =, click cell I3, click the Enter button on the formula bar, then
format the value in cell L1 using the Percentage format with two decimal places.

3. With cell L1 selected, click the Home tab, click the Format button in the Cells
group, click Format Cells, click the Number tab in the Format Cells dialog box if
necessary, click Custom under Category, select any characters in the Type box, type
;;;(three semi-colons), then click Ok.
*type ;;;  hides the values in a cell

4. Select the range K1:L6, click the Data tab, click the What-If Analysis button in
the Forecast group, and then click Data Table.

5. Click the Column input cell text box, click cell H3, and then click Ok.

6. Format the range L2:L6 with the Percentage format with two decimal places, and
then click cell A1.

18
AACS3763 Managing Information Systems

Practical 1
7. Change the page orientation to landscape, then save the workbook.

3.4 Use Goal Seek

 Steps:

Open Sales.xlsx file and display the Projected Sales worksheet.

1. Click cell B8. Click the Data Tab, click the What-If Analysis button in the Forecast
group, and then click Goal Seek.

2. Click the To value text box, then type 17%.

3. Click the By changing cell text box, then click cell B3. Click Ok.

4. Click Ok, then click cell A1. Save the workbook.

3.5 Set up a complex what-if analysis with solver

 Steps:

Open Sales.xlsx file and display the Budgets worksheet.

If Solver does not appear in the Data tab, click the File tab, click Options, click Add-ins,
click Go, in the Add-ins dialog box click the Solver Add-in check box to select it, then
click OK.

1. Click the Data tab, then click the Solver button in the Analyze group.

2. With the insertion point in the Set Objective text box, click cell I7 in the worksheet,
click the Value Of option button if necessary, double-click the Value Of text box,
then type 3,100,000.

3. Click the By Changing Variable Cells text box, then select the range G3:H6 on the
worksheet.

4. Click Add, with the insertion point in the Cell Reference text box in the Add
Constraint dialog box, select the range I3:I6 in the worksheet, click the list arrow in
the dialog box, click =, then with the insertion point in the Constraint text box click
cell C9.

5 Click Add, with the insertion point in the Cell Reference text box select the range
G3:G6 in the worksheet, click the list arrow in the dialog box, click >=, then with
the insertion point in the Constraint text box click cell C11.

6. Click Add, with the insertion point in the Cell Reference text box select the range

19
AACS3763 Managing Information Systems

Practical 1
H3:H6 in the worksheet, click the list arrow in the dialog box, click >=, then with
the insertion point in the Constraint text box click cell C10, then click Ok.

To be continued in 3.6
3.6 Run solver and summarize results

 Steps: (Continued 3.5)

Open Sales.xlsx file and display the Budgets worksheet.

7. Click Solve.

8. Click Save Scenario, type Adjusted Budgets in the Scenario Name text box, click
Ok, in the Solver Results dialog box click the Restore Original Values option
button, then click Ok.

9. Click the What-If Analysis button in the Forecast group, click Scenario Manager,
with the Adjusted Budgets scenario selected in the Scenario Manager dialog box
click Summary, then click Ok.

10. Select Column A, click the Home tab if necessary, click the Delete button in the
cells group, right-click the Scenario Summary 2 sheet tab, click Rename on the
shortcut menu, type Adjusted Budgets, then press [Enter].

11. Select the range A16:A18, press [Delete], select the range A2:D3, click the Fill
Color list arrow in the Font group, click Blue, Accent 1, select the range A5:D15,
click the Fill Color list arrow in the Font group, click Blue, Accent 1, Lighter
80%, right click the row 1 header, click Delete, select cell A1, then enter Solver
Solutions.

20
AACS3763 Managing Information Systems

Practical 1

12. Enter your name in the center section of the worksheet footer, save the workbook,
then preview the worksheet.

3.7 Analyze data using the Analysis ToolPak

 Steps:

Open Sales.xlsx file and display the Projected Sales worksheet.

If Data Analysis does not appear on Data tab, click the File tab, click Options, click
Add-ins, click Go, in the Add-ins dialog box click the Analysis ToolPak check box to
select it, then click OK.

1. Click the Data tab, then click the Data Analysis button in the Analyze group.

2. Click Descriptive Statistics, then click Ok.

3. With the insertion point in the Input Range text box, select the range H3:H6 on the
worksheet.

4. Click the Columns option button in the Grouped By: area if necessary, click the
New Worksheet Ply option button in the Output options section if necessary, then
type Branch Statistics in the text box.

5. Click the Summary Statistics check box to select it, then click Ok.

6. Widen column A to display the row labels, then edit the contents of cell A1 to read
Total Projected Sales Jan – Jun.

Enter your name in the center section of the Branch Statistics footer, preview the
worksheet, then save the workbook.

21
AACS3763 Managing Information Systems

Practical 1

22
AACS3763 Managing Information Systems

Practical 1

Practical Exercise:
Open Repair.xlsx file.
1. Track What-if analysis with Scenario Manager
a. Click the Stair Stepper Repair worksheet, select the range B3:B5, then use the
Scenario Manager to setup a scenario called Most Likely with the current data input
values.
b. Add a scenario called Best Case using same changing cells, but change the Labor cost
per hour in the $B$3 text box to 80, change the Parts cost per job in the $B$4 text box
to 70, then change the Hours per job value in cell $B$5 to 2.5.
c. Add Scenario called Worst case. For this scenario, change the Labor cost per hour in
the $B$3 text box to 95, change the Parts cost per job in the $B$4 text box to 85, then
change the Hours per job value in cell $B$5 to 4.
d. Drag the Scenario Manager Dialog box to the right until columns A and B are visible.
e. Show the Worst Case scenario results, and view the total job cost.
f. Show the Best Case scenario results, and observe the job cost. Finally, display the
Most Likely scenario results.
g. Close the Scenario Manager Dialog box.
h. Save the workbook.

2. Generate a scenario summary – Continued Question 1

a. Create names for the input value cells and the dependant cell using the range A3:B7.
b. Verify that the names were created. (You will see other names in the Name Manager
dialog box)
c. Create a scenario summary report, using the Cost to complete job value in cell B7 as
the result cell.
d. Edit the title of the Summary report in cell B2 to read Scenario Summary for Stair
Stepper Repair.
e. Delete the Current Values column.
f. Delete the notes beginning in cell B11.
g. Return to cell A1, save the workbook.

3. Project Figures using a data table

a. Click the Stair Stepper Repair worksheet, enter the label Labor $ in cell D3.
b. Format the label so that it is bold and right-aligned.
c. In cell D4, enter 80; then in cell D5, enter 85.
d. Select the range D4:D5, then use the fill handle to extend the series to cell D8.
e. In cell E3, reference the job cost formula by entering =B7.
f. Format the contents of cell E3 as hidden, using the ;;; Custom formatting type on the
Number tab of the Format Cells dialog box.
g. Generate the new job costs based on the varying labor costs. Select the range D3:E8
and create a data table. In the data table dialog box, make cell B3 ( the labor cost ) the
column inout cell.
h. Format the range E4:E8 as currency with two decimal places.
i. Save the workbook.

23
AACS3763 Managing Information Systems

Practical 1

4. Use Goal Seek

a. Click cell B7, and open the Goal Seek dialog box.
b. Assuming the labor rate and the hours remain the same, determine what the parts
would have to cost so that the cost to complete the job is $300. (Hint: Enter a job cost
of 300 as the To value, and enter B4 (the Parts cost) as the By changing cell). Write
down the parts cost that Goal Seek finds.
c. Click Ok, then use [CTRL][Z] to reset the parts cost to its original value.
d. Enter the parts cost that you found in step 4b into cell A14.
e. Assuming the parts cost and hours remain the same, determine the labor so that the
cost to complete the job is $300. Use [CTRL][Z] to rest the labor cost to its original
value. Enter the labor cost in cell A15.
f. Save the workbook.

1. Set up a complex what-if analysis with Solver

a. Click the Equipment Repair worksheet, open the Solver Parameters dialog box.
b. Make B14 (the total repair costs) the objective cell, with a target value of 16,000.
c. Use cells B6:D6 (the number of all scheduled repairs) as the changing cells.
d. Specify that cells B6:D6 must be integer. (Hint: select int in the Add Constraint dialog
box).
e. Specify a constraint that cells B6:D6 must be greater than or equal to 10.

2. Run Solver and summarize results

a. Use Solver to find a solution.
b. Save the solution as a scenario named Equipment Repair Solution, and restore the
original values to the worksheet,
c. Create a scenario summary using the Repair Solution, delete the notes at the bottom
of the solution, and change the title in cell B2 to Repair Solution.
d. Save the workbook.

3. Analyze data using the Analysis ToolPak.

a. With the Equipment Repair worksheet, generate summary descriptive statistics for the
repair cost per model, using cells B10:D10 as the input range. (Hint: The input is
grouped in a row). Place the statistics in a new worksheet named Repair Cost
Statistics.
b. Widen column as necessary to view statistics.
c. Change the contents of cell A1 to Repair Costs. Delete row 6 containing the mode
error (generated because you do not have any repeating data values). Also delete row
9 containing the kurtosis error information (generated because you only have three
data values).
d. Save and close the workbook.

24