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

Excel Tutorial 1 Mini-Case1 Directions

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/title/left_2.gif

Excel Tutorial 1: Mini-Case 1

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/title/center_2.gif

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

  

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.

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/sf.gif  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:

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

·  

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.

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

·  

Adjust all numeric values to display as currency format.

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

·  

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

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

·  

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

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

·  

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

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

·  

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

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

·  

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.

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

http://edugen.wileyplus.com/edugen/courses/crs4877/common/art2/pixel.gif

Get help from top-rated tutors in any subject.

Efficiently complete your homework and academic assignments by getting help from the experts at homeworkarchive.com