Question: For this assignment, you will need to copy the excel file provided in Project Assignment 1 into your UMSystem Office365 OneDrive. You should have been
For this assignment, you will need to copy the excel file provided in Project Assignment 1 into your UMSystem Office365 OneDrive. You should have been prompted to do so when you clicked on the link for the file. Within the googlesheets file here will be three sheets, one called Prior YR Final Trial Balance, one called “Working Trial Balance”. On the sheet called “Working Trial Balance,” you will find the working trial balance as of the end of the 1stquarter. This is the information you will use to complete the following tasks.
1) On the “Working Trial Balance” In column B titled as “F/S” indicate which financial statement will include each account listed on the working trial balance using these abbreviations (you may have to do some internet searching to determine which kind of financial statement element you are working with if you are not familiar with the account name):
- B for balance sheet
- I for income statement
- C for a statement of comprehensive income
- S for statement of shareholders’ equity
2) At the far right you will see a column titled “Adjusted TB”. In this column enter a formula that captures the beginning balance of the account and any debits or credits made during the year. I have provided an example in the cash account (NOTE: because the Cash account is normally a debit account, the formula I have provided shows that all “debit” items increase the balance while all “credit” items decrease the balance. When you enter a formula for credit accounts you will need to have all “debit” items decrease the balance while all “credit” items increase the balance). If you need a refresher on how to include formulas in excel, there is a link in Project Assignment 1 that will lead you to a tutorial
3)Open a new sheet in the workbook by clicking on the “+” on the bottom left of the screen. This is going to be your Income Statement page. Using the numbers from the “Adjusted Trial Balance” column on the working trial balance, create a multi-step income statement that includes every account you identified as “I” in step 1 above. (If you need a refresher on income statement use chapter 4 of your textbook - do NOT use an online template or the acctng 3401 “Paris Bike” income statement but instead create your own). You should create the income statement such that it updates automatically if you add any debits or credits to the accounts existing on your working trial balance.
4) Open a new sheet in the workbook by clicking on the “+” on the bottom left screen. This is going to be your Balance Sheet page. Using the numbers from the “Adjusted Trial Balance” column on the working trial balance, create a classified balance sheet that includes every account you identified as “B” in step 1 above. (If you need a refresher on the balance sheet use chapter 3 of your textbook- again do NOT use an online template or the acctng 3401 “Paris Bike” balance sheet but create your own).
IMPORTANT NOTE: When you get to the “ Retained Earnings” line on the balance sheet you will need to pick up the balance from the Adjusted Trial Balance column like you did for the other accounts PLUS you will need to pick up the Net Income (Loss) from your income statement PLUS you will need to subtract any (Dividends) from the Adjusted Trial Balance page. FYI – the extra steps are necessary because you have not done closing entries for the period in this assignment.
You also need to adjust the Accumulated Comprehensive loss line on the balance sheet by picking up any debits or credits on the working trial balance that you have marked "C" as items that flow through the Statement of Comprehensive Income. FYI - these extra steps are necessary because you have not done closing entries for the period in this assignment.
5) UPLOAD A LINK TO CANVAS: This assignment requires you to upload a URL link to your OneDrive excel file. You need to make sure that I have access to your project so click on the “share” icon on the top right and choose “People in the University of Missouri with the link can edit ” (so leave the "allow editing" box checked - this will allow me to give you useful feedback). Copy the "copy link" in the "send link" box and paste the link when you are asked for a “website URL” in the assignment “submit” process. There is a video in Project Assignment 1 that will walk you through this process so you can see exactly what it will look like and what you do.
| Changes during the quarter ended June 30, 2020 | Changes during the quarter ended Sept 30, 2020 | Changes during the quarter ended Dec 31, 2020 | Adjusted Trial Balance as of Dec 31, 2020 | ||||||||||
| URLIVE | As of March 31, 2020 | Adjusting Entries | |||||||||||
| CONDENSED CONSOLIDATED BALANCE SHEETS - USD ($) $ in Thousands | F/S | Debit | Credit | Debit | Credit | Debit | Credit | Debit | Credit | Debit | Credit | Debit | Credit |
| Cash and cash equivalents | B | $ 488,653 | $ 488,653 | 0 | |||||||||
| Marketable securities | B | 616,714 | $ 616,714 | 0 | |||||||||
| Accounts receivable | B | 269,502 | $ 269,502 | 0 | |||||||||
| Allowance for bad debts | B | 11990 | $ (11,990) | 11990 | |||||||||
| Deferred contract acquisition costs, current | B | 84,054 | $ 84,054 | 0 | |||||||||
| Prepaid expenses and other current assets | B | 336,024 | $ 336,024 | 0 | |||||||||
| Deferred contract acquisition costs, noncurrent | B | 115,643 | $ 115,643 | 0 | |||||||||
| Property and equipment, net | B | 60,479 | $ 60,479 | 0 | |||||||||
| Natural Resource Asset | B | 65,316 | $ 65,316 | 0 | |||||||||
| Equity Method Investments | B | 43,314 | $ 43,314 | 0 | |||||||||
| Accounts payable | B | 3,472 | $ (3,472) | 3472 | |||||||||
| Accrued expenses and other current liabilities | B | 507,010 | $ (507,010) | 507010 | |||||||||
| Deferred revenue, current | B | 523,246 | $ (523,246) | 523246 | |||||||||
| Deferred revenue, noncurrent | B | 28,596 | $ (28,596) | 28596 | |||||||||
| Asset Retirement Obligations | B | 62,989 | $ (62,989) | 62989 | |||||||||
| Other liabilities, noncurrent | B | 40,765 | $ (40,765) | 40,765 | |||||||||
| Common stock, $0.001 par value per share | B | 280 | #VALUE! | #VALUE! | |||||||||
| Additional paid-in capital | S | 872,237 | #VALUE! | #VALUE! | |||||||||
| Accumulated other comprehensive income | C | 809 | $ (809) | 809 | |||||||||
| Retained earnings | B | 152 | #VALUE! | #VALUE! | |||||||||
| Revenue | I | $ 328,167 | $ (328,167) | 328167 | |||||||||
| Cost of revenue | I | 103,707 | $ 103,707 | 0 | |||||||||
| Research and development | I | 26,389 | $ 26,389 | 0 | |||||||||
| Sales and marketing | I | 117,200 | $ 117,200 | 0 | |||||||||
| Bad Debt Expense | I | 4,356 | $ 4,356 | 0 | |||||||||
| General and administrative | I | 24,320 | $ 24,320 | 0 | |||||||||
| Stock Based Compensation | I | 28,810 | $ 28,810 | 0 | |||||||||
| Interest income and other income | I | 5,790 | $ (5,790) | 5790 | |||||||||
| Provision for income taxes | S | 2,100 | $ 2,100 | 0 | |||||||||
| Unrealized gain on available-for-sale marketable securities, net of tax | C | 1,078 | $ (1,078) | 1078 | |||||||||
| Totals | $ 2,386,581 | $ 2,386,581 | $ 0 | $ 0 | $ 0 | $ 0 | $ 0 | $ 0 | $ 0 | $ 0 | #VALUE! | #VALUE! |
EXTRA GOOGLESHEET hints I have figured out:
- When you need to empty out a cell (say you put the number in the debit column but it really should have been in the credit column), use the ‘ key (apostrophe only) to empty the cell. Type the apostrophe and hit return and the cell will become empty (in excel you normally do this with the space bar BUT THIS WILL NOT WORK IN GOOGLESHEETS).
- If you put in a formula and it shows #VALUE instead of a sum, then enter an ‘ (apostrophe) into all of the blank cells in your formula to make it work.
Step by Step Solution
3.58 Rating (155 Votes )
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help