CREATE 3 MORE YEARS OF CASH FLOW STATEMENT (7 HOURS)

profilesantth
week2final_1_1.xls
Home>Business & Finance homework help>CREATE 3 MORE YEARS OF CASH FLOW STATEMENT (7 HOURS)

Title Page

BalSheet

Huffman Trucking Balance Sheet (Unaudited) (In Thousands)
2011 2010 2009 2008 2007 2006 2005 2004 2003 2002
Current Assets
Cash & Cash Equivalents $89,664 $58,003 $48,336 $54,727 $57,349 $51,993 $38,893 $33,610 $30,071 $22,086
Accounts Receivable $51,869 $81,557 $59,698 $60,004 $55,959 $56,292 $57,441 $53,935 $46,043 $48,492
Prepaid Expenses & Supplies $6,267 $5,529 $9,118 $9,123 $7,787 $3,443 $3,343 $3,269 $3,174 $2,760
Total Current Assets $147,800 $145,089 $117,152 $123,854 $121,095 $111,728 $99,677 $90,814 $79,288 $73,338
Carrier Operating Property (at cost) $85,306 $81,461 $78,370 $78,275 $75,581 $73,024 $70,957 $69,009 $67,103 $65,166
Less: Allowance for Depreciation ($69,536) ($67,119) ($64,846) ($62,591) ($60,004) ($57,536) ($55,477) ($53,473) ($51,424) ($49,356)
Net Carrier Operating Property $15,770 $14,342 $13,524 $15,684 $15,577 $15,488 $15,480 $15,536 $15,679 $15,810
Assets of Discontinued Operations $7,516 $8,739 $10,196 $11,896 $13,879 $16,192 $18,891 $21,590 $24,288 $26,987
Goodwill (net) $49,852 $49,852 $57,670 $57,731 $57,767 $57,767 $53,977 $55,646 $56,782 $51,380
Other Assets $46,327 $37,306 $31,616 $26,347 $29,274 $26,613 $24,194 $23,263 $25,564 $25,822
Total Assets $267,265 $255,328 $230,158 $235,512 $237,592 $227,788 $212,219 $206,849 $201,601 $193,337
Need to factor in carrier operating property as per $10,000
the strategic initiatives memo in 2012
Current Liabilities
Accounts Payable 40843 45381 $47,266 $49,151 $51,836 $47,124 $39,936 $42,038 $30,462 $31,404
Salaries & Wages 37299 33014 $27,386 $32,283 $31,191 $29,753 $27,048 $24,313 $21,904 $20,664
Current Portion of Long-Term Debt 1752 1343 $1,483 $1,387 $1,370 $2,204 $2,514 $2,890 $3,200 $3,544
Freight & Casualty Claims Payable 10389 9697 $9,697 $9,746 $9,746 $9,746 $8,941 $8,356 $7,737 $7,369
Total Current Liabilities $90,283 $89,435 $85,832 $92,567 $94,143 $88,827 $78,439 $77,597 $63,303 $62,981
Long-Term Liabilities
Accrued Pension & Post Retirement Health Care 64058 58672 $59,241 $62,689 $61,280 $58,362 $52,721 $47,390 $42,694 $38,813
Long-Term Debt 7307 6562 $7,413 $6,936 $6,850 $13,431 $15,318 $17,607 $20,497 $22,697
Total Long Term Liabilities $71,365 $65,234 $66,654 $69,625 $68,130 $71,793 $68,039 $64,997 $63,191 $61,510
Shareholders' Equity
Common Stock ($1 par value Authorized: 20,000,000 shares) 3.882 3.882 $3.882 $3.882 $3.882 $3.882 $3.882 $3.882 $3.882 $3.882
Treasury Shares -1.952 -1.952 ($1.952) ($1.952) ($1.952) ($1.952) ($1.952) ($1.952) ($1.952) ($1.952)
Retained Earnings 105615 100657 $77,670 $73,318 $75,317 $67,166 $65,739 $64,253 $75,105 $68,844
Total Shareholders' Equity $105,617 $100,659 $77,672 $73,320 $75,319 $67,168 $65,741 $64,255 $75,107 $68,846
Total Liabilities and Shareholders' Equity $267,265 $255,328 $230,158 $235,512 $237,592 $227,788 $212,219 $206,849 $201,601 $193,337
Copyright 2007, 2012 © Apollo Group, Inc. All right reserved

IncomeStmt

Huffman Trucking Income Statement (Unaudited) (In Thousands)
2-yr Average Major Assumptions
2013 2012 run rate 2011 As a % of Sales 2010 As a % of Sales 2009 2008 2007 2006 2005 2004 2003 2002
Existing Revenue $1,293,882 $1,198,039 $1,109,295 $969,240 $842,817 $1,021,348 $932,653 $879,944 $807,288 $685,432 $603,852 $555,778 8% increase in existing revenue
Incremental Consulting Revenue $4,500 $3,500 memo info
New Warehousing Revenue $30,000 $10,000 memo info
Total Revenue $1,328,382 $1,211,539
Operating Expenses
Salaries, Wages & Benefits $515,197 $469,881 37% $406,191 37% $367,993 38% $328,624 39% $387,391 $374,291 $353,739 $330,597 $293,212 $266,556 $251,468 4% inflation, plus used 2-yr run rate to estimate expenses
Fuel Expense $382,994 $349,306 28% $318,737 29% $258,904 27% $206,052 24% $349,952 $256,488 $217,363 $192,357 $139,389 $111,067 $104,780 4% inflation, plus used 2-yr run rate to estimate expenses
Operating Supplies and Expenses $148,728 $135,646 11% $117,670 11% $105,875 11% $144,387 17% $146,414 $136,468 $152,318 $136,319 $121,442 $107,471 $95,956 4% inflation, plus used 2-yr run rate to estimate expenses
Purchased Transportation $167,444 $152,715 12% $138,140 12% $114,250 12% $95,209 11% $103,901 $94,455 $89,957 $82,529 $75,026 $64,400 $54,392 4% inflation, plus used 2-yr run rate to estimate expenses
Operating Taxes & Licenses $24,504 $22,349 2% $19,033 2% $17,753 2% $17,665 2% $18,993 $19,088 $18,613 $17,989 $16,170 $14,700 $14,272 4% inflation, plus used 2-yr run rate to estimate expenses
Insurance & Claims $16,373 $14,933 1% $11,995 1% $12,493 1% $11,676 1% $13,918 $14,202 $13,526 $13,006 $11,587 $10,534 $9,405 4% inflation, plus used 2-yr run rate to estimate expenses
Provision for Depreciation $3,850 $3,511 0% $3,009 0% $2,773 0% $2,618 0% $2,899 $2,803 $2,726 $2,738 $2,663 $2,590 $2,598 4% inflation, plus used 2-yr run rate to estimate expenses
Total Operating Expenses $1,259,089 $1,148,341 96% $1,014,775 91% $880,041 91% $806,231 83% $1,023,468 $897,795 $848,242 $775,535 $659,489 $577,318 $532,871
Operating Income from Continuing Operations $69,293 $63,198 9% $94,520 9% $89,199 9% $36,586 4% ($2,120) $34,858 $31,702 $31,753 $25,943 $26,534 $22,907
Interest Expense $805 $734 0% $466 0% $768 0% $1,044 0% $1,018 $988 $790 $901 $1,036 $1,147 $1,111
Tax Expense $25,576 $23,326 $34,887 3% $32,923 3% $13,504 1% ($782) $12,866 $11,701 $12,050 $10,917 $10,879 $9,163
Net Income $8,412 $25,637 2% $59,167 5% $55,508 6% $22,038 2% ($2,356) $21,004 $19,211 $18,802 $13,990 $14,508 $12,633
Copyright 2007, 2012 © Apollo Group, Inc. All rights reserved

Marketing Budget

Marketing Department Job Posting Positions in Marketing
Marketing/Communications (headquarters)
Operating Budget - FY12 Annual Salary Reference
NOTE: NOT IN THOUSANDS Market Research Analyst
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Marketing Analyst
Product Analyst
Compensation Marketing Specialist
Wages - existing headcount $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ - Systems Analyst
Wages - open headcount (see below list) $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - Total Open Headcount Positions
Benefits $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ -
Commission $ -
Car Allowance $ - Assumptions
Total $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 Marketing headcount represents approx 8% of total company headcount Estimate used from Starbucks experience
8% Assume an average wage rate of $50,000 for marketing dept
Rent $ -
Utilities $ -
Insurance and claims $ -
Bank Charges $ -
Professional Fees (legal, accounting, etc.) $ -
Travel and Entertainment $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ - T&E represents approx 1.12% of revenue for a mid-size business https://www.jpmorgan.com/cm/BlobServer?blobkey=id&blobwhere=1320534215431&blobheader=application%2Fpdf&blobcol=urldata&blobtable=MungoBlobs&blobheadername1=Content-disposition&blobheadervalue1=attachment;filename=CorpCardBenchmarkingSurvey10-11.pdf.
Fuel Expense $ -
Postage $ -
Subscriptions $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ - Guestimate
Seminars $ 1,000,000 $ 1,000,000 $ 1,000,000 $ 1,000,000 $ - Assume quarterly seminars and company wide meetings to launch initiatives with $2000 spent on each marketing employee
Contracts (Advertising/ Consulting) $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ - To expand the business with the new initiatives, a higher percentage of marketing dollars are required, close to 15% http://www.inc.com/guides/2010/09/how-to-build-your-marketing-budget.html
Office Supplies $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ - Average Office Supplies Expense per person is $19 http://smallbusiness.chron.com/average-cost-per-month-office-supplies-12771.html
Depreciation $ -
Operating Tax $ -
Total $ 161,403,704 $ 161,403,704 $ 162,403,704 $ 161,403,704 $ 161,403,704 $ 162,403,704 $ 161,403,704 $ 161,403,704 $ 162,403,704 $ 161,403,704 $ 161,403,704 $ 162,403,704
Not to Exceed 15% of total company sales 13.3%

Operations Budget

Operations Department Job Posting Positions in Operations
Operations (headquarters)
Operating Budget - FY12 Annual Salary Reference
NOTE: NOT IN THOUSANDS
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Compensation
Wages - existing headcount $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ -
Wages - open headcount (see below list) $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - Total Open Headcount Positions $ - 0
Benefits $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ -
Commission $ -
Car Allowance $ - Assumptions
Total $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ -
8% a. 80% of wage benefits is variable and 20% is fixed.
Rent $ - b. 100% of fuel expenses, purchased transportation, and operating supplies is variable.
Utilities $ - c. 100% of operating taxes is fixed.
Insurance and claims $ - d. 20% of insurance and claims is fixed; the balance is variable.
Bank Charges $ - e. Assume depreciation, even with new expenditures, is fixed as the retirement of written-off assets, equaling new equipment.
Professional Fees (legal, accounting, etc.) $ - f. In the first year, add $10 million of inflation, space rental, and operating costs at 25% of revenues from the new initiative.
Travel and Entertainment $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ - g. In the second year, add $10 million space rental, with inflation at the same variable percentage of sales.
Postage $ - h. In the third year, add $7.5 million of the variable percentage of sales.
Fuel Expense $ -
Subscriptions $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ -
Seminars $ 1,000,000 $ 1,000,000 $ 1,000,000 $ 1,000,000 $ -
Contracts (Advertising/ Consulting) $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ -
Office Supplies $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ -
Depreciation $ -
Operating Tax $ -
Total $ 161,403,704 $ 161,403,704 $ 162,403,704 $ 161,403,704 $ 161,403,704 $ 162,403,704 $ 161,403,704 $ 161,403,704 $ 162,403,704 $ 161,403,704 $ 161,403,704 $ 162,403,704
Not to Exceed 15% of total company sales 13.3%

HR Budget

HR Department Job Posting Positions in HR
HR (headquarters)
Operating Budget - FY12 Annual Salary Reference
NOTE: NOT IN THOUSANDS
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Compensation
Wages - existing headcount $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ 32,000,000 $ -
Wages - open headcount (see below list) $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - Total Open Headcount Positions $ - 0
Benefits $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ 7,200,000 $ -
Commission $ -
Car Allowance $ - Assumptions
Total $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000 $ 39,200,000
8% a. The human resources budget will not change substantially aside from added hiring, recruiting, training, and drug testing fees. Assume 10% of expenses is fixed; the balance is variable with volume.
Rent $ -
Utilities $ -
Insurance and claims $ -
Bank Charges $ -
Professional Fees (legal, accounting, etc.) $ -
Travel and Entertainment $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ 1,032,448 $ -
Postage $ -
Fuel Expense $ -
Subscriptions $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ 2,500 $ -
Seminars $ 1,000,000 $ 1,000,000 $ 1,000,000 $ 1,000,000 $ -
Contracts (Advertising/ Consulting) $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ 121,153,860 $ -
Office Supplies $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ 14,896 $ -
Depreciation $ -
Operating Tax $ -
Total $ 161,403,704 $ 161,403,704 $ 162,403,704 $ 161,403,704 $ 161,403,704 $ 162,403,704 $ 161,403,704 $ 161,403,704 $ 162,403,704 $ 161,403,704 $ 161,403,704 $ 162,403,704
Not to Exceed 15% of total company sales 13.3%