Cowboy Burger Company 80 points Data set background 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 companys 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 PivotTable to address each requirement below. You may use a new Pivot Table to address Requirement 9, but one is not required. Once youve 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 dont just submit a recording of your screen using your phone or an external camera. Not sure how to do this? Just ask. Were happy to help 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 arent 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 companys fixed asset data and has asked that your firm prepare a fixed asset schedule and answer various questions concerning the companys fixed assets. In addition to the fixed asset data, Twyla included the following information from the companys 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 identified. 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. Dont 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 shes only provided information on accounts with balances due at year-end. In other words, you dont 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, were 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 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 a 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.