Twyla Sands, owner of Cowboy Burger Company has contacted your accounting firm to review her fixed asset and accounts receivable records. Cowboy Burger is the home of the “wildest burger variety in the world” and specializes in serving all-organic beef and locally sourced food with a Western flair. The company has restaurants throughout the United States and divides its reporting into six regions (South, Mid-Atlantic, Southwest, Northeast, West, and Midwest). Twyla purchased the business a little over a year ago and is interested in better understanding the fixed assets owned by the company as well as the company’s current accounts receivable. Relevant records have been provided to you through
12/31/2020.
Overall requirements
For each of the following requirements, create a new pivot table in a new worksheet. Name each new worksheet as “Req 1,” “Req 2,” etc. Format dollar amounts using the accounting format with zero decimal places. Format non-currency numbers using the number format with zero decimal places. Except for Requirement 9, you should prepare a separate Pivot Table to address each requirement below. 

You may use a new Pivot Table to address Requirement 9, but one is not required.
Once you’ve completed your analysis, you will deliver a presentation of approximately five minutes to explain your findings and insights to Twyla. Unless there are extenuating circumstances, I expect you to deliver your presentation using a slide deck and capture your presentation in a professional way. You have two options: (1) record your computer screen using an online program (e.g. Canvas Studio) or program installed on your computer (e.g. Camtasia) or (2) display your presentation on a TV or projector screen and video both yourself and the screen. Please don’t just submit a recording of your screen using your phone or an external camera. Not sure how to do this? Just ask.
When designing and executing your presentation, consider the following:
1. How can you most efficiently covey your findings to Twyla? Consider that she is not an accountant and is very busy. She needs insights that are delivered in plain language and get
directly to the point. Hint: consider using graphical depictions of your findings.
2. You aren’t able to meet in person with Twyla because of the COVID-19 pandemic. Even so, you need to communicate with her in a way that helps maintain your personal connection with her.
For example, how do you demonstrate your enthusiasm and interest in her business? How can you be as physically “present” as possible without actually being in the same room (hint: capture your camera as part of your recording and let your enthusiasm shine through the computer
screen).

Fixed asset analysis
Twyla has provided you with a spreadsheet containing the company’s fixed asset data and has asked that your firm prepare a fixed asset schedule and answer various questions concerning the company’s fixed assets.
In addition to the fixed asset data, Twyla included the following information from the company’s fixed asset policies and procedures manual.
Fixed asset categories are defined as follows:
• Land - Vacant land parcels purchased for building sites, parking lots, or other purposes.
• Land Improvements - Site improvements (other than buildings) such as fencing and parking lots.
• Buildings - Permanent structures (including permanently attached fixtures).
• Equipment - Self-explanatory.
• Furniture - Self-explanatory.
Data dictionary
• RegionalArea: Indicates the regional location of the fixed asset.
• AssetID: This field is a sequential number assigned at purchase. This is a unique identification.
• AssetCategory: Indicates the fixed asset category.
• InSvcDate: This is the date the asset was put into service.
• Cost: The purchase price of the asset.
• SalvageValue: The salvage value assigned by the company.
• DeprMeth: The depreciation method used for the asset. NoDep indicates that no depreciation is
being calculated. SL indicates the asset is being depreciated using the straight-line convention.
• Useful Life: The useful life assigned by the company.
• BegAccumDepr: Beginning accumulated depreciation as of 1/1/2020.
• CurrentYearDepr: Current year depreciation for 2020.
Requirements
1. Prepare a fixed asset schedule that contains the totals for cost, beginning accumulated
depreciation, current year depreciation, ending accumulated depreciation, and ending book
value by regional areas. Create a calculated field for Ending Accumulated Depreciation and
Ending Book Value. What is the total ending book value for each region? How much
depreciation was taken during the year for the entire company?
2. Which region(s) have land? Use a slicer to determine the answer.
3. Twyla is concerned there might be some errors in the data for the Northeast. Prepare a fixed asset schedule (similar to requirement 1) for the Northeast by asset category. Use the slicer to Identify the asset ID which has the error and explain the error involved as part of your presentation. Do not correct the error at this point. (Hint: Land is not depreciated. Don’t worry about checking any calculations.)
4. What is the total cost of assets purchased by asset category in each year?
5. What is the total cost of assets purchased by region in each year?

6. What is the remaining average life (in years) for fixed assets by category in each region? Hint: there are multiple ways to obtain this data. You may find the most straightforward way is to go back to the main FixedAssets sheet and use formula functions such as an IF statement and first determine the remaining life (if any) for each asset.
Accounts Receivable Analysis
Like many restaurants, Cowboy Burger has suffered declines in revenue during the COVID-19 pandemic.
In an attempt to offset this, Cowboy Burger has experimented with offering catering and pre-packaged food items to businesses near its corporate headquarters. Unfortunately, those customers have not paid amounts due promptly and Twyla is considering whether to discontinue the program. To evaluate the collectability of accounts receivable, Twyla has asked for help analyzing how long the remaining receivables have been outstanding. Why? The longer the receivables go unpaid, the less likely they will ever be collected. Aging receivables helps the company determine the appropriate bad debts expense to record, and thus, improves the estimate for the allowance for doubtful accounts valuation. Prepare an analysis to answer the questions below. However, when you prepare your presentation for Twyla, be sure to consider that she’s only provided information on accounts with balances due at year-end. In other words, you don’t have the entire population of sales made on credit by her business.
Data dictionary
• Customer – business purchasing products and/or services
• InvoiceAmount – original amount purchased as part of the sale
• InvoiceDate – date goods or services were provided (invoices are due upon receipt)
Requirements
7. Age the receivables from the date of sale in six 30-day buckets including 1-30 days, 31- 60 days, 61-90 days, 91-120 days, 121-150 days, and 151-180 days. Assume you are
preparing the schedule on 12/31/2020. What is the total dollar value and number of accounts that fall into each category? What is the total amount due in each category?
Hint: before creating a pivot table, add a column in the AcctRec sheet to calculate days each transaction is past due. Remember, we’re evaluating data as of 12/31/2020.
8. Twyla wonders if the average size of each purchase may cause an account to pay more slowly. Provide an analysis based on the average invoice amount. Additionally, examine the
correlation between invoice amount and days past due. What does this analysis suggest? Add a sentence or two to your Req8 sheet to explain.
9. Twyla is considering requiring purchases of a certain size to be prepaid. She feels accounts paid within 60 days or less are acceptable and is considering requiring
prepayment for accounts that are likely to take more than 60 days to pay. Based on the data provided, what advice can you give her? Hint: Use your analysis above and the
forecast. linear function or regression analysis to investigate. Add any additional analysis and your conclusions to your Req9 sheet. When you present this analysis to her, alert her to any other issues she might also consider if making this policy change. For example: What would be the potential revenue impact to such a change? Are there limitations to this analysis? Should additional analysis be evaluated? If you can provide that analysis with the data available, do so. If not, what additional data would be required? 

Hint: your analysis may not support your original expectations.

Answer rating: 100% (QA)

Req 1 The first requirement is to prepare a fixed asset schedule that contains the totals for cost beginning accumulated depreciation current year depreciation ending accumulated depreciation and endi... View the full answer