View the step-by-step solution to:

XYZ Company, Quarterly P&L Forecast Sales Sales Returns & Allowances Net Sales Cost of Sales Gross Profit Marketing &

This question was answered on Dec 01, 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

Top Answer

Please increase the deadline for your question to... View the full answer

This question was asked on Dec 01, 2011 and answered on Dec 01, 2011.

Recently Asked Questions

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

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

    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