EXC_1_MC_Balance_SheetxlsxBalance_SheetBALANCE_SHEETUltraCorp

EXC_1_MC_Balance_Sheet.xlsx

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 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 column D 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

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

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

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 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 Cell L17 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 Cell L23 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

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 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 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 column D in this row 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 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

Capital:

Owner's Equity

28546

28455

27645

28029

Investment

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 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

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 L 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.

Excel Tutorial 1 Mini-Case1 Directions

Excel Tutorial 1: Mini-Case 1

Scenario: Dexter Lampe is excited. His new boss in the accounting department, Mr. Chaise, has asked him to spruce up a spreadsheet that he wants to present to the board of directors in the next board meeting. The spreadsheet contains financial information about the company's current balance sheet. In addition, it contains a section for forecasting sales for the next 3 years and analyzing how the forecast changes impact the balance sheet. It's the first opportunity that Dexter has had to show what he can do. He's sure that this will be the first of many successful assignments that will allow him to move up the corporate ladder at UltraCorp, Inc. Mr. Chaise attached the spreadsheet to an e-mail along with his instructions to Dexter. When Dexter opened it, he smiled and thought to himself, “Piece of cake!” All he would need to do is add a few formulas and do some formatting to make the spreadsheet presentable. The hard part would be in holding back and not getting too wild with the colors. He decided to get right to work.

Starter File: Tutorial 1: Mini-Case Balance Sheet

Your Task: Download the Excel file EXC_1_MC_Balance_Sheet.xlsx. Use this spreadsheet to help Dexter by completing the missing formulas in the spreadsheet and adjusting cell formats and font styles to make the balance sheet presentable for your boss. At a minimum, you should do the following:

·	Complete the missing formulas. Comments are provided on each cell (indicated by little red triangles in the cell) that describe the formula that you should enter. Simply place the cursor on the cell to read the comment.

·	Adjust all numeric values to display as currency format.

·	Use font styles (bold, italics, etc.) to highlight the various headings in the balance sheet.

·	Center the heading information at the top of the spreadsheet above the balance sheet and adjust the font and font style.

·	Use cell borders to separate the various sections of the balance sheet.

·	Fill the blank cells between the balance sheet columns with color to separate the columns visually.

·	Don't forget to save the file when you complete your work.

If you are not sure how to do some of these tasks, be sure to use Excel Help to find out how.

blog12

EXC_1_MC_Balance_Sheet.xlsx

Balance Sheet

Get help from top-rated tutors in any subject.