90.2K

Verified Solution

Link Copied!

Data & Analytics for Accounting and Finance Professionals

Fixed Asset Book$/$Tax Depreciation Example Project

Introduction

This project will provide tax professionals with an introduction to the Alteryx APA Platform.

Alteryx is an analytic process automation tool that can be used to automate time$-$consuming

Microsoft Excel and Google Sheet activities. Alteryx is used by the Big $4$ Public Accounting firms

and many fortune $500$ companies. Familiarity with the software is a positive differentiating factor

for potential candidates looking to work at the Big $4.$

Learning Objectives

$1.$ Create an Alteryx workflow

$2.$ Clean and format data into an easy$-$to$-$read table

$3.$ Filter data to eliminate duplicate results

$4.$ Use Alteryx to create an excel sheet

$5.$ Manipulate existing data to create new columns

$6.$ Utilize an existing Alteryx workflow on new data

Prerequisites

In order to complete this exercise you will need the following:

$1.$ Access to a computer which uses Windows OS

$2.$ Access to the internet $($to Install Alteryx & follow the instruction video$)$

a$.$ Alteryx is free for Educators, Students, and Small Nonprofits

i$.$ Academics: Click here

ii$.$ Students: Click here

b$.$ Alteryx offers a $1-$month free trial

i$.$ Free trial: Click here

$3.$ The attached data set

a$.$ Download data: Click here

$4.$ Access to Youtube to follow the walkthrough videos on building the Alteryx workflow.

a$.$ Lesson Playlist: Click here

Data Set Background

Cash Hillam, CFO of DriveCog, a car rental company, has contacted your accounting firm to

review their fixed asset records. DriveCog is a national car rental company that specializes in

renting self$-$driving cars. The company operates throughout the United States and splits its

business across six subsidiaries. For tax purposes, DriveCog consolidates these subsidiaries on

its annual tax return.

Olu Amudipe $$ [email protected]

John Richey $$ [email protected]

Cash has provided you with a spreadsheet containing the company$$s fixed asset data. Cash has

requested that you calculate the company$$s annual tax depreciation to assist with preparing

their Form $4562$ and answer a few questions Cash has about the fixed assets.

Note: This data analytics activity uses an anonymized fixed asset record from a small company

for the years of $1991-2017$ and contains approximately $3,000$ records

Data Dictionary

Tax Year: The net tax basis of the asset during the given year

Entity: Indicates the subsidiary which owns the fixed asset

AssetID: This field is a sequential number assigned at purchase. This is a unique

identifier.

AssetDescription: Indicates the fixed asset category.

InSvcDate: This is the date the asset was put into service.

AcquiredValue: The purchase price of the asset.

DeprMeth: The depreciation method used for the asset.

$$ NoDep $-$ indicates that no depreciation is being calculated.

$$ MF $-$ MACRS Formula $(100\%,150\%,$ and $200\%)$

$$ MA $-$ MACRS Formula $(100\%,150\%,$ and $200\%)+168($k$)$ allowance

$$ ADS $-$ ADS Straight$-$line MACRS

$$ SLMM $-$ Straight$-$line Mid$-$Month

$$ SFFM $-$ Straight$-$line, Full Month

$$ SBFM $-$ Straight$-$line Full Month $+168($k$)$ allowance

EstLife: The tax useful life.

Recovery Period: The period during which a company depreciates an asset.

Requirements

For each of the following questions create a new worksheet in the workbook. Name each new

worksheet as $$Question $1,$Question $2,$ etc. Format the dollar amounts in each chart or table

to two decimal places. Format non$-$currency numbers to zero decimal places.

Please be aware the dataset used in the video examples is different from the dataset you

are being provided with. As a result your answers will be different then the answers you

see in the video.

Olu Amudipe $$ [email protected]

John Richey $$ [email protected]

$1.$ Generate an excel sheet showing all of the duplicate assets to share with DriveCog$$s

CFO.

For all remaining questions exclude the duplicate assets you found above, unless

specifically directed to do otherwise

$2.$ Prepare a fixed asset schedule that contains the totals for cost, beginning accumulated

depreciation, current year depreciation, ending accumulated depreciation, and ending

book value for each in service year for each asset.

$3.$ What is the end of year book value for Asset $1128$ in Entity $1$ in $2003?$

$4.$ What is the total current year depreciation for entity $1$ for tax year $2018?$

$5.$ Which entity had the most depreciation in $2020?$

$6.$ What is the total current year depreciation for DriveCog in $2020?$

$7.$ Cash sent back a cleaned up version of the duplicate assets. What are the answers to

questions $1-6$ for just these duplicate assets? $($you can reuse the workflow you built to

answer questions $1-6)$

$-$ Save each answer as a separate sheet in the workbook. Name each sheet

$$Question $7\mathrm{.}1,$Question $7\mathrm{.}2,$Question $7\mathrm{.}3,$ etc.

$-$ Tip: Be sure both input data tools are connected to the duplicate asset workbook

before running the workflow.

$-$ Tip: Be sure to change all o

Answer & Explanation Solved by verified expert

See Answer