Calculate the cash balance.

Question

Company is preparing its cash budget for the 3rd quarter of 2015. All sales are made on credit and expenses equal 60% of sales. All expenses are paid off in the same quarter except income tax. Income tax rate is 25% and each quarter’s income tax is paid in the following quarter.
The following is the information of sales for each quarter.
Quarter 1 = $5.6 million
Quarter 2 = $6.2 million
Quarter 3 = $7.4 million
Quarter 4 = $5.0 million
20% of sales is collected in the same quarter, 50% in the following quarter and 30% in the second quarter following sales.
Expected cash balance at the beginning of quarter 3 = $2.1 million

Calculate the cash balance at the end of quarter 3.

Answer 1

Here is a spreadsheet (preferred method of calculation for this problem):
  
Enter formulas for cells based on Sales (this quarter, last quarter, two quarters ago), taxes (on last quarter sales).
 
                                       Quarter 1      Quarter 2     Quarter 3        Quarter 4                                  
Sales                            $5,600,000    $6,200,000   $7,400,000     $5,000,000                                 
Expenses                      $3,360,000    $3,720,000    $4,440,000    $3,000,000                                 
Tax on previous quarter                        $840,000       $930,000    $1,110,000                                
Income from this           $1,120,000    $1,240,000    $1,480,000    $1,000,000     
  month's sales  

Income from last                                $2,800,000    $3,100,000    $3,700,000  $2,500,000          
  month's sales
Income from sales
  two months ago                                                    $1,680,000  $1,860,000  $2,220,000  $1,500,000
Beginning Expected cash balance                              $2,100,000                                
Ending Expected cash balance                                 $10,390,000