I have been working on this accounting problem and looking at the one you have posted. It seems in the required section #2 column B22 balance should be $2020.00 and on the problem that you have under FTRANS the retained earnings account balance equals that. I have been held up on this problem for 2 weeks and it is getting me behind. Once I get this part of the problem solved I pretty sure I am okay.

CORPORATE TRANSACTIONS FTRANS Excel Applications for Accounting Principles: Website Problem by Gaylord N. Smith LEARNING OBJECTIVES * Show how financial position is affected by transactions. * Prepare three basic corporate financial statements. * Back-solve for cash balances. * Alter the worksheet to accommodate additional transactions. * Create a chart showing liability and equity categories. PROBLEM DATA On June 1 of the current year, Wilson Wood opened Woody's Web Services, Inc. The corporation had the following transactions during June. a. Opened a business checking account and made a deposit, $4,000. In exchange, Wilson was issued 200 shares of $20 par value stock. b. Paid rent for June for office space and computer equipment, $2,000. c. Purchased office supplies (stamps, pens, etc.) on account, $680. d. Received cash for services rendered, $3,500. e. Paid creditor for office supplies purchased on account, $500. f. Purchased office supplies for cash, $190. g. Billed clients for consultations performed on account, $4,400. h. Paid the monthly internet service bill of $180. i. Paid the secretary's salary of $2,400. j. Declared and paid dividends of $500. k. Received cash from clients previously billed, $1,500. l. Returned $150 of office supplies purchased in transaction f above. Received a full refund. REQUIRED 1) You are to record these transactions using a spreadsheet format. Click the Answer tab and enter your name in cell C5. Then click the Worksheet tab and enter your name in cell C1 of the worksheet. There are six formulas needed to complete the worksheet. Enter the formulas in the appropriate cells. Then enter the increases and decreases resulting from each transaction on the worksheet. For example, the first transaction increases the cash account by $8,000 and also increases the common stock account by $8,000. This transaction has already been recorded on the worksheet. Enter negative numbers with a minus sign (-). The worksheet will automatically total each column as values are entered in that column. When you are finished, check line 27 to make sure that total assets equal total liabilities and equity. 2) It has been determined that the cost of the supplies used during the month was $300. Record the increase or decrease in the appropriate columns on the worksheet. This is Transaction m. Save the completed file as FTRANS2. Print the worksheet. Also print your formulas. Check figure: Ending cash balance (cell B22), $2,020 3) How does Transaction m differ from Transactions a through I? In other words, why is it treated as a special item? 4) Prepare an income statement, a statement of retained earnings, and a balance sheet in good form for Woody's Web Services, Inc for the month of June. WHAT-IF ANALYSIS 5) Wilson initially invested $8,000 in the business. Could he have invested less? How little could he have invested initially and never have his cash balance go below zero? To help answer this question, move to column K and analyze the information provided. Then enter different amounts in cell B9 to help calculate an answer. When you have determined an answer, print the worksheet again. Explain how you derived your answer. CHART ANALYSIS 6) Reset cell B8 to $5,000. Click the Chart tab. A pie chart appears on the screen indicating the percentage of each asset in relation to total assets. Wilson does not want his cash balance to exceed 40% of his total assets. How much does he have to withdraw as dividends to reduce his cash percentage to 40%? To find out, enter different (negative) values in cell B18 of the worksheet and click CHART ANALYSIS 6) Reset cell B8 to $5,000. Click the Chart tab. A pie chart appears on the screen indicating the percentage of each asset in relation to total assets. Wilson does not want his cash balance to exceed 40% of his total assets. How much does he have to withdraw as dividends to reduce his cash percentage to 40%? To find out, enter different (negative) values in cell B18 of the worksheet and click the Chart tab after each entry. When the assignment is complete, reset cell B18 to -500. Click the Answer tab and print the Answer sheet. Save the file again as FTRANS2. TICKLERS (optional) Worksheet. Two additional transactions occurred in June which need to be recorded on the worksheet. n. Billed customers for additional services rendered on account, $1,200. o. Paid creditor for office supplies purchased on account, $120. Expand the FTRANS2 worksheet to include these transactions. Do not revise column K. Preview the printout to make sure that the worksheet will print neatly on one page, then print the worksheet. Save the revised file as FTRANST. Chart. Using the FTRANS2 file, create a 3-D pie chart that shows the relative percentages of the liability and equity accounts. Complete the Chart Tickler Data Table on the Chart worksheet and use it as a basis for preparing the chart. Put your name somewhere on the chart. Save the file again as FTRANS2. Select the chart and then print it out. CORPORATE TRANSACTIONS Excel Applications for Accounting Principles: Website Problem by Gaylord N. Smith Student Name: Lori Ball Answer sheet for requirements 3, 4, 5, and 6 3) 4) FTRANS 5) 6) Dividends required: A 1 2 B C Student Name: Lori Ball D 5 6 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 F G H I J a) b) c) d) e) f) g) h) i) j) k) l) m) n) o) Cash $4,000 (2,000) Assets Accounts Receivable $0 3,500 (500) (190) Office Supplies $0 = Liabilities Accounts Payable $0 680 680 + Equity Common Retained Stock Earnings $4,000 $0 (2,000) 3,500 (500) 190 4,400 (180) (2,400) (500) 1,500 150 4,400 (180) (2,400) (1,500) (150) (300) (300) $420 (120) ($420) 1,200 (120) $3,260 $4,100 1,200 $4,000 $4,700 24 25 26 27 28 29 K FTRANS Corporate Transactions 3 4 7 8 E Balance Verification Total assets Total liabilities and equity Difference $7,780 8,280 ($500) Running Cash Balance $4,000 2,000 2,000 5,500 5,000 4,810 4,810 4,630 2,230 1,730 3,230 3,380 3,380 ASSETS Percentage Breakdown Supplies 5% Cash 42% A/R 53% Chart Data Table Cash A/R Supplies 3260 4100 420 Chart Tickler Data Table A/P C/S R/E CORPORATE TRANSACTIONS FTRANS Excel Applications for Accounting Principles: Website Problem by Gaylord N. Smith LEARNING OBJECTIVES * Show how financial position is affected by transactions. * Prepare three basic corporate financial statements. * Back-solve for cash balances. * Alter the worksheet to accommodate additional transactions. * Create a chart showing liability and equity categories. PROBLEM DATA On June 1 of the current year, Wilson Wood opened Woody's Web Services, Inc. The corporation had the following transactions during June. a. Opened a business checking account and made a deposit, $4,000. In exchange, Wilson was issued 200 shares of $20 par value stock. b. Paid rent for June for office space and computer equipment, $2,000. c. Purchased office supplies (stamps, pens, etc.) on account, $680. d. Received cash for services rendered, $3,500. e. Paid creditor for office supplies purchased on account, $500. f. Purchased office supplies for cash, $190. g. Billed clients for consultations performed on account, $4,400. h. Paid the monthly internet service bill of $180. i. Paid the secretary's salary of $2,400. j. Declared and paid dividends of $500. k. Received cash from clients previously billed, $1,500. l. Returned $150 of office supplies purchased in transaction f above. Received a full refund. REQUIRED 1) You are to record these transactions using a spreadsheet format. Click the Answer tab and enter your name in cell C5. Then click the Worksheet tab and enter your name in cell C1 of the worksheet. There are six formulas needed to complete the worksheet. Enter the formulas in the appropriate cells. Then enter the increases and decreases resulting from each transaction on the worksheet. For example, the first transaction increases the cash account by $8,000 and also increases the common stock account by $8,000. This transaction has already been recorded on the worksheet. Enter negative numbers with a minus sign (-). The worksheet will automatically total each column as values are entered in that column. When you are finished, check line 27 to make sure that total assets equal total liabilities and equity. 2) It has been determined that the cost of the supplies used during the month was $300. Record the increase or decrease in the appropriate columns on the worksheet. This is Transaction m. Save the completed file as FTRANS2. Print the worksheet. Also print your formulas. Check figure: Ending cash balance (cell B22), $2,020 3) How does Transaction m differ from Transactions a through I? In other words, why is it treated as a special item? 4) Prepare an income statement, a statement of retained earnings, and a balance sheet in good form for Woody's Web Services, Inc for the month of June. WHAT-IF ANALYSIS 5) Wilson initially invested $8,000 in the business. Could he have invested less? How little could he have invested initially and never have his cash balance go below zero? To help answer this question, move to column K and analyze the information provided. Then enter different amounts in cell B9 to help calculate an answer. When you have determined an answer, print the worksheet again. Explain how you derived your answer. CHART ANALYSIS 6) Reset cell B8 to $5,000. Click the Chart tab. A pie chart appears on the screen indicating the percentage of each asset in relation to total assets. Wilson does not want his cash balance to exceed 40% of his total assets. How much does he have to withdraw as dividends to reduce his cash percentage to 40%? To find out, enter different (negative) values in cell B18 of the worksheet and click CHART ANALYSIS 6) Reset cell B8 to $5,000. Click the Chart tab. A pie chart appears on the screen indicating the percentage of each asset in relation to total assets. Wilson does not want his cash balance to exceed 40% of his total assets. How much does he have to withdraw as dividends to reduce his cash percentage to 40%? To find out, enter different (negative) values in cell B18 of the worksheet and click the Chart tab after each entry. When the assignment is complete, reset cell B18 to -500. Click the Answer tab and print the Answer sheet. Save the file again as FTRANS2. TICKLERS (optional) Worksheet. Two additional transactions occurred in June which need to be recorded on the worksheet. n. Billed customers for additional services rendered on account, $1,200. o. Paid creditor for office supplies purchased on account, $120. Expand the FTRANS2 worksheet to include these transactions. Do not revise column K. Preview the printout to make sure that the worksheet will print neatly on one page, then print the worksheet. Save the revised file as FTRANST. Chart. Using the FTRANS2 file, create a 3-D pie chart that shows the relative percentages of the liability and equity accounts. Complete the Chart Tickler Data Table on the Chart worksheet and use it as a basis for preparing the chart. Put your name somewhere on the chart. Save the file again as FTRANS2. Select the chart and then print it out. CORPORATE TRANSACTIONS Excel Applications for Accounting Principles: Website Problem by Gaylord N. Smith Student Name: Answer sheet for requirements 3, 4, 5, and 6 3) 4) FTRANS 5) 6) Dividends required: A 1 2 B C 11 12 13 14 15 16 17 18 19 20 21 F Office Supplies $0 Liabilities Accounts Payable $0 FORMULA1 FORMULA2 FORMULA3 FORMULA4 5 6 9 10 E G H I a) b) c) d) e) f) g) h) i) j) k) l) m) Cash $4,000 Assets Accounts Receivable $0 = + Equity Common Retained Stock Earnings $4,000 $0 FORMULA5 FORMULA6 22 23 24 25 26 27 J K FTRANS Corporate Transactions 3 4 7 8 D Student Name: Balance Verification Total assets Total liabilities and equity Difference $0 #VALUE! #VALUE! Running Cash Balance $4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 ASSETS Percentage Breakdown Chart Data Table Cash FORMULA1 A/R FORMULA2 Supplies FORMULA3 Chart Tickler Data Table A/P C/S R/E CORPORATE TRANSACTIONS Excel Applications for Accounting Principles: Website Problem by Gaylord N. Smith FTRANS Answer sheet for requirements 3, 4, 5, and 6 3) This is an adjusting entry. Such entries are needed because simply recording external transactions rarely tells the whole story. The internal consumption of supplies affects the financial status of the firm just as much as an external transaction. 4) Woody's Web Services Income Statement June, 20XX Revenue Expenses: $7,400 Rent Internet services Salaries Supplies Net income $2,000 180 2,400 300 4,880 $2,520 Woody's Web Services Statement of Retained Earnings June, 20XX Beginning balance Net income Less: dividends Ending balance Woody's Web Services $ -02,520 (500) $2,020 Balance Sheet June 30, 20XX Assets Cash Accounts receivable Office supplies $2,880 2,900 420 $6,200 Liabilities Accounts payable Stockholders' Equity Common stock Retained earnings $180 $4,000 2,020 6,020 $6,200 5) There are a number of answers to this question. Some possibilities are: a. $1,120 - results in a zero cash balance at the end of the month. However, Woody would have had negative cash balances for some of the month b. $2,770 - ensures that the cash balance never drops below zero during the month c. $2,270 - same as Answer b above, but this assumes that the owner does not make the $500 dividend payment to himself at the end of the month. 6) Dividends required: Dividends in the range $1,118 to $1,199 will reduce the percentage to 40% (rounded off). Student Name: a) b) c) d) e) f) g) h) i) j) k) l) m) Cash $4,000 (2,000) Solution to Requirement 2 FTRANS Corporate Transactions Assets Accounts Receivable $0 3,000 (500) (190) Office Supplies $0 = Liabilities Accounts Payable $0 680 680 + Equity Common Retained Stock Earnings $4,000 $0 (2,000) 3,000 (500) 190 4,400 (180) (2,400) (500) 1,500 150 $2,880 4,400 (180) (2,400) (500) (1,500) $2,900 (150) (300) $420 $180 Balance Verification Total assets Total liabilities and equity Difference $4,000 (300) $2,020 $6,200 6,200 $0 Running Cash Balance $4,000 2,000 2,000 5,000 4,500 4,310 4,310 4,130 1,730 1,230 2,730 2,880 2,880 ASSETS Percentage Breakdown Supplies 7% Cash 46% A/R 47% Chart Data Table Cash $2,880 A/R $2,900 Supplies $420 Chart Tickler Data Table A/P $180 C/S $4,000 R/E $2,020 Student Name: a) b) c) d) e) f) g) h) i) j) k) l) m) n) o) Cash $4,000 (2,000) Solution to Worksheet Tickler FTRANS Corporate Transactions Assets Accounts Receivable $0 3,000 (500) (190) Office Supplies $0 = Liabilities Accounts Payable $0 680 680 + Equity Common Retained Stock Earnings $4,000 $0 (2,000) 3,000 (500) 190 4,400 (180) (2,400) (500) 1,500 150 4,400 (180) (2,400) (500) (1,500) (150) (300) (300) 1,200 1,200 (120) $2,760 $4,100 $420 (120) $60 Balance Verification Total assets Total liabilities and equity Difference $4,000 $3,220 $7,280 7,280 $0 Running Cash Balance $4,000 2,000 2,000 5,000 4,500 4,310 4,310 4,130 1,730 1,230 2,730 2,880 2,880 Solution to Chart Tickler Liabilities and Equity R/E 33% A/P 3% C/S 65%