case4

profileMaria Castro
JacksonAutomotive-FinalSpreadsheets1.xlsx
Home>Business & Finance homework help>case4

Exhibit 1

Exhibit 1 Monthly Shipments at Selling Price (thousands of dollars)
Month Forecast as of September 2012 Actual Forecast as of May 2013
2012 October 6,508 6,421
November 6,219 6,302
December 6,250 6,009
2013 January 6,074 6,170
February 5,996 6,006
March 6,291 5,197
April 6,515 4,165
May 6,832 3,744
Eight months total 50,685 44,014
June 6,759 12,681
July 6,645 7,374
August 6,478 7,201
September 6,433 7,394
Twelve months total 26,315 34,650
Fiscal year total 77,000 78,664

Exhibit 2

Exhibit 2 Balance Sheets, 2012-2013 (thousands of dollars)
2012 2013
August September October November December January February March April May
Cash 8,350 3,328 3,523 4,511 4,239 4,878 5,182 3,962 6,277 4,994
Accounts receivablea 5,793 5,969 6,421 5,851 6,009 6,170 5,606 5,197 3,365 3,744
Inventory 7,154 7,364 7,524 7,219 7,277 7,097 7,529 8,371 11,234 12,163
Current assets 21,297 16,661 17,468 17,581 17,525 18,145 18,317 17,530 20,876 20,901
Gross PP&E 45,500 45,500 45,500 45,500 45,500 45,500 45,500 45,500 45,500 45,500
Accumulated depreciationb 30,368 30,488 30,608 30,728 30,848 30,968 31,088 31,208 31,328 31,448
Net PP&E 15,132 15,012 14,892 14,772 14,652 14,532 14,412 14,292 14,172 14,052
Prepaid expenses 242 58 23 45 47 52 65 46 46 54
Total assets 36,671 31,731 32,383 32,398 32,224 32,729 32,794 31,868 35,094 35,007
Accounts payablec 4,977 5,197 5,347 5,352 5,110 5,130 5,162 5,122 6,223 5,969
Notes payable, bank 0 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000
Accrued taxesd 252 4 174 331 107 269 417 140 216 273
Other accrued expenses 1,500 1,542 1,542 1,542 1,542 1,542 1,542 1,142 1,142 1,142
Customer advance payments 1,651 1,651 1,651 1,200 1,200 1,200 800 800 2,700 2,700
Current liabilities 8,380 13,394 13,714 13,425 12,959 13,141 12,921 12,204 15,281 15,084
Shareholders' equity 28,291 18,337 18,668 18,973 19,265 19,588 19,874 19,664 19,813 19,923
Total liabilities and equity 36,671 31,731 32,383 32,398 32,224 32,729 32,794 31,868 35,094 35,007
a Selling term of net 30 days.
b Depreciation of $120,000 per month.
c Purchase terms of net 30 days.
d Outstanding taxes on 2012 fiscal year income were due January 15, 2013. On December 15, 2011, March 15, 2012, June 15, 2012, and September 15, 2012, payments of 25% of each of the estimated tax for 2012 ($1,500,000) were due. Taxes payable for 2013 were assumed to be $1,500,000 and would be paid on December 15, 2012, March 15, 2013, June 15, 2013, and September 15, 2013, in equal increments.

Exhibit 3

Exhibit 3 Income Statements, 2012-2013 (thousands of dollars)
2012 August 2012 September Fiscal Year Ending 09/30/2012 2012 October 2012 November 2012 December 2013 January 2013 February 2013 March 2013 April 2013 May Eight Months Total
Net sales 6,321 5,969 71,642 6,421 6,302 6,009 6,170 6,006 5,197 4,165 3,744 44,014
COGS 4,994 4,727 56,955 5,003 4,914 4,695 4,815 4,692 4,087 3,215 2,876 34,297
Gross profit 1,327 1,242 14,687 1,418 1,388 1,314 1,355 1,314 1,110 950 868 9,717
Operating expenses 773 763 9,509 777 788 733 728 744 685 587 566 5,608
Depreciation and amortization 120 120 1,440 120 120 120 120 120 120 120 120 960
Interest expensea 0 0 0 25 25 25 25 25 25 25 25 200
Interest incomeb 13 14 120 6 6 8 7 8 9 7 10 60
Profit (loss) before tax 447 373 3,858 502 461 443 489 433 289 225 167 3,009
Income taxesc 152 127 1,312 171 157 151 166 147 98 76 57 1,023
Net income 295 246 2,546 331 304 293 323 286 191 148 111 1,986
Dividends 0 200 400 0 0 0 0 0 400 0 0 400
a 6% annualized interest rate charged on outstanding bank loans.
b 2% annualized rate of return on beginning monthly cash balances.
c The federal tax rate on all earnings was 34%.

Table 1

Table 1: Sources and Uses of Funds, August 31, 2012 to May 31, 2013 (thousands of dollars)
Sources of Funds
Increase in bank debt 5,000
Decrease in cash 3,356
Decrease in accounts receivable 2,049
Increase in retained earnings 1,632
Decrease in net fixed assets 1,080
Increase in customer advances 1,049
Increase in accounts payable 992
Decrease in prepaid expenses 188
Increase in taxes payable 21
Total Sources of Funds 15,367 <---Check: Total Sources of Funds should equal 15,367
Uses of Funds
Stock repurchase 10,000
Inventories 5,009
Decrease in accruals 358
Total Uses of Funds 15,367 <---Check: Total Uses of Funds should equal 15,367

Table 2

Table 2: Pro Forma Cash Budget, June to October 2013 (thousands of dollars)
June July August September October
Beginning Cash Balance 4,994 1,627 6,536 7,034 (715)
Cash Receipts
Collections of accounts receivable 3,744 10,881 6,474 7,201 7,394
Interest income 8 3 11 12 0
Bank loan 0 2,400 0 0 0
Total cash inflow 3,752 13,284 6,485 7,213 7,394
Cash Disbursements
Payments of accounts payable 5,969 5,200 5,200 5,200 5,200
Operating expenses 750 750 750 750 750
Capital expenditure 0 2,400 0 0 0
Tax payments 375 0 0 375 0
Interest payments 25 25 37 37 0
Principal payments 0 0 0 7,400 0
Dividend payments 0 0 0 1,200 0
Total cash outflow 7,119 8,375 5,987 14,962 5,950
Net cash inflow (outflow) (3,367) 4,909 498 (7,749) 1,444
Ending Cash Balance 1,627 6,536 7,034 (715) 729 <---Check: Ending Cash Balances should equal 1627, 6536, 7034, -715, and 729.

Table 3

Table 3: Pro Forma Income Statements, June to October 2013 (thousands of dollars)
June July August September Four Months Total
Net sales 12,681 7,374 7,201 7,394 34,650
COGSa 10,850 5,810 5,810 5,810 28,280
Gross profit 1,831 1,564 1,391 1,584 6,370
Operating expenses 750 750 750 750 3,000
Depreciation and amortization 120 120 130 130 500
Interest expenseb 25 25 37 37 124
Interest incomec 8 3 11 12 34
Profit (loss) before tax 944 672 485 679 2,780
Income taxesd 321 228 165 231 945
Net income 623 443 320 448 1,835 <---Check: Net Income should equal 623, 443, 320, 448, and 1835.
Dividends 0 0 0 1,200 1,200
a: $5,040 imminent reduction of work-in-progress in June, plus $2,440 of abnormal inventory levels spread evenly over four months, plus monthly materials purchases.
b 6% annualized interest rate charged on outstanding debt.
c 2% annualized rate of return on the cash balance.
d 34% tax rate.

Table 4

Table 4: Sensitivity Analysis - The Impact of Sales Drop on September Cash Balance
Sales Increase / Dropa
-10% -5% 0% 5% 10%
Dividend 2400 (4,646) (3,281) (1,915) (550) 816
1800 (4,046) (2,681) (1,315) 50 1,416
1200 (3,446) (2,081) (715) 650 2,016
600 (2,846) (1,481) (115) 1,250 2,616
0 (2,246) (881) 485 1,850 3,216
a Sales drop is assumed to be evenly distributed over four months.