Excel Assignment

Balance Sheet

BALANCE SHEET

UltraCorp, Inc.

Sales Growth Rate:

0.05

Years 1-7

Sales

Forecasted Sales

Assets

69100

72400

76500

80000

cpiercy: Enter a formula that calculates a Forecasted Sales value by multiplying the previous years Sales by (1 plus the Sales Growht Rate). Use appropriate relative and absolute cell references so that your formula may be copied to the remaining yearly forecasts.

Year 1 - 6/1/04

Year 2 - 6/1/05

Year 3 - 6/1/06

Year 4 - 6/1/07

Year 5 - 6/1/08

Year 6 - 6/1/09

Year 7 - 6/1/10

Current Assets:

% of Sales

Cash

12417

11652

9923

10460

0.13

Accounts Receivable, net

1109

1107

1157

1351

0.017

Merchandise Inventory

828

775

765

873

0.01

Prepaid Expenses

548

580

595

624

0.008

Notes Receivable

194

175

181

129

0.0015

Total Current Assets

win200: Use the appropriate function to calculate the total for the Current Assets

win200: Copy your formula from column L in this row to here

win200: Copy your formula from column D in this row to here

cpiercy: Enter a formula that calculates this value as a percentage of the Forecasted Sales for this year. The percentage of Sales for this row may be found in column Q. Use appropriate relative and absolute cell references so that your formula may be copied to all of the remaining cells in this section.

win200: Copy your formula from column L in this row to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from column D in this row to here

Fixed Assets:

Investments

10877

11661

12328

12167

0.15

Property and Equipment, Net

3921

3890

3805

3643

0.04

Other Fixed Assets

3595

3789

3689

3817

0.05

Total Fixed Assets

win200: Use the appropriate function to calculate the total for the Fixed Assets

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from Cell L9 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

Other Assets:

Goodwill

3709

3717

3813

4043

0.05

Total Other Assets

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L9 to here

cpiercy: Enter a formula that calculates this value as a percentage of the Forecasted Sales for this year. The percentage of Sales for this row may be found in column Q. Use appropriate relative and absolute cell references so that your formula may be copied to all of the remaining cells in this section.

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L17 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

Total Assets

win200: Enter a formula that will calculate Total Assets by adding the Total Current Assets, Total Fixed Assets, and the Total Other Assets

win200: Copy your formula from Cell L17 to here

win200: Copy your formula from Cell L17 to here

win200: Copy your formula from Cell L17 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L17 to here

win200: Copy your formula from Cell L17 to here

win200: Copy your formula from Cell L17 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L17 to here

cpiercy: Enter a formula that calculates this value as a percentage of the Forecasted Sales for this year. The percentage of Sales for this row may be found in column Q. Use appropriate relative and absolute cell references so that your formula may be copied to all of the remaining cells in this section.

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L23 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L23 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

Liabilities and Capital

Current Liabilities:

Accounts Payable

540

518

480

594

0.007

Sales Taxes Payable

336

322

304

295

0.004

Payroll Taxes Payable

2424

2280

2220

2162

0.03

Income Taxes Payable

508

773

544

739

0.01

Accrued Wages Payable

1083

1251

1293

1470

0.02

Unearned Revenues

2980

2920

2954

3034

0.035

Total Current Liabilities

win200: Use the appropriate function to calculate the total for the Current Liabilities

cpiercy: Enter a formula that calculates this value as a percentage of the Forecasted Sales for this year. The percentage of Sales for this row may be found in column Q. Use appropriate relative and absolute cell references so that your formula may be copied to all of the remaining cells in this section.

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from column D in this row to here

Long-Term Liabilities:

671

620

605

612

0.008

Long-Term Notes Payable

100

197

200

162

0.002

Total Long-Term Liabilities

win200: Use the appropriate function to calculate the total for the Long-Term Liabilities

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

Total Liabilities

win200: Enter a formula that will calculate Total Liabilities by adding the Total Current Liabilities and the Total Long-Term Liabilities

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from column D in this row to here

cpiercy: Enter a formula that calculates this value as a percentage of the Forecasted Sales for this year. The percentage of Sales for this row may be found in column Q. Use appropriate relative and absolute cell references so that your formula may be copied to all of the remaining cells in this section.

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L32 to here

win200: Copy your formula from Cell L40 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from Cell L40 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

Capital:

Owner's Equity

28546

28455

27645

28029

Investment

10

10

11

10

10

10

10

Total Capital

win200: Use the appropriate function to calculate the total for the Capital

win200: Copy your formula from Cell L40 to here

win200: Copy your formula from Cell L40 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

Total Liabilities and Capital

win200: Insert a formula that will add the Total Liabilities and Capital here.

win200: Copy your formula from Cell L40 to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

cpiercy: Enter a fomula that will calculate the Owner's Equity as the Total Liabilities and Capital less than the Investment and Total Capital for this year.

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column L in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column D in this row to here

win200: Copy your formula from column L in this row to here

win200: Copy your formula from column D in this row to here

cpiercy: Enter a formula here that will set the Total Liabilities and Capital for this year equal to the Total Assets for this year.

win200: Copy your formula from column D in this row to here

cpiercy: Enter a formula here that will set the Total Liabilities and Capital for this year equal to the Total Assets for this year.

cpiercy: Enter a formula here that will set the Total Liabilities and Capital for this year equal to the Total Assets for this year.