Fixed Asset Analysis
Big Publishing Co.
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
|Furniture & Fixtures||1630|
|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|
|Capital Lease Asset||1660|
|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.
- 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).
- 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.
- 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).
- 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):
- 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)