Design appropriate data entry and processing controls to

Question

A. Create a 12 month cash budget in Excel using the following assumptions:

• Initial sales of $5,000,000 with forecasted monthly growth of 1%.

• 40% of each months sales for cash; 30% for the following month ; 20% collected 2 months later; 8% collected 3 months later, and 2% never collected.

• Initial cash balance of 350,000.

B. Add a spinner to your spreadsheet that will enable you to easily change the forecasted monthly sales growth to range from .5% to 1.5% in increments of .1%.

C. Add a scroll bar to your spreadsheet that will let you modify the amount of the initial sales to vary from $4,000,000 to $6,000,000 in increments of $100,000.

D. Design appropriate data entry and processing controls to ensure the spreadsheet accuracy.

Answer 1

Q1. Prepare a 12 month cash budget in Excel employing the given suppositions: • Initial sales of $5,000,000 having forecasted monthly growth of 1%. • 40% of each month’s sales for cash; 30% for the following month; 20% collected 2 months later; 8% collected 3 months later, and 2% never collected. • Initial cash balance of 350,000. Q2. Add a spinner to your spread-sheet which will let you to simply change the forecasted monthly sales growth to range from .5% to 1.5% in increments of .1%. Q3. Add a scroll bar to your spread-sheet which will let you change the amount of the initial sales to differ from $4,000,000 to $6,000,000 in increments of $100,000. Q4. Design suitable data entry and processing controls to make sure the spreadsheet precision.