View the step-by-step solution to: Excel Project A. Expand the cash flow budget you created in

Excel Project A. Expand the cash flow b...
This question was answered on Dec 03, 2011. View the Answer
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, I have reviewed your assignment thoroughly, based on your assignment details and current requirement. I am unable... View the full answer

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