View the step-by-step solution to:

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

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.
XYZ Company, Quarterly P&L Forecast Quarter 1 Forecast Quarter 2 Forecast Sales \$100,000 \$110,000 \$121,000 Sales Returns & Allowances 2,000 2,200 2,420 Net Sales \$98,000 \$107,800 \$118,580 Cost of Sales 70,000 77,000 84,700 Gross Profit \$28,000 \$30,800 \$33,880 Marketing & Promotion 22,000 22,000 22,000 General & Administration 9,000 9,000 9,000 Operating Income Before Taxes \$(3,000) \$(200) \$2,880 Provision for Income Taxes (1,020) (68) 979 Net Operating Income \$(1,980) \$(132) \$1,901 Return on Sales -2.0% -0.1% 1.6% Assumptions Base Quarter Sales \$100,000 Quarterly Sales Growth Rate 10.0% Sale Returns & Allowance Rate 2.0% Cost of Sales Rate 70.0% Marketing & Promotion \$22,000 General & Administration \$9,000 Base Quarter Actual
XYZ Company, Quarterly P&L Forecast Quarter 1 Forecast Quarter 2 Forecast Sales \$100,000 \$110,000 \$121,000 Sales Returns & Allowances 2,000 2,200 2,420 Net Sales \$98,000 \$107,800 \$118,580 Cost of Sales 70,000 77,000 84,700 Gross Profit \$28,000 \$30,800 \$33,880 Marketing & Promotion 22,000 22,000 22,000 General & Administration 9,000 9,000 9,000 Operating Income Before Taxes \$(3,000) \$(200) \$2,880 Provision for Income Taxes (1,020) (68) 979 Net Operating Income \$(1,980) \$(132) \$1,901 Return on Sales -2.0% -0.1% 1.6% Assumptions Base Quarter Sales \$100,000 Quarterly Sales Growth Rate 10.0% 100 Sale Returns & Allowance Rate 2.0% Cost of Sales Rate 70.0% Marketing & Promotion \$22,000 General & Administration \$9,000 Base Quarter Actual
Show entire document
Sign up to view the entire interaction

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

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