AMIS3723 Digital Literacy for Finance Assignment Brief 2026 | TAR UMT

AMIS3723 Assignment Brief

3.0 Perform What-if Analysis and Use Scenario

3.1 Track What-if Analysis with Scenario Manager

Open  Sales.xlsx  file.

Display the  Projected Sales  worksheet.

Steps:

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 .

3.2 Generate a Scenario Summary

Open  Sales2.xlsx  file.

Display the  Projected Sales  worksheet.

Steps:

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 Manager 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  Scenario Summary for New York Sales , click  cell C10,   and then edit its  contents to  Total New York Sales .

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

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

3.3 Project Figures using a Data File

Open  Sales2.xlsx  file.

Display the  Projected Sales  worksheet.

Steps:

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 , erase any  characters in the Type  box, then type  ;;;  (three semicolons), then click  OK .

Note: 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:L

6 with the  Percentage format  with  two decimal places ,  and then click  cell A1 .

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

3.4 Use Goal Seek

Open  Sales.xlsx  file.

Display the  Projected Sales  worksheet.

Steps:

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.

Practical Exercise 3

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 set up a scenario called Most Likely with the  current data input values.

b. Add a scenario called Best Case using the 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

a. Create names for the input value cells and the dependent 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

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

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 ) in the column input cell.

h. Format the range E4:E8 as currency with two decimal places.

i. Save the workbook.

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

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.

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

g. Save the workbook.