Complete the problems shown below in Excel Sheet. Show all calculations

1) Lambert Department Store is located in midtown Metropolis. During the past several years, net income has been declining because suburban shopping centers have been attracting business away from city areas. At the end of the companys fiscal year on November 30, 2014, these accounts appeared in its adjusted trial balance.

Accounts Payable $ 26,800

Accounts Receivable 17,200

Accumulated DepreciationEquipment 68,000

Cash 8,000

Common Stock 35,000

Cost of Goods Sold 614,300

Freight-Out 6,200

Equipment 157,000

Depreciation Expense 13,500

Dividends 12,000

Gain on Disposal of Plant Assets 2,000

Income Tax Expense 10,000

Insurance Expense 9,000

Interest Expense 5,000

Inventory 26,200

Notes Payable 43,500

Prepaid Insurance 6,000

Advertising Expense 33,500

Rent Expense 34,000

Retained Earnings 14,200

Salaries and Wages Expense 117,000

Sales Revenue 904,000

Salaries and Wages Payable 6,000

Sales Returns and Allowances 20,000

Utilities Expense 10,600

Additional data: Notes payable are due in 2018.

Instructions

(a) Prepare a multiple-step income statement, a retained earnings statement, and a classified balance sheet. (a) Net income $ 32,900 Tot. assets $146,400

(b) Calculate the profit margin and the gross profit rate.

(c) The vice president of marketing and the director of human resources have developed a proposal whereby the company would compensate the sales force on a strictly commission basis. Given the increased incentive, they expect net sales to increase by 15%. As a result, they estimate that gross profit will increase by $40,443 and expenses by $58,600. Compute the expected new net income. (Hint: You do not need to prepare an income statement.) Then, compute the revised profit margin and gross profit rate. Comment on the effect that this plan would have on net income and on the ratios, and evaluate the merit of this proposal. (Ignore income tax effects.)

Prepare financial statements and calculate profitability ratios

2. Sun Company is trying to determine the value of its ending inventory as of March 31, 2014, the companys year-end. The following transactions occurred, and the accountant asked your help in determining whether they should be recorded or not.

(a) On March 30, Sun shipped to a customer goods costing $1,000. The goods were shipped FOB destination, and the receiving report indicates that the customer received the goods on April 1.

(b) On March 28, Wholesale Inc. shipped goods to Sun FOB shipping point. The invoice price was $600 plus $20 for freight. The receiving report indicates that the goods were received by Sun on April 2.

(c) Sun had $750 of consigned goods from Frederick Inc.

(d) Sun had $380 of inventory at Stephens Variety, on consignment from Sun.

(e) On March 29, Sun ordered goods costing $640. The goods were shipped FOB destination on March 31. Sun received the goods on April 3.

(f) A customer returned goods to Sun on March 31. Upon inspection, the goods were found to be undamaged and were accepted as returned goods. These goods originally cost $400 and Sun sold them for $640.

Instructions: For each of the above transactions, specify whether the item in question should be included in ending inventory, and if so, at what amount. For each item that is not included in ending inventory, indicate who owns it and what account, if any, it should have been recorded in.

3. Granada Theater is in the Greenbelt Mall. A cashiers booth is located near the entrance to the theater. Two cashiers are employed. One works from 1:00 to 5:00 P.M., the other from 5:00 to 9:00 P.M. Each cashier is bonded. The cashiers receive cash from customers and operate a machine that ejects serially numbered tickets. The rolls of tickets are inserted and locked into the machine by the theater manager at the beginning of each cashiers shift.

After purchasing a ticket, the customer takes the ticket to a doorperson stationed at the entrance of the theater lobby some 60 feet from the cashiers booth. The doorperson tears the ticket in half, admits the customer, and returns the ticket stub to the customer. The other half of the ticket is dropped into a locked box by the doorperson.

At the end of each cashiers shift, the theater manager removes the ticket rolls from the machine and makes a cash count. The cash count sheet is initialed by the cashier. At the end of the day, the manager deposits the receipts in total in a bank night deposit vault

located in the mall. In addition, the manager sends copies of the deposit slip and the initialed cash count sheets to the theater company treasurer for verification and to the companys accounting department. Receipts from the first shift are stored in a safe located in the managers office.

Instructions

(a) Identify the internal control principles and their application to the cash receipts transactions of Granada Theater.

(b) If the doorperson and cashier decided to collaborate to misappropriate cash, what actions might they take?

Complete the problems shown below in Excel Sheet. Show all calculations 1) Lambert Department Store is located in midtown Metropolis. During the past several years, net income has been declining because suburban shopping centers have been attracting business away from city areas. At the end of the company's fiscal year on November 30, 2014, these accounts appeared in its adjusted trial balance. Accounts Payable $ 26,800 Accounts Receivable 17,200 Accumulated DepreciationEquipment 68,000 Cash 8,000 Common Stock 35,000 Cost of Goods Sold 614,300 Freight-Out 6,200 Equipment 157,000 Depreciation Expense 13,500 Dividends 12,000 Gain on Disposal of Plant Assets 2,000 Income Tax Expense 10,000 Insurance Expense 9,000 Interest Expense 5,000 Inventory 26,200 Notes Payable 43,500 Prepaid Insurance 6,000 Advertising Expense 33,500 Rent Expense 34,000 Retained Earnings 14,200 Salaries and Wages Expense 117,000 Sales Revenue 904,000 Salaries and Wages Payable 6,000 Sales Returns and Allowances 20,000 Utilities Expense 10,600 Additional data: Notes payable are due in 2018. Instructions (a) Prepare a multiple-step income statement, a retained earnings statement, and a classified balance sheet. (a) Net income $ 32,900 Tot. assets $146,400 (b) Calculate the profit margin and the gross profit rate. (c) The vice president of marketing and the director of human resources have developed a proposal whereby the company would compensate the sales force on a strictly commission basis. Given the increased incentive, they expect net sales to increase by 15%. As a result, they estimate that gross profit will increase by $40,443 and expenses by $58,600. Compute the expected new net income. (Hint: You do not need to prepare an income statement.) Then, compute the revised profit margin and gross profit rate. Comment on the effect that this plan would have on net income and on the ratios, and evaluate the merit of this proposal. (Ignore income tax effects.) Prepare financial statements and calculate profitability ratios 2. Sun Company is trying to determine the value of its ending inventory as of March 31, 2014, the company's year-end. The following transactions occurred, and the accountant asked your help in determining whether they should be recorded or not. (a) On March 30, Sun shipped to a customer goods costing $1,000. The goods were shipped FOB destination, and the receiving report indicates that the customer received the goods on April 1. (b) On March 28, Wholesale Inc. shipped goods to Sun FOB shipping point. The invoice price was $600 plus $20 for freight. The receiving report indicates that the goods were received by Sun on April 2. (c) Sun had $750 of consigned goods from Frederick Inc. (d) Sun had $380 of inventory at Stephen's Variety, on consignment from Sun. (e) On March 29, Sun ordered goods costing $640. The goods were shipped FOB destination on March 31. Sun received the goods on April 3. (f) A customer returned goods to Sun on March 31. Upon inspection, the goods were found to be undamaged and were accepted as returned goods. These goods originally cost $400 and Sun sold them for $640. Instructions: For each of the above transactions, specify whether the item in question should be included in ending inventory, and if so, at what amount. For each item that is not included in ending inventory, indicate who owns it and what account, if any, it should have been recorded in. 3. Granada Theater is in the Greenbelt Mall. A cashier's booth is located near the entrance to the theater. Two cashiers are employed. One works from 1:00 to 5:00 P.M., the other from 5:00 to 9:00 P.M. Each cashier is bonded. The cashiers receive cash from customers and operate a machine that ejects serially numbered tickets. The rolls of tickets are inserted and locked into the machine by the theater manager at the beginning of each cashier's shift. After purchasing a ticket, the customer takes the ticket to a doorperson stationed at the entrance of the theater lobby some 60 feet from the cashier's booth. The doorperson tears the ticket in half, admits the customer, and returns the ticket stub to the customer. The other half of the ticket is dropped into a locked box by the doorperson. At the end of each cashier's shift, the theater manager removes the ticket rolls from the machine and makes a cash count. The cash count sheet is initialed by the cashier. At the end of the day, the manager deposits the receipts in total in a bank night deposit vault located in the mall. In addition, the manager sends copies of the deposit slip and the initialed cash count sheets to the theater company treasurer for verification and to the company's accounting department. Receipts from the first shift are stored in a safe located in the manager's office. Instructions (a) Identify the internal control principles and their application to the cash receipts transactions of Granada Theater. (b) If the doorperson and cashier decided to collaborate to misappropriate cash, what actions might they take? Question 2 a) The goods should be recorded in the ending inventory of Sun ltd at $1000 since the goods were shipped FOB destination and had not been received by the buyer on 30th March which was the balance sheet date. The goods were delivered a day later on 1st April. b) The goods should not be reported in the ending inventory of Sun since on March 31st the goods had not been received by sun since they were shipped FOB destination. The goods will remain as part of the inventory of Wholesale Inc. at $600 up to 2nd April when they are received by Sun. c) The goods should not be included as part of Sun inventory. This is because sun has not yet sold the goods as at the balance sheet date. The goods will still remain as part of Inventory of Frederick Inc. at $750. d) The goods on consignment with Stephan's Variety should be recorded as part of the inventory at $380 since the goods have already not been sold by the agent as at the reporting date. e) The goods returned by the customer at the balance sheet (31st march) date will be part of the closing inventory. The goods will be recorded at cost $400 since assets are recorded at cost and the balance $240 reported as part of revenue for the year in the statement of income. Question 3 1. Establishment of authority In the Granada Theatre responsibility has been specifically designed to each employee. Each employee performs one task at a time. The jod tasks should also be approved by a relevant person. In Granada Theatre, responsibities have been clearly defined. The work of the doorperson is to admit customers to the theatre, the cashiers is to collect cash and offer out receipts and the manager is to lock the roll of receipts and carry out a cash count at the end of the day. 2. Segreggation of duties Duties should be separated between the employees to avoid duplication of tasks and reduce errors. Only closely related tasks should be allocated to each employee. In Granada Theatre, the cashiers only collect cash from customers but the duty of cash sount has been left for the Granada Theatre manager. 3. Physical, Electronic and Mechanical controls This is put in place to safeguard the company assets and avoid any potential loss. In Granada Theatre, the doorperson has been placed 60 feet away from the cashier to avoid any collusion as a physical control. There are also physical lock boxes where the receipts are kept by the manager and another lock where the other half torn ticket by the person is locked. This is to avoid any loss or theft of receipts. 4. Documentation procedures All transactions in the business should be recorded properly to avoid any error or fraud. Documentation also helps in providing evidence of the transactions that have taken place. In Granada Theatre, the machine produces prenumbered tickets to keep track of the taransactions that have taken place. 5. Independent Internal Verification All activities perfomed by the employees should be reviewed, compared and reconciled by another independent party to disclose any fraud or error. In Granada Theatre, after the deposit of the cash by the Granada Theatre manager, copies of the cash deposits are sent to the company treasurer and the account department for verification and approval. 6. Rotation of workers and other controls In Granada Theatre, the cashiers are rotated in shifts. One cashier starts working from 1-5 p.m and the other one from 5-9 p.m. The other controls available in Granada Theatre, is the bonding of each cashier. 3 b) i) The cashier and door person may agree to admit customers to the theatre without the customer specifically paying cash at the counter. ii) The doorperson may fail to tear the receipts given to him by the customers so that the receipts may be used by another customer. LAMBERT DEPARTMENT STORE STATEMENT OF INCOME FOR THE YEAR ENDED NOVEMBER 30, 2014 $ $ Sales Revenue Less: Sales return and allowances Net sales Less: Cost o goods sold of Gross profit Expenses Freight-out Depreciation Insurance Advertising Rent Salaries and wages Utilities Total expenses Operating profit before interest expense and income tax Interest Expense Operating profit before income tax Add: Gain on disposal of of Plant Assets Total Income before Income tax Less: Income Tax expense Net income 904000 20000 884000 614300 269700 6200 13500 9000 33500 34000 117000 10600 223800 45900 5000 40900 2000 42900 10000 32900 LAMBERT DEPARTMENT STORE STATEMENT OF RETAINED EARNINGS FOR THE YEAR ENDED NOVEMBER 30,2014 Balance 1/1/2014 Add: Net profit for the year Total Retained Earnings Less: Dividends Balance 30/11/2014 $ 14200 32900 47100 12000 35100 LAMBERT DEPARTMENT STORE BALANCE SHEET FOR THE YEAR ENDED NOVEMBER 30,2014 $ $ ASSETS Current Assets Inventory Accounts Receivable Cash Prepaid Insurance Non-Current Assets Equipment Less: Accumulated Depreciation Total Assets 26200 17200 8000 6000 157000 68000 57400 89000 146400 SHAREHOLDER'S EQUITY AND LIABILITIES Equity Common stock Retained Earnings Total Equity 35000 35100 Liabilities Notes payable Accounts Payable Salaries and wages payable Total Equity and Liabilities 43500 26800 6000 70100 76300 146400 1 (b) The gross profit, sales, net sales and expenses have been extracted from the financial statements. They do not require recomputation s will be duplication of work done. Refer to worksheets 1, 2 and 3 for the figures. Sales from Income statement= $904,000, Net sales= $8 $269,700 and expenses= $223,000 (All figures have been extracted from statement of income). Gross profit rate= Gross profit Net sales Gross profit rate= Gross profit rate= 269700 884000 0.305 Profit Margin= Gross profit rate X 100 Profit Margin= 0.305 X 100 = 30.5% 1 c) New Sales= $904000 X 115/100 =$1039600 New Gross profit= $269700 + $ 40443= $310143 New expenses= $223800 + $58600 = $282400 New Net Income= $310143 - $ 282400 = $27743 Revised profit rate= New Net sales= 1039600 - 20000 =1019600 310143 1019600 Profit rate= 0.304 Profit Margin = 0.304 X 100 =30.4% The net income has dropped by $27743. this has been attributed by the unproportional increase in expenses and the increase in sales The rate at which the expenses has increased is more compared to the rate at which the sales revenue has increased. The profit margin however has remained almost the same and hence the plan may be adopted by the company so as to motivate sales since earnings will be based on perfomance on since it $884000, Gross profit= es revenue les people