View the step-by-step solution to: 12.4 Excel Project. (Hint: For help on steps b and c, see the

12.4 Excel Project. (Hint: For help on s...
This question was answered on Dec 03, 2011. View the Answer
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.
weisel_xyz_forecast.xls

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 Income Taxes
Net Operating Income
Return on Sales
Assumptions
Base Quarter Sales
Quarterly Sales Growth Rate
Sale Returns & Allowance Rate
Cost of Sales Rate
Marketing & Promotion
General & Administration

Base Quarter
Actual Quarter 1 Forecast Quarter 2 Forecast
$100,000
$110,000
$121,000
2,000
2,200
2,420
$98,000
$107,800
$118,580
70,000
77,000
84,700
$28,000
$30,800
$33,880
22,000
22,000
22,000
9,000
9,000
9,000
$(3,000)
$(200)
$2,880
(1,020)
(68)
979
$(1,980)
$(132)
$1,901
-2.0%
$100,000
10.0%
2.0%
70.0%
$22,000
$9,000

-0.1%

1.6%

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 Income Taxes
Net Operating Income
Return on Sales
Assumptions
Base Quarter Sales
Quarterly Sales Growth Rate
Sale Returns & Allowance Rate
Cost of Sales Rate
Marketing & Promotion
General & Administration

Base Quarter
Actual Quarter 1 Forecast Quarter 2 Forecast
$100,000
$110,000
$121,000
2,000
2,200
2,420
$98,000
$107,800
$118,580
70,000
77,000
84,700
$28,000
$30,800
$33,880
22,000
22,000
22,000
9,000
9,000
9,000
$(3,000)
$(200)
$2,880
(1,020)
(68)
979
$(1,980)
$(132)
$1,901
-2.0%

$100,000
10.0%
2.0%
70.0%
$22,000
$9,000

-0.1%

1.6%

100

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 Income Taxes
Net Operating Income
Return on Sales
Assumptions
Base Quarter Sales
Quarterly Sales Growth Rate
Sale Returns & Allowance Rate
Cost of Sales Rate
Marketing & Promotion
General & Administration

Base Quarter
Actual Quarter 1 Forecast Quarter 2 Forecast
$100,000
$110,000
$121,000
2,000
2,200
2,420
$98,000
$107,800
$118,580
70,000
77,000
84,700
$28,000
$30,800
$33,880
22,000
22,000
22,000
9,000
9,000
9,000
$(3,000)
$(200)
$2,880
(1,020)
(68)
979
$(1,980)
$(132)
$1,901
-2.0%
$100,000
10.0%
2.0%
70.0%
$22,000
$9,000

-0.1%

1.6%

Use the arrows to the left
to change the sales growth rate

100

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 Income Taxes
Net Operating Income
Return on Sales
Assumptions
Base Quarter Sales
Quarterly Sales Growth Rate
Sale Returns & Allowance Rate
Cost of Sales Rate
Marketing & Promotion
General & Administration

Base Quarter
Actual Quarter 1 Forecast Quarter 2 Forecast
$100,000
$110,000
$121,000
2,000
2,200
2,420
$98,000
$107,800
$118,580
70,000
77,000
84,700
$28,000
$30,800
$33,880
22,000
22,000
22,000
9,000
9,000
9,000
$(3,000)
$(200)
$2,880
(1,020)
(68)
979
$(1,980)
$(132)
$1,901
-2.0%

$100,000
10.0%
2.0%
70.0%
$22,000
$9,000

-0.1%

1.6%

Use the arrows to the left
to change the sales growth rate

100

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 Income Taxes
Net Operating Income
Return on Sales
Assumptions
Base Quarter Sales
Quarterly Sales Growth Rate
Sale Returns & Allowance Rate
Cost of Sales Rate
Marketing & Promotion
General & Administration

Base Quarter
Actual Quarter 1 Forecast Quarter 2 Forecast
$$$$$$$$$22,000
22,000
22,000
9,000
9,000
9,000
$(31,000)
$(31,000)
$(31,000)
(10,540)
(10,540)
(10,540)
$(20,460)
$(20,460)
$(20,460)
#DIV/0!
$100.0%
2.0%
70.0%
$22,000
$9,000

#DIV/0!

#DIV/0!
Use the arrows or drag the
center bar to adjust base quarter sales

Use the arrows to the left
to change the sales growth rate

1000
100

20

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 Income Taxes
Net Operating Income
Return on Sales
Assumptions
Base Quarter Sales
Quarterly Sales Growth Rate
Sale Returns & Allowance Rate
Cost of Sales Rate
Marketing & Promotion
General & Administration

Base Quarter
Actual Quarter 1 Forecast Quarter 2 Forecast
$80,000
$144,000
$259,200
1,600
2,880
5,184
$78,400
$141,120
$254,016
56,000
100,800
181,440
$22,400
$40,320
$72,576
22,000
22,000
22,000
9,000
9,000
9,000
$(8,600)
$9,320
$41,576
(2,924)
3,169
14,136
$(5,676)
$6,151
$27,440
-7.1%
$80,000
80.0%
2.0%
70.0%
$22,000
$9,000

4.3%

10.6%
Use the arrows or drag the
center bar to adjust base quarter sales

Use the arrows to the left
to change the sales growth rate

800
100

60

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 Income Taxes
Net Operating Income
Return on Sales
Assumptions
Base Quarter Sales
Quarterly Sales Growth Rate
Sale Returns & Allowance Rate
Cost of Sales Rate
Marketing & Promotion
General & Administration

Base Quarter
Actual Quarter 1 Forecast Quarter 2 Forecast
$100,000
$110,000
$121,000
2,000
2,200
2,420
$98,000
$107,800
$118,580
70,000
77,000
84,700
$28,000
$30,800
$33,880
22,000
22,000
22,000
9,000
9,000
9,000
$(3,000)
$(200)
$2,880
(1,020)
(68)
979
$(1,980)
$(132)
$1,901
-2.0%
$100,000
10.0%
2.0%
70.0%
$22,000
$9,000

-0.1%

1.6%
Use the arrows or drag the
center bar to adjust base quarter sales

Use the arrows to the left
to change the sales growth rate

1000
100

Sign up to view the entire interaction

Dear Student, Please find s... View the full answer

7718786 - Accounting.xls

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

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors and customizable flashcards—available anywhere, anytime.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access or to earn money with our Marketplace.

    Browse Documents
  • 890,990,898

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
  • 890,990,898

    Flashcards

    Browse existing sets or create your own using our digital flashcard system. A simple yet effective studying tool to help you earn the grade that you want!

    Browse Flashcards