12.4 EXCEL PROJECT. (Hint: For help on steps b and c, see the article “Dial a
Forecast,” by James A. Weisel, in the December 2006 issue of the Journal of
Accountancy. The Journal of Accountancy is available in print or online at the
AICPA’s Web site: www.aicpa.org
Required:
a. Create a 12-month cash flow budget in Excel using the following assumptions:
• Initial sales of $5,000,000 with forecasted monthly growth of 1%
• 40% of each month’s sales for cash; 30% collected the following month; 20%
collected 2 months later; 8% collected 3 months later; and 2% never
collected
• Initial cash balance of $350,000
Formulas (the formulas for June – December are similar to those shown in the column for
April and May)
b. Add a “spinner” to your spreadsheet that will enable you to easily change
forecasted monthly sales growth to range from 0.5% to 1.5% in increments of
0.1%.
In part b, we will create a spin button to change the assumed sales growth rate.
Step 1: Click on the “Developer” tab and then click on the “Insert” button as shown:
Step 2: In the drop-down menu that appears when you click on “Insert”, click on the “Spin
button” option from the Active X controls choices (move your mouse over the various Active
X choices to reveal their names – the Spin button is the larger pair of arrows)
Then click on a cell that is two cells to the right of the one that contains your initial assumption
for the sales growth rate (i.e., cell F5) which will result in the following:
Step 3: Now we have to link the spin button tool to the cell that we wish to manipulate. In this
case, the objective is to be able to vary the sales growth rate (in cell D5) from 0.5% to 1.5%.
Step 4: Now right–click on the spin button, then select “Properties” and enter the following
values:
Step 5: Click the “Design Mode” option in the tool bar to exit Design Mode. You can now click
c. Add a scroll bar to your spreadsheet that will let you modify the amount of initial
sales to vary from $4,000,000 to $6,000,000 in increments of $100,000.
Step 1: In Developer Tab, click on Design Mode to get back into Design Mode. Then click on
Insert. Select the scroll bar option from the Active X choices that appear. (As before, moving
your mouse over the choices reveals their names. The scroll bar option is the smaller pair of
arrows). Move to cell F4 and click to enter the scroll bar there. Your spreadsheet should now
look like this:
Step 2: Click on one corner of the scroll bar and drag it so that it fills cell F4 horizontally. Your
spreadsheet should now look like this:
