This question has been answered by Expert on Dec 3, 2011. View Solution
FtmK3 posted a question
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.
http://www.journalofaccountancy.com/Issues/2006/Dec/DialAForecast

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

13.6 (12.4 expansion)

Excel Project

A. Expand the cash flow budget you created in Problem 12.4 to include a row for expected cash outflows equal to 77% of the current month's sales.

B. Also add a row to calculate the amount of cash that needs to be borrowed in order to maintain a minimum cash balance of $50,000 at the end of each month.

C. Add another row to show the cash inflow from borrowing.

D. Add another row to show the cumulative amount borrowed.

E. Add another row to show the amount of the loan that can be repaid, being sure to maintain a minimum ending balance of $50,000 each month.

F. Add appropriate data validation controls to ensure spreadsheet accuracy.
Attachment Preview:
  • weisel_xyz_forecast.xls Download Attachment

    XYZ Company, Quarterly P&L Forecast
    Sales
    Sales Returns & Allowances
    Net Sales
    Cost of Sales
    Gross Profit
    Marketing & Promotion
    General & Administration
    Operating Income Before Taxes
    Provision for...
    Show more

Expert answered the question
Dear Student,
Please...  View Full Answer

Download Preview:

Cash Budget
April
Sales
Opening Cash Balance
Cash Sales

May

June

July
4430294

August
September
October
November
December
January
February
March
Total
4474597
4519343
4564536
4610181
4656283...