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
|

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