Problem

Excel Problem*Objective: How to do what-if analysis with graphs.REQUIREDa. Read the articl...

Excel Problem*

Objective: How to do what-if analysis with graphs.

REQUIRED

a. Read the article “Tweaking the Numbers,” by Theo Callahan in the June 2001 issue of the Journal of Accountancy (available at www.aicpa.org). Follow the instructions in the article to create a spreadsheet with graphs that do what-if analysis.
b. Now create a spreadsheet to do graphical what-if analysis for the “cash gap.” Cash gap represents the number of days between when a company has to pay its suppliers and when it gets paid by its customers. Thus,
The purpose of your spreadsheet is to display visually what happens to cash gap when you “tweak” policies concerning inventory, receivables, and payables. Thus, you will create a spreadsheet that looks like Figure.
c. Set the three spin buttons to have the following values:

SPIN BUTTON INVENTORY
SPIN BUTTON FOR RECEIVABLES
SPIN BUTTON FOR PAYABLES
Linked cell
C2
C3
C4
Maximum
120
120
90
Minimum
0
30
20
Value
30
60
20
Small change
10
10
10
d. The article “Analyzing Liquidity: Using the Cash Conversion Cycle” by C. S. Cagle, S. N. Campbell, and K. T. Jones in the Journal of Accountancy (May 2013), pp. 44–48 calls the “Cash Gap” the “Currency Conversion Cycle” and explains that bigger values are bad because they indicate less liquidity (because cash needed to pay suppliers is tied up in receivables and inventory). Indeed, the “cash gap” can even be negative for companies, like Dell, that collect payment from customers in advance and stretch out payments to suppliers as long as possible. Given that background, collect the information from annual reports needed to calculate the “cash gap” for at least 3 years for Dell and 3 or more other companies. Enter that data in a spreadsheet and create a graph that you think best highlights the trend in cash gap across the different companies.