Excel: Calculating Scenarios with What If Analysis

There are many reasons you might be running through a list of possible scenarios. If you would like to transform your data into a visualization of a variety of possibilities, you might be interested in the What If Analysis feature in Excel.

Video

Here is an accompanying video for this blog post. I hope this is helpful!

A Fictional Applicable Situation

Think of a business considering a start date, weighing the cost of a delayed open. They have two possible amounts for how much they stand to lose per day if they don’t open: $250 loss per day and $500 loss per day. They also have three possible proposed open/start dates: 6/1/20, 7/1/20, and 8/1/20. The owner would like to know a variety of outcomes, given different possible start dates and different possible income loss per day to delay opening.

Of course, this is a vastly over simplified version of a business accounting model, but will hopefully serve to show you how this feature works.

Document Formulas

The two cells that will prompt changes in our What If Scenario are the Start Date, of which there are three possibilities (June, July, August), and Loss/Day, of which there are two possibilities ($250 and $500).

The total Expenses and Balance in reserve will change as the date and loss figures change.

Creating Scenarios

To create What If scenarios:

1. Go to the Data tab, and select the dropdown for What-If Analysis. Select Scenario Manager.

2. If you have not created any scenarios, this area will be blank. Press Add.

3. Name your scenario something that will help you identify it later. I identified mine with the elements that would be changing in each scenario: the start date and the loss per day.

4. Click into the blank space next to Changing Cells and select a cell that you would like to create a scenario for. In my case, I selected two cells (for date and loss per day) by holding down the Ctrl key and selecting each of them. Press OK.

5. For my first scenario, I wanted to see what would happen if B1 were the date 6/1/2020, and B3 were $500. I entered those figures accordingly, then pressed Add.

6. Create as many scenarios as you would like. I made six possible scenarios and for the different possible loss amounts and start dates.

7. Select a scenario and press show to run the What If Analysis.

There is also an option to generate a summary on a new sheet with all the possible scenarios. Press Summary to generate this.

Accessing Scenarios

You can access scenarios this way:

1. Go to the Data tab, What-If Analysis dropdown, and select Scenario Manager.

Each of these listed scenarios represents a potential start date and a different loss per day amount.

2. To examine a scenario, either double click on one of them, or select one and press Show.

3. The various figures change as you select different possible scenarios: a July start date, with $250 loss per day means the total expenses were $17,000 and remaining balance $33,000.

An August start date with $500 loss per day leaves a $500 remaining balance.

Extras: Macros and Charts

You can customize your What If scenario with charts and macros. Look at this setup below, incorporating a Donut Chart (like this one) to accompany the data, and some text boxes linked to macros to run the What If Scenarios.

Pretty handy for a presentation or for distributing to others without having to send instructions for running the What If scenarios.

Downloads

I made a couple versions of this document for you to examine that incorporate the What If Analysis:

Original Version

Macro Enabled Version with Chart and Extras

Warning: you may choose to set up formulas a variety of ways, but the formulas in these documents were set up for forecasting, so are not intended to be access to far in the future. Long story short, you may get an error if you are opening these documents too many months from now; this is due to the formulas, not the What If feature.

Thoughts?

What do you think? Has this sparked any ideas? I hope this feature helps you out as you are making plans for different future possibilities.

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

PowerPoint: Create a GIF from a Presentation

First of all, is it pronounced GIF, or GIF? Did you just say it in your head both ways? There is endless debate about the pronunciation, and for this I am grateful this article is in print rather than spoken. GIF stands for Graphics Interchange Format. You have likely experienced these short looping animations in a variety of ways, most notably in social media circles. Thanks to Office 365, you now have a new way of creating GIFs… through PowerPoint! Let’s take a look.

Action in the Presentation

Ideally, you will want these to be short and simple presentations. Because GIFs are all about action, consider applying a few actions (Transitions and Animations) to the presentation.

To learn more about Transitions and Animations, I would love to see you at a PowerPoint Essentials session!

You can set up timings for Animations in the Timing group of the Animations tab. Any durations and delays you apply will be honored when you turn the presentation into a GIF.

Timings

For transitions, any “mouse click” items will automatically be turned into a timed action, but you can choose to set timings for slides that you would like to allow a little extra time on for your end users. You can alter the “After” setting in the Transitions tab to accomplish this.

Exporting, and Settings

Once you have your PowerPoint ready to go, visit File, Export and select Create an Animated GIF.

Notice you will have the ability to change the file size and adjust the seconds spent on a slide, which will be overridden by any timings you set in the previous steps.

When you select Create GIF, you will be prompted to select a location, and you will be done!

Thoughts?

What do you think? Will you create GIFs from your PowerPoint Presentations?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Compatibility Mode: How to Re-Activate Your Favorite Features

This happens frequently after a PivotTables session… An attendee excitedly returns to their computer to create a PivotTable from an existing Excel file, only to discover that the PivotTable screen looks a lot different than it did in class, and a lot of their favorite features are grayed out in the ribbon. This is not just an Excel issue… sometimes this happens Word or PowerPoint: certain features are mysteriously faded in the user’s ribbon. The culprit? Compatibility Mode. Let’s talk about this setting, and how to escape from it, in your Microsoft Office documents.

What is Compatibility Mode?

The intentions of Compatibility Mode are in the right place. Its purpose is to make a document as functional as possible with older versions of software. So if you are using Office 365, and your friend is running Office 2010, you could send this Compatibility Mode document to them and rest assured it will look the same to them as it does to you.

For this reason, you will often see data that is exported from other programs; Reporting Services and WSU Reporting, for instance; default to Compatibility Mode. The software programmers aren’t sure what version of Office you will be using, so they err on the safe side, and have the export default to Compatibility Mode.

Another possibility: you are opening an old document. If someone created this document in 2001, and never updated the file format since then (we will talk about this further down), chances are you are in Compatibility Mode by default.

Missing Features

Earlier I mentioned that PivotTables are often the first place people notice missing features in the ribbon…

… But this happens in other Excel tabs, PowerPoint, and Word as well.

The features that gray out vary from version to version, but bottom line, newer features are what disappear in Compatibility Mode. When someone is experiencing these ghosts of buttons, this is almost always the culprit.

How to identify Compatibility Mode

How do you know for sure if your document is in Compatibility Mode? There are several ways.

1. You might have some visual cues; the icons look slightly different in Compatibility Mode, shown below, on the left side of the icons.

2. At the top of your document, next to the title, you might see the words Compatibility Mode

3. Go to your Save As screen (File, Save As). What do you see in the dropdown under the title? If it says Excel 97-2003 Workbook, you are in Compatibility Mode.

In fact, stay in the Save As screen, and I will show you how to fix this.

How to Escape From This Setting (and get your favorite features back)

In your Save As screen (shortcut: F12), Compatibility Mode reads as Excel 97-2003 as the File type (dropdown under the document title).

To fix this issue, click on the dropdown under the title, and select the first option for Type: Excel Workbook in this case (Word Document for Word and PowerPoint Presentation for PowerPoint).

Important: you must close the file and reopen it to see the new options appear.

That’s it! You have your features back! I hope this helps with some of your legacy documents or documents exported from various sources.

Thoughts?

What do you think? Have you been plagued by the trials and tribulations of Compatibility Mode?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

PowerPoint: Secret Recording Tab

We are all finding ourselves making more recordings in PowerPoint lately. I hope you had a chance to try out the new and improved Record Slide Show feature in PowerPoint. In fact, you are probably using a variety of recording features in PowerPoint and have likely noticed that these features are spread out throughout numerous different tabs in the ribbon: Slide Show recording is on the Slide Show tab, screen recording on the Insert tab, exporting a video is in Backstage View, and so on. Let’s simplify the process with a secret tab: the Recording Tab.

Activate the Recording Tab

We learn about another secret tab in Excel Macros session, but PowerPoint has its own special tab: the Recording Tab.

To activate this tab:

1. Right click anywhere on the contents of the ribbon.

2. Select Customize the Ribbon.

3. On the right side of the popup is a list of the tabs in your ribbon. Scroll down to Recording. Click on the checkbox next to this tab to activate it in the ribbon.

4. Press OK.

Recording Tab

Hey, you have a new tab! How exciting. Here are some details about what you can access here.

A. Record Slide Show: A feature found on the Slide Show Tab. Learn more about this feature here.

B. Screenshot: Normally found on the Insert Tab, this feature allows you to grab a picture of your screen for a presentation.

C. Screen Recording, Video, and Audio: Also found on the Insert Tab, powerful tools for inserting video, audio, or recording your screen in action.

D. Save as Show, Export to Video: Typically you have to venture to Backstage View (File) to access these features.

Just to reiterate… you are not accessing new features with the Recording Tab, but you may find this to be a time saver as it brings together features that normally live all over the ribbon in various disparate tabs.

Thoughts?

What do you think? Will you utilize this secret tab to save some time when recording in PowerPoint?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Maddie Domebo (Picture coming soon!)