ACCOUNT

profileMohsin
proforma_1.xls
Home>Business & Finance homework help>Accounting homework help>ACCOUNT

Fin. Statmts

This assignment requires you to develop three years of pro-forma financial statement information in a contracting environment.
1. The financial statements should integrate and balance
2. The financial statements are developed using information from the follow-on tabs.
3. Try to develop the statements using all the relational information provided.
4. If you become stuck, you may manually plug numbers into the financial statements in order to create a set of working financial statements (see point 7 for the master plug)
5. Grading will be based upon completeness and accuracy.
6. The Dec. 31, 20Y6 is complete as given, reflecting owner contributions of $10 million on the last day of 20Y6.
7. A "bail out plug numnber" line is provided on the balance sheet. If you are unable to get the financial statements to articulate you may use the "plug option" to complete the assignment. Just manually input a number to get it to balance. It is possible to do the assignment without the plug.
Sentinel Systems
Balance Sheet
December 31
20Y6 20Y7 20Y8 20Y9
Assets
Current assets
Cash $ 10,000,000
Trade receivables, net - 0
Materials inventory
Work in process inventory - 0
Total current assets $ 10,000,000 $ - 0 $ - 0 $ - 0
Property, plant, and equipment (at cost) $ -
Less: accumulated depreciation
Net property, plant, and equipment - 0 $ - 0 $ - 0 $ - 0
Total assets $ 10,000,000 $ - $ - $ -
Liabilities and Stockholders' Equity
Current liabilities
Accounts payable $ -
Customer advances in excesss of costs - 0
Current portion of long-term debt - 0 0 0
Total current liabilites - 0 $ - $ - $ -
Long-term debt - 0
Total liabiltiies $ - $ - $ - $ -
Stockholders' Equity - 0 - 0
Common Stock ($1.00 par value) $ 100,000
Additional Paid in Capital 9,900,000
Retained Earnings - 0
Total stockholders' equity $ 10,000,000 $ - $ - $ -
Bail out plug number*
Total liabilities and stockholders' equity $ 10,000,000 $ - $ - $ - * The bailout plug nmber is a place where you can manually insert a number to get the balance sheet to balance. It means you aren't able to get the numbers to work, so you
elect to use this option to complete the assignment. I do expect to see the balance sheets balance, so use this option if you need to.
Sentinel Systems
Income Statement
For the Year Ended, December 31
20Y7 20Y8 20Y9
Net Sales
Products
Services
Total Net sales
Cost of sales
Products
Services
Total cost of sales
Operating income
Interest expense
Earnings from continuing operations
Income tax expense*
Net income
Earnings per share
* Income tax assumed at a 25% rate on earnings from continuing operations
Sentinel Systems
Statement of Cash Flows
For the Year Ended, December 31
20Y7 20Y8 20Y9
Cash flows from operating activities
Collections on billings
Payments for materials
Payments for labor
Payments for interest
Payments for taxes
Cash provided by operating activities
Cash flows from investing activities
Capital Expenditures
Net cash used in investing activities $ - $ - $ -
Cash flows from financing activiites
Dividends paid
Retirement of long-term debt
Issuance of long term debt
Issuance of common stock
Net cash provided (used) by financing activities
Net change in cash
Financial Performance:
20Y7 20Y8 20Y9
ROA
ROE
ROCE
ROA calculations: Net Income/Average Total Assets
ROE calculation: Operating Income/Average Stockholder's Equity
ROCE calculation: Operating Income/Average(PPE + Net Working Capital - Cash)

Contract Revenues and Costs

Information about contracts:
1. Each contract is with a different customer.
2. Revenue is earned for service contracts using the cost-to-cost method
3. The cost of service contracts is the sum of the cost of software engineering hours, material costs, and overhead costs.
4. Revenue is earned and costs incurred for product contracts using the units completed method
5. The cost per unit of a product contract is the sum of the labor, materials, and overhead for each unit produced.
Service Contracts
Service contract estimates:
S-100 S-200 S-300
Total estimated revenue $ 12,000,000 $ 15,000,000 $ 16,000,000
Total estimated costs $ 10,000,000 $ 12,000,000 $ 13,000,000
Solution: Costs
Service contract Materials ($'s) Overhead (labor and Investing tabs)
labor (labor tab) (materials tab) Service Support Corporate Support Service PPE Corporate PPE Total Cost Total service contract costs (transposed)
Contract S-100 20Y7 20Y8 20Y9
20Y7 $ 292,500 $ 190,000 $ 4,500 S-100
20Y8 $ - 0 $ 220,000 $ 5,000 S-200
20Y9 $ - 0 $ 250,000 $ 8,000 S-300
Total
Contract S-200
20Y7 $ 325,000 $ - 0 $ - 0 Revenue earned (cost to cost method)
20Y8 $ 422,500 $ 300,000 $ 6,500 20Y7 20Y8 20Y9
20Y9 $ - 0 $ 380,000 $ 9,200 S-100
S-200
Contract S-300 S-300
20Y7 $ 520,000 $ - 0 $ - 0 Total
20Y8 $ 598,000 $ - 0 $ - 0
20Y9 $ 780,000 $ 400,000 $ 12,000
Product Contracts
Solution space: 20Y7 Hint: =INT function is useful here
Number of units of effort Production labor Material cost Overhead per unit (labor and investing tabs) Total cost Total 20Y7 20Y7 COGS 20Y7 Revenue 12/31/20Y7
20Y7 20Y8 20Y9 cost per unit per unit Production Support Company Support Production PPE Company PPE per unit Cost Produced WIP
Contract P-50 3.5 5.5 6 Contract P-50 $ 2,100,000
Contract P-60 4.25 5.75 3.25 Contract P-60 $ 2,975,000
Contract P-70 3 4 5.5 Contract P-70 $ 1,050,000
Contract P-80 6.75 8 Contract P-80 $ - 0
Contract P-90 5.5 Contract P-90 $ - 0
Total
Ck Fig. $ - 0 $ - 0
Additional product contract information: Equal
Contract sales value per unit 20Y8
Contract P-50 $ 600,000 Production labor Material cost Overhead per unit (labor and investing tabs) Total cost Total 20Y8 20Y8 COGS 20Y8 Revenue 12/31/20Y8
Contract P-60 $ 700,000 cost per unit per unit Production Support Company Support Production PPE Company PPE per unit Cost Produced WIP
Contract P-70 $ 350,000 Contract P-50 $ 3,300,000
Contract P-80 $ 400,000 Contract P-60 $ 4,025,000
Contract P-90 $ 150,000 Contract P-70 $ 1,400,000
Contract P-80 $ 2,700,000
Contract P-90 $ - 0
Total
Ck Fig. $ - 0 $ - 0
Equal
20Y9
Production labor Material cost Overhead per unit (labor and investing tabs) Total cost Total 20Y9 20Y9 COGS 20Y9 Revenue 12/31/20Y9
cost per unit per unit Production Support Company Support Production PPE Company PPE per unit Cost Produced WIP
Contract P-50 $ 3,600,000
Contract P-60 $ 2,275,000
Contract P-70 $ 1,925,000
Contract P-80 $ 3,200,000
Contract P-90 $ 825,000
Total
Ck Fig. $ - 0 $ - 0
Equal

Labor

Information on labor:
1.Production labor is associated with product contracts
2.Software engineers are associated with service contracts
3.Production support is overhead to product contracts allocated on the basis of production labor hours (annually), using a Production Support rate. All rates are used in the contracts tab.
4.Service support is overhead to service contracts allocated on the basis of software engineering hours (annually), using a service support rate.
5. Company support is overhead to all contract allocated on the basis of all labor hours charged to product and service contracts (annually), using a company support rate.
6. We will assume labor rate, FTE support levels, and salaries are stable across the three years.
7. Assume there is no accrued labor liabilities on the financial statement date (Sentinel has paid for all labor that has been provided)
8. Assume labor is provided uniformly (ratably) for partially completed product.
Production Labor Rate per hour
Class A $28.00
Software Engineers Rate per hour
Class J $65.00
Solution Space
Solution space:
Production Support Number FTE's Salary Prod. Support Salary Cost 20Y7 20Y8 20Y9
Maintenance 5 $42,000 Maintenance 210,000 420,000 630,000
Material control 4 $60,000 Material control 240,000 480,000 720,000
Production control 4 $55,000 Production control 220,000 440,000 660,000
Production management 8 $60,000 Production management 480,000 960,000 1,440,000
Total Prod. Support 1,150,000 2,300,000 3,450,000 6,900,000
Solution space:
Service Support Number FTE's Salary Serv. Support Salary Cost 20Y7 20Y8 20Y9
Engineering management 4 $110,000 Engineering management 440,000 880,000 1,320,000
Engineering support staff 12 $38,000 Engineering support staff 456,000 912,000 1,368,000
Total Service Support 896,000 1,792,000 2,688,000 5,376,000
Solution space:
Company Support Number FTE's Salary Company Support Salary Cost 20Y7 20Y8 20Y9
C-Suite 3 $500,000 C-Suite $1,500,000 $3,000,000 $4,500,000
Human Resources 3 $52,000 Human Resources 156,000 312,000 468,000
Accounting 5 $58,000 Accounting 290,000 580,000 870,000
Information Systems 10 $72,000 Information Systems 720,000 1,440,000 2,160,000
Business Development 3 $95,000 Business Development 285,000 570,000 855,000
Process improvement 3 $68,000 Process improvement 204,000 408,000 612,000
Total Company Support $3,155,000 $6,310,000 $9,465,000
Production Contracts Solution space: Number of units of effort
Production hours per unit (routings) Production hours 20Y7 20Y8 20Y9 20Y7 20Y8 20Y9
Contract P-50 700 Contract P-50 2,450 3,850 4,200 Contract P-50 3.5 5.5 6
Contract P-60 900 Contract P-60 3,825 5,175 2,925 Contract P-60 4.25 5.75 3.25
Contract P-70 400 Contract P-70 1,200 1,600 2,200 Contract P-70 3 4 5.5
Contract P-80 800 Contract P-80 - 0 5,400 6,400 Contract P-80 6.75 8
Contract P-90 300 Contract P-90 - 0 - 0 1,650 Contract P-90 5.5
Total Class A hours 7,475 16,025 17,375
Service Contracts
Software Engineering Hours Direct Labor Cost :J Class 20Y7 20Y8 20Y9
Class J Contract S-100 $ 292,500 $ 325,000 $ 520,000
Contract S-100 Contract S-200 $ - 0 $ 422,500 $ 598,000
20Y7 4,500 Contract S-300 $ - 0 $ - 0 $ 780,000
20Y8 5,000 Total Class J labor cost $ 292,500 $ 747,500 $ 1,898,000
20Y9 8,000
Contract S-200 Service Contract Hours
20Y7 Service hours :J Class 20Y7 20Y8 20Y9
20Y8 6,500 Contract S-100 4,500 5,000 8,000
20Y9 9,200 Contract S-200 - 0 6,500 9,200
Contract S-300 - 0 - 0 12,000
Contract S-300 Total Class J labor hours 4,500 11,500 29,200
20Y7
20Y8
20Y9 12,000 Direct labor cost : A class 20Y7 20Y8 20Y9
Contract P-50 $68,600 $107,800 $117,600
Contract P-60 $107,100 $144,900 $81,900
Contract P-70 $33,600 $44,800 $61,600
Contract P-80 $0 $151,200 $179,200
Contract P-90 $0 $0 $46,200
Total Class A labor cost $209,300 $448,700 $486,500
20Y7 20Y8 20Y9
Service Support Rate $ 199.11 $ 155.83 $ 92.05
20Y7 20Y8 20Y9 3.Production support is overhead to product contracts allocated on the basis of production labor hours (annually), using a Production Support rate. All rates are used in the contracts tab.
Production Support Rate $ 153.85 $ 143.53 $ 198.56
20Y7 20Y8 20Y9
Company Support Rate $ 263.47 $ 229.25 $ 203.22
20Y7 20Y8 20Y9
Total payments for labor (SCF) $ 501,800 $ 1,196,200 $ 2,384,500

Materials

Additional information on materials:
1. Product contracts consume materials on the basis of units of completed or partial production.
2. Materials consumed for service contracts are as given below.
3. Assume the material consumed for partial production is uniform throughout the completion process per unit.
Service contracts
Materials consumed:
20Y7 20Y8 20Y9
Contract S-100 $ 190,000 $ 220,000 $ 250,000
Contract S-200 $ 300,000 $ 380,000
Contract S-300 $ 400,000
total $ 190,000 $ 520,000 $ 1,030,000
Product Contracts
Number of units of effort (given) Total Production material costs (units x Material cost per unit)
Materials per unit (BOM): 20Y7 20Y8 20Y9 20Y7 20Y8 20Y9
Contract P-50 $ 56,000 Contract P-50 3.5 5.5 6 Contract P-50 $ 196,000 $ 308,000 $ 336,000
Contract P-60 $ 86,000 Contract P-60 4.25 5.75 3.25 Contract P-60 $ 365,500 $ 494,500 $ 279,500
Contract P-70 $ 32,000 Contract P-70 3 4 5.5 Contract P-70 $ 96,000 $ 128,000 $ 176,000
Contract P-80 $ 45,000 Contract P-80 0 6.75 8 Contract P-80 $ - 0 $ 303,750 $ 360,000
Contract P-90 $ 18,000 Contract P-90 0 0 5.5 Contract P-90 $ - 0 $ - 0 $ 99,000
total $ 657,500 $ 1,234,250 $ 1,250,500
Material purchases (given)
20Y7 20Y8 20Y9
Materials purchased (on account) $ 1,101,750 $ 2,105,100 $ 1,824,400
Payments on account (given)
20Y7 20Y8 20Y9
Payments on account (SCF) $ 881,400 $ 2,315,610 $ 1,641,960
December 31,
End of period 20Y7 20Y8 20Y9
Accounts Payable balance $ 220,350 $ 9,840 $ 192,280
December 31,
End of period 20Y7 20Y8 20Y9
Materials inventory balance $ 254,250 $ 605,100 $ 149,000

Financing

Bank Loans: Solution space: 20Y7 20Y8 20Y9
Issue Date Principal Interest rate Term Interest expense $ 200,000 $ 200,000 $ 140,000
Bank Loan No. 1 1/1/20Y7 $ 4,000,000 5.0% 2 years (retired Dec. 31, 20Y8) Issuance (SCF) $ 7,000,000 $ 5,000,000 $ 2,000,000
Bank Loan No. 2 12/31/20Y7 $ 3,000,000 6.0% 5 years Retirement (SCF) $ 4,000,000
Bank Loan No. 3 6/30/20Y8 $ 5,000,000 5.5% 7 years
Bank Loan No. 4 12/31/20Y9 $ 2,000,000 7.0% 10 years
Stock issuance: Solution space: 20Y7 20Y8 20Y9
Date Number of shares Price per share Stock issuance: - 0 - 0 5,500,000
Issued common stock 1/1/20Y9 25,000 $ 220.00 Par value (SCF) 1 1 1
Paid in captial in excess of par (SCF)
Total
Dividends paid:
Date paid Dividend per share
20Y8 Dividend 11/01/20Y8 $25 Solution space: 20Y7 20Y8 20Y9
20Y9 Dividend 11/01/20Y9 $30 Shares outstanding 100,000 100,000 125,000
Solution space: 20Y7 20Y8 20Y9
Note: Dividends are deducted from retained earnings balance and are not on the income statement Dividends paid (SCF) $ 2,500,000 $ 3,750,000

Investing

Information about investing activities:
1. This tab provides information to determine the depreciation overhead rates that allocate depreciation to the contracts (contract tab), and provide balance sheet information for PP&E.
2. General plant and equipment annual depreciation is allocated to contracts based on total production and software engineering labor hours (annually), using a General PPE overhead rate.
3. Production plant and equipment depreciation is overhead allocated to product contracts on the basis of total annual production direct labors, using a Production PPE overhead rate.
4. Service equipment depreciation is overhead allocated to service contracts on the basis of total annual service direct labor hours, using a Service PPE overhead rate.
5. Depreciation per time period is determined to the nearest whole month.
Company: Solution space:
Plant and Equipment Acquisition Cost Placed into Service Life in Years Company Depreciation Expense 20Y7 20Y8 20Y9 Company Investment 20Y7 20Y8 20Y9
Land (non-depreciable) $ 1,500,000 1/5/20Y7 N/A Land $ 1,500,000 $ 1,500,000 $ 1,500,000 Land $ 1,500,000 $ 3,000,000 $ 4,500,000
Building $ 3,000,000 1/5/20Y7 25 Building $ 120,000 $ 64,800 $ 62,592 Building 3,000,000 $ 6,000,000 $ 9,000,000
General Information Systems $ 1,000,000 1/5/20Y7 8 General I/S $ 125,000 $ 23,725 $ 10,790 General I/S 1,000,000 $ 2,000,000 $ 3,000,000
Furniture and fixtures $ 500,000 1/5/20Y7 10 Furn and Fixtures $ 50,000 $ 7,373 $ 1,816 Furn and Fixtures 500,000 $ 1,000,000 $ 1,500,000
Sub-total $ 1,795,000 $ 1,595,898 $ 1,575,198 Sub-total $ 6,000,000 $ 12,000,000 $ 18,000,000
Production: Solution space:
Equipment Acquisition Cost Placed into Service Life in Years Production Depreciation Expense 20Y7 20Y8 20Y9 Production Investment 20Y7 20Y8 20Y9
Production equipment class A $ 4,200,000 1/5/20Y7 6 Prod. Class A $ 700,000 $ - 0 $ - 0 Prod. Class A $ 4,200,000
Production equipment class B $ 2,400,000 1/3/20Y8 6 Prod. Class B $ - 0 $ 400,000 $ - 0 Prod. Class B $ 2,400,000
Production equipment class C $ 3,000,000 1/4/20Y9 8 Prod. Class C $ - 0 $ - 0 $ 375,000 Prod. Class C $ 3,000,000
Sub-total $ 700,000 $ 400,000 $ 375,000 Sub-total $ 4,200,000 $ 2,400,000 $ 3,000,000
Services: Solution space:
Equipment Acquisition Cost Placed into Service Life in Years Service Depreciation Expense 20Y7 20Y8 20Y9 Service Investment 20Y7 20Y8 20Y9
Test equipment A $ 2,800,000 1/5/20Y7 5 Test Eq A $ 560,000 $ - 0 $ - 0 Test Eq A $ 2,800,000
Test equipment B $ 3,500,000 1/4/20Y9 4 Test Eq B $ 1,000,000 $ - 0 Test Eq B $ 3,500,000
Technology equipment $ 4,000,000 1/3/20Y8 4 Technology EQ $ - 0 $ 1,000,000 Technology EQ $ 4,000,000
Sub-total $ 560,000 $ 1,000,000 $ 1,000,000 Sub-total $ 2,800,000 $ 4,000,000 $ 3,500,000
Total Depn. Expense $ 3,055,000 $ 2,995,898 $ 2,950,198 Total Investment (SCF) $ 13,000,000 $ 18,400,000 $ 24,500,000
20Y7 20Y8 20Y9 End of period 20Y7 20Y8 20Y9
Service direct hours (from labor tab) 4,500 11,500 29,200 Property, plant, and Equipment cost balance
Production direct hours (from labor tab) 7,475 16,025 17,375
Total 11,975 27,525 46,575
20Y7 20Y8 20Y9
Service PPE Overhead rate 124.44 36.33 21.47
Production PPE Overhead rate 58.46 14.53 8.05
General PPE Overhead rate 255.11 108.84 63.34
End of period 20Y7 20Y8 20Y9
Accumulated Depreciation balance $ 3,055,000 $ 6,050,898 $ 9,001,095

Collections

Use this worksheet to determine the Accounts Receivable and Customer Advances (services liability) balances for the end of period balance sheet date.
The revenue comes from the contract revenue tab.
Service Contracts
Solution:
Given information 20Y7 20Y8 20Y9 20Y7 20Y8 20Y9
Contract S-100 Contract S-100
Collection $ - 0 $ - 0 $ - 0 Revenue
Collected
Contract S-200 Accounts Receivable or Customer Advances
Collections $ - 0 $ - 0
Contract S-200
Contract S-300 Revenue
Collections $ - 0 Collected
Accounts Receivable or Customer Advances
Product Contracts Contract S-300
Revenue
Given information 20Y7 20Y8 20Y9 Collected
Contract P-50 Accounts Receivable or Services Liablity
Collections $ - 0 $ - 0 $ - 0
Contract P-50
Contract P-60 Revenue
Collections $ - 0 $ - 0 $ - 0 Collected
Accounts Receivable or Customer Advances
Contract P-70
Collections $ - 0 $ - 0 $ - 0 Contract P-60
Revenue
Contract P-80 Collected
Collections $ - 0 $ - 0 Accounts Receivable or Customer Advances
Contract P-90 Contract P-70
Collections $ - 0 Revenue
Collected
Accounts Receivable or Customer Advances
The information above is formula driven information that links back to Contract P-80
the Contract revenues determined in the contract tab. The contract revenues Revenue
must be completed first, before working on the collections in this tab. Collected
Accounts Receivable or Customer Advances
Contract P-90
Revenue
Collected
Accounts Receivable or Customer Advances
Accounts Receivable, Dec. 31
Service Liability year, Dec. 31
Total collections (SCF)
Note: Accounts receivable and customer advances are end of period balances