Description

Fixed Asset Analysis

Big Publishing Co.

2016

It is October 31, 2016.  Big Publishing Co. is considering buying Little Publishing Co.  Little is a family owned newsletter publishing business.  Little has three very successful business newsletter products that interest Big.  Big has assigned you to do some investigative work of Little’s accounting records.  (FYI—Prior to buying out a company, the acquiring company performs “due diligence” financial and legal investigative work to determine the value of the target company.)  Specifically, you have been assigned to investigate the status of Little’s fixed asset accounting.  You found out that Little has done no fixed asset accounting whatsoever.  So, you roll up your sleeves and tell Little’s accountants that you need to take a fixed asset inventory.  You walk around to each department and record the following assets.

GL=General Ledger; EUL = Estimated Useful Life

 

Asset	GL		Date	EUL
Description	Account#	Dept.	Purchased	mos.	Cost
Furniture & Fixtures	1630
Filing Cabinets	1630	Admin	09-Sep-14	60	8,200
Filing Cabinets	1630	Design	09-Jan-16	60	2,000
Drafting Desks	1630	Design	09-Jan-14	60	5,000
Desks & Chairs	1630	Various	11-May-14	60	30,000
Desks & Chairs	1630	Various	11-May-12	36	8,500
Tables & Chairs	1630	Various	23-Mar-14	60	8,000
Sofas, Rugs, Art	1630	Various	12-May-12	84	12,775
Leasehold Improve	1640
Partitions	1640	Various	31-Dec-14	84	16,000
Office Equipment	1650
PCs	1650	Editorial	09-Sep-14	60	20,000
PCs	1650	Acctg	03-Mar-15	60	15,100
PCs	1650	Design	12-Jan-16	60	25,000
Printers	1650	Editorial	09-Sep-14	36	7,600
Printers	1650	Acctg	03-Mar-15	36	4,000
Printers	1650	Design	12-Jan-15	36	9,000
Capital Lease Asset	1660
Xerox Copier	1660	Design	17-Sep-14	60	20,100
Xerox Copier	1660	Acctg	01-May-15	60	22,500
Xerox Copier	1660	Admin	01-Jan-16	60	35,000
Phone System	1660	All	15-Sep-14	60	78,000
PCs-IBM	1660	Acctg	20-Oct-16	60	5,000
Acctg Software (ignore)	1660	Acctg

 

 

The inventory items were tagged with date of purchase, so you recorded that information on your schedule.

Both Big’s and Little’s year-end is December 31.

Required:

1. Prepare a spreadsheet so that it meets your boss’ needs. Specifically, the schedule must support the amounts to be included on the financial statements. Big wants to assemble a beginning of year (12-31-15) Balance Sheet and a current (10-31-16) Balance Sheet, as well as monthly and year-to-date (YTD) Income Statements.  Your schedule will only support the Balance Sheet and Income Statement information related to Fixed Assets (not the Statement of Cash Flows).
2. Input the data and formulas in your spreadsheet. An “A” spreadsheet would require minimal human input to update the data for each month’s financial statements. Recall that a good spreadsheet is Accurate, Clear, Professional, and easy to modify.
3. You need to know the company’s depreciation method and depreciation policy. Little uses straight-line depreciation and it takes a full month of depreciation in the month of acquisition (even if it buys the asset on the last day of that month).
4. Hint: there may be some errors in the data you just received.  Your spreadsheet should be designed to deal with changes to the Cost, EUL, purchase date, and the current period (month).  For example, the current period/date is October (period 10). Your spreadsheet must show the appropriate values if the current period is changed from October to November (or December or back to September). The change to the purchase date is perhaps the most complicated change.  I would get your spreadsheet to work for the other three changes first.  The period (month) field should be at the top of your spreadsheet (outside of the body of the fixed asset schedule).

In the past you have probably created spreadsheets that have largely been laid out for you.  This exercise is a bit more realistic.  Management tells you what data they need and you create a spreadsheet that meets their information needs.  In this exercise, you basically need data to support the balance sheet and income statement with regards to fixed assets and depreciation.

On the day that this assignment is due, you will bring your laptops to class. You will make five INDEPENDENT changes (or so) to the data in your spreadsheet.  You will record certain figures from your spreadsheet on the quiz paper.  The data you have been given contains some errors.  You will make those corrections during the in-class “quiz.”

YTD = year-to-date.  Virtually all companies prepare monthly financial statements. In addition to preparing an Income Statement for the current month, they typically prepare (along side it) an income statement for all of the months so far this year (YTD).

Required (to turn in):

1. Print on one sheet of paper (probably landscape) your fixed asset schedule with all pertinent data.  Submit your file called Little_Your_Name.xls on Bb (prior to class).  I should be able to read your hard copy (the print should not be microscopic, fyi)