Create a horizontal trend analysis of the reported Compnet financial statements. This worksheet should list each financial statement (balance sheet and income statement in this order) on two Excel worksheets (one worksheet file). In column A, you would list each account on the statement (make sure you show the various categories of accounts). In column B and following, you would present the annual totals of each account with a monetary difference (absolute) column to compare one year to the next year and a percentage difference column. Therefore, for each of the two years reported, you will have Y1, Y2, Difference, and Percentage Difference columns. Your trend analysis must include three historical years. Therefore you need to compare Y1 to Y2 and then Y2 to Y3 (fiscal years 2012–2014). Part II: Summary After completing the computations on each of the three financial statements, provide a 1-page summary of the trends that you have observed. Describe the material trends by the category of accounts. Did that category of account increase, decrease, and if so by what amount and percentage. Remember to only discuss the major or material trends that you have observed.
auo_ms6010_compnet_balance_sheet_income_statement.xls
auo_ms6010_compnet_budget_template.xls
auo_ms6010_cost_volume_profit_analysis_template.xls
auo_ms6010_ratio_calculator.xls
Unformatted Attachment Preview
(in thousands)
############ ############ ############ ############
ASSETS
Current assets:
Cash and cash equivalents
Restricted cash—current portion
Accounts receivable, less allowance for doubtful
accounts
Inventories
Income taxes receivable
Deferred income taxes
Other current assets
Total current assets
Property, plant and equipment, net
Deferred income taxes—long-term portion
Prepayments and deferred charges
Restricted cash—long-term portion
Other intangible assets including Goodwill, net
Total assets
$97,550
$190
$102,054
$190
$108,950
$190
$98,050
$110
$49,650
$50,643
$56,501
$49,200
$106,900
$650
$9,213
$1,136
$265,289
$148,921
$59,352
$3,076
$110
$37,020
$513,768
$100,238
$987
$9,125
$973
$264,210
$167,860
$59,182
$2,941
$110
$37,090
$531,393
$115,559
$879
$9,300
$1,300
$292,679
$177,302
$59,522
$3,212
$110
$36,951
$569,775
$25,600
$8,448
$16,640
$1,890
$109
$52,687
$3,097
$64,030
$603
$142,050
$31,850
$10,511
$20,703
$1,980
$108
$65,151
$3,074
$41,228
$409
$152,791
$40,506
$13,037
$23,960
$2,001
$109
$79,613
$1,580
$59,509
$397
$156,900
$49,089
$348
$8,950
$645
$206,392
$226,820
$58,843
$2,670
$110
$13,214
$508,049
LIABILITIES AND STOCKHOLDERS’ EQUITY
Liabilities
Accounts payable
Accrued expenses
Accrued pension and postretirement benefits
Deferred revenue—current portion
Current maturities of long-term obligations
Total current liabilities
Long-term obligations (less current portion)
Deferred revenue (less current portion)
Non-current income taxes payable
Accrued pension and postretirement benefits
Total liabilities
Stockholders’ equity:
Common stock, $0.001 par value (40,000,000 shares
authorized)
Additional paid-in capital
Retained Earnings (Accumulated Loss)
Total stockholders’ equity
Total liabilities and stockholders’ equity
$262,467
$262,653
$297,999
$41,970
$17,890
$27,890
$2,500
$110
$90,360
$1,482
$36,070
$292
$169,870
$298,074
$11
$11
$11
$12
$217,631
$33,659
$220,926
$47,803
$223,220
$48,246
$227,650
$251,301
$513,768
$268,740
$531,393
$271,476
$569,475
($17,687)
$209,975
$508,049
##########
$107,890
$1,110
$75,510
$49,786
$698
$8,650
$2,000
$245,644
$228,075
$47,200
$3,753
––
$12,900
$537,572
$54,284
$21,780
$18,758
$2,500
$1,009
$98,331
$5,434
$39,250
$308
$170,802
$314,125
$13
$237,989
($14,855)
$223,147
$537,272
For the period ending:
Net Revenues
(dollars in 000s)
Computer component Manufacturing
Auto Compnents Manfacturing
Other markets
Total Business Unit Revenue
Other revenue
Net revenues
Cost of sales
Gross Profit
Selling, general and administrative
expenses
Research and Development
Impairment of Goodwill
Operating income (loss)
Interest income
Interest expense
Income (loss) before income taxes
Provision for (benefit from) income
taxes
Net income (loss)
############ ############ ############ ############
$135,023
$128,978
$42,002
$306,003
$5,600
$311,603
$210,000
$146,897
$81,025
$437,922
$10,600
$448,522
$248,790
$165,498
$86,995
$501,283
$15,066
$516,349
$286,675
$399,185
$475,041
$24,928
$49,337
$41,308
$19,008
$2,119
$31,845
$3,050
$37,053
$3,511
$3,802
$68
$309
$3,561
$14,442
$98
$396
$14,144
$744
$149
$450
$443
$188,906
$129,540
$53,237
$371,683
$12,033
$383,716
$396,844
($13,128)
$34,207
$3,120
$23,737
($74,192)
$138
$647
($74,701)
($8,768)
$3,561
$14,144
$443
($65,933)
############
$149,088
$109,098
$60,279
$318,465
$13,001
$331,466
$280,890
$50,576
$35,470
$4,105
$11,001
$98
$1,550
$9,549
$6,717
$2,832
The product line was purchased and you are assigned P&L responsibility for the Prime
Component Product Line.
The first year’s budget is based upon the variables outlined in M4: Assignment 2.
Budgeted Amounts
Sales
Sales price per unit
Project Sales Volume (in units)
Variable Cost per unit
direct Labor
Direct Materials
Direct sales expense
Fixed Cost
Product management team
Amortization of sales price
Interest expense
$35.00
1,500,000
$2.50
$6.00
$0.50
$5,000,000.00
$20,000,000.00
$3,000,000.00
Part 1 assignment:
Based upon this budget, project sales and profit for the product line.
At year end the company reviewed its results and determined the following results:
Actual Sales
Sales
Actual average Sales Price
Actual Sales (in units)
Variable Cost per unit
Actual direct labor
Actual direct materials
Actual sales expense
Fixed Cost
Product management team
Amortization of sales price
Interest expense
$35.50
1,350,000
$2.75
$5.25
$0.65
$5,000,000.00
$20,000,000.00
$3,000,000.00
Your assignment:
1. Prepare a sales budget variance report
2. Prepare a manufacturing budget report
:
Sales price per unit
$75.00 *
Variable Cost per unit
$67.00 *
Fixed Cost
Targeted Net Income
Calculated Volume
* inputted by user
$100,000.00 *
$0.00 *
12,500 calculated
(assume 0 if you want to calculate break
if you want to calculate breakeven)
Input
input data here:
Cash
Inventory
input data in yellow cells
Total Current Assets
Total Current Liabilities
Calculated Results
Current Ratio
#DIV/0!
Quick Ratio
#DIV/0!
Input Data here
2009
Balance Sheet
Year
Accounts Receivable
2008
2010
Inventory
Income Sheet
2009
Credit Sales
Cost of Goods Sold
2009
Input Data
2010
Calculated Results
2009
2010
AR Turnover
#DIV/0!
#DIV/0!
Days Sales Outstanding
#DIV/0!
#DIV/0!
Inventory Turnover
#DIV/0!
#DIV/0!
Days Inventory On Hand
#DIV/0!
#DIV/0!
Input
Total Assets
Total Liabilities
Total Equity
Long Term Debt
EBIT (Earnings before Interest and Taxes)
Interest Expense
Calculated Results
Debt ratio (Ttl Liabilities/Ttl Assets)
#DIV/0!
Debt to Equity (Ttl Liabilties/ Ttl Equity)
#DIV/0!
Interest Coverage (EBIT/ Interest Charges)
#DIV/0!
…
Purchase answer to see full
attachment
Recent Comments