Description

MISY 341- New Age Phones Balance Sheets

Spring 2021

New Age Phones is a Baltimore based company that sells popular phones. After launching its website three years ago, the company has attracted so many clients from the east coast that the owners opened additional stores in Philadelphia, New York and Boston.

The following three tables give you the assets, liabilities, and stockholders’ equity data for the Baltimore, Philadelphia, New York and Boston stores for the last year. Now the company asks you to create a balance sheet for each store and also the entire company.

Assets

Baltimore Philadelphia New York Boston

Cash 725,101 353,321 245,213 450,450

Account Receivable 357,190 332,180 365,404 205,860

Marketable Securities 145,213 67,499 271,621 81,450

Inventory 645,258 79,320 103,210 106,120

Equipment 247,603 52,330 80,000 30,770

Liabilities

Baltimore Philadelphia New York Boston

Notes Payable 1,012,381 343,000 800,995 342,000

Accounts Payable 213,360 82,500 98,000 109,900

Income Tax Payable 62,100 36,400 44,000 55,350

Stockholders’ Equity

Baltimore Philadelphia New York Boston

Common Stock 612,300 340,600 37,801 205,000

Retained Earnings 220,224 82,150 84,652 162,400

Use the concepts and techniques presented in Chapters 4-6, especially Chapter 5, to design an Excel workbook to fulfill the company’s requirements with the data given above. Save your exam with a file name.

(10 points) Your finished workbook should contain 5 worksheets: one for the Baltimore store, one for the Philadelphia store, one for the New York store, one for the Boston store, and one Consolidated worksheet consolidating the 4 stores’ data for the entire company. Each worksheet should have an appropriate sheet tab name (instead of just Sheet 1, Sheet 2, etc.) and tab color that is consistent with the corresponding color used on the worksheet.

(55 points) Each worksheet should include the relevant assets, liabilities, and stockholders’ equity data; and in addition, you should also calculate the totals for assets, liabilities, and stockholders’ equity, respectively using the SUM function, on each worksheet.  All the numbers and formulas need to be correct on each worksheet. More Specifically,

(7 points per worksheet; 28 points in total) The worksheet for each store should include the assets, liabilities, and stockholders’ equity data and the three totals only for that corresponding store, e.g., the Baltimore worksheet should have the data only for the Baltimore store, the Philadelphia worksheet should have only the Philadelphia data, and so on. Again, the three totals should be calculated using the SUM function.

(15 points for 3-D cell reference formulas; 3 points for the three totals; 9 points for the layout of columns and rows; 27 points in total) The Consolidated worksheet should have the exact same number of columns and rows and structure as the other 4 store worksheets. What is different from the other 4 worksheets is that on the consolidated worksheet for the entire company, all the numbers should be calculated using formulas. Most importantly, you are not supposed to enter the 4 stores’ data again on the consolidated worksheet; instead, you should use 3-D cell references to link to the data on the other 4 worksheets to come up with the assets, liabilities, and stockholders’ equity data for the entire company (e.g., similar to your calculation for the 2017 column on the Consolidated worksheet in Excel Chapter 5, page EX 272 – EX 275). As an example, on the Consolidated worksheet, Cash under Assets for the entire company should equal to the sum of the Cash amount from the Baltimore, Philadelphia, New York, and Boston worksheets. The same logic applies to the other categories. The three totals (i.e., total assets, total liabilities, and total stockholders’ equity) on the Consolidated worksheet should be calculated in the exact same way using the SUM function as on the other 4 stores’ worksheets and do not require the use of 3-D cell references. 

(35 points) Consistent formatting needs to be applied across all the five worksheets:

(10 points) Your designed layouts, styles and formatting need to be consistent, very clear and straightforward across all the five worksheets. 

(15 points) All the data and texts need to be formatted professionally and consistently across all the worksheets; each worksheet should have a date; in addition, each worksheet should be set to print nicely on one page. Regarding the professional formatting of the dollar amounts, make sure you follow the same rules as all the other Excel assignments. 

(10 points) At the same time, each worksheet must have its own title, subtitle, distinctive color, header, and footer as you see appropriate so that each store can be easily identified while being a part of the same company. Make sure you do not use colors or fonts that are difficult to read. In addition, please do not forget to do a spell check of your entire workbook before submission.

Instruction Files