1. Despite or perhaps because of talk about a potential recession, Wheels is finding that people are putting money into their vehicles, both sprucing them up and keeping them maintained. Industry experts believe that sales for this industry will be 5% more than in the prior year. Use5% for the Excel worksheet.

Cost of Sales

1. Industry trends indicate that cost of goods sold, as a percent of sales, will be 3% higher than in the prior year. (Take last year's cost of sales and divide by last year's sales. To this percent add an additional 3% to find the current year's cost of sales as a percent of sales.)

Depreciation

1. For this item, assume that depreciable assets increased by 10%. The prior year's gross depreciable assets were $660. Further assume that depreciation will go up by 10% if depreciable assets go up by 10%.

Interest Expense

1. The company maintained a line of credit during the year which shows a balance of $2,300 as of October 2023. Wheel's agreement with the bank is to pay 2% above the prime rate.

Operating Expenses

1. What patterns do you see with the other expenses? Do these patterns make sense? Can you think of other ways in which to estimate these expenses? Below are other questions and information to consider crexpectation of expenses at Wheels.

1. What was last year's commission expense as a percent of sales? Assume the rate stayed approximately the same as in the prior year and use 7% of sales.

1. Use a 4% increase for advertising expense in relation to the prior year.

1. For salaries use a 5% increase over the prior year.

1. Assume that payroll taxes are 10% of salaries and use this to compute your expectation. Would you also include commissions? (To keep the problem simpler, please exclude commissions from your calculation, if you want to match to the answer key.)

1. Employee benefit plans (e.g. health care costs) often vary in relation to salaries. Assume that the healthcare costs have gone up. They are 2% more than last year, bringing the estimate of employee benefits to 18% of salary. In other words, multiply salaries (determined above) times 18% to get your expectation.

1. For rent, take the pattern that you see.

1. Supplies often vary in relation to inflation. Let's say inflation was 6%.

1. Assume that utility consumption was the same as in the prior year, but overall utility costs have increased by 5%.

1. Assume legal and accounting expenses increase by 8%. How could we learn about legal and accounting expenses?

1. For miscellaneous, keep the pattern that you see.

Income Taxes

1. What is the corporate federal tax rate? To that rate, add an additional 9% for state taxes.

TranscribedText: Pathway where filed Maming convention Index Date: Preparec Wheels Auto Supply Comparative Income Statements Date: Reviewe: Expectations for purposes of Risk Assessment Years ending December 31, Year ending December 31, 2023 Expected 2020 2021 2022 2023 Basis for Summary of Signficant Assumptions expectation Assumption Audited Audited Audited Expected Sales, expected change from prior year 1 0.05 Sales 8,800.0 9,500.0 10,200.0 10,710.0 Cost of Sales Cost of sales 6,100.0 6,600.0 7,100.0 Depreciation 3 Gross profit Interest expense 4 Sales commissions S5a 0.07 Sales commissions 610.0 660.0 710.0 749.7 Advertising 5b Advertising 175.0 190.0 202.0 Salaries 5c Salaries 1,061.0 1,082.0 1,103.0 Payroll taxes 5d Payroll taxes 85.0 92.0 99.0 Employee benefits Se Employee benefits 167.0 174.0 181.0 Rent 5f Rent 60.0 610 62.0 Depreciation 60.0 63.0 66.0 Supplies 5g Supplies 26.0 28.0 30.0 Utilities Sh Utilities 21.0 220 23.0 Legal and accounting 5i Legal and accounting 34.0 37.0 40.0 Miscellaneous 5j Miscellaneous 12.0 13.0 14.0 Income Taxes 6 Interest expense 105.0 114.0 120.0 Income before tax Link your assumptions in column E to Column L, in a manner so that you can change the assumptions. After you select cell in column E, hit the F4 Incame taxes 99.0 113.0 126.0 key to make it static. This allows you to avoid typing a $ for both the : ' ' row and column in E4. NETEaE *In the real world, the accountant would be as expansive as necessary in explaining how he/she arrived at the assumption. For purposes of this assignment, your Word document will be your narrative summary of assumptions. You will not remember from year to year how you arrived at assumptions. You will appreciate your documentation! Further, in audit, the documentation allows other members on the audit team to Excel matters Consider naming a print range for your income statement, add comment with name Consider labeling your pathway Consider typical conventions with respect to single and double underlines, percent formatting and dollar formatting

Transcribed Image Text:

Pathway where filed Naming convention Wheels Auto Supply Expectations for purposes of Risk Assessment Year ending December 31, 2023 Comparative Income Statements Years ending December 31, Expected 2020 2021 2022 2023 Summary of Signficant Assumptions Basis for expectation Assumption* Audited Audited Audited Expected Sales, expected change from prior year 1 0.05 Sales 8,800.0 Cost of Sales 2 Cost of sales 6,100.0 9,500.0 6,600.0 10,200.0 10,710.0 7,100.0 Depreciation Interest expense Sales commissions Advertising 3 Gross profit 4 5a 0.07 Sales commissions 610.0 660.0 710.0 749.7 5b Advertising 175.0 190.0 202.0 Salaries 5c Salaries 1,061.0 1,082.0 1,103.0 Payroll taxes 5d Payroll taxes 85.0 92.0 99.0 Employee benefits 5e Employee benefits 167.0 174.0 181.0 Rent 5f Rent 60.0 61.0 62.0 Depreciation 60.0 63.0 66.0 Supplies Utilities Legal and accounting Miscellaneous Income Taxes 5g 5h 5i 5j 6 Supplies 26.0 28.0 30.0 Utilities 21.0 22.0 23.0 Legal and accounting 34.0 37.0 40.0 Miscellaneous 12.0 13.0 14.0 Interest expense 105.0 114.0 120.0 Income before tax Link your assumptions in column E to Column L, in a manner so that you can change the assumptions. After you select cell in column E, hit the F4 key to make it static. This allows you to avoid typing a $ for both the row and column in E4. *In the real world, the accountant would be as expansive as necessary in explaining how he/she arrived at the assumption. For purposes of this assignment, your Word document will be your narrative summary of assumptions. You will not remember from year to year how you arrived at assumptions. You will appreciate your documentation! Further, in audit, the documentation allows other members on the audit team to Income taxes Net income Excel matters Consider naming a print range for your income statement, add comment with name Consider labeling your pathway Consider typical conventions with respect to single and double underlines, percent formatting and dollar formatting. 99.0 113.0 126.0 Index Date: Preparec Date: Reviewer Pathway where filed Naming convention Wheels Auto Supply Expectations for purposes of Risk Assessment Year ending December 31, 2023 Comparative Income Statements Years ending December 31, Expected 2020 2021 2022 2023 Summary of Signficant Assumptions Basis for expectation Assumption* Audited Audited Audited Expected Sales, expected change from prior year 1 0.05 Sales 8,800.0 Cost of Sales 2 Cost of sales 6,100.0 9,500.0 6,600.0 10,200.0 10,710.0 7,100.0 Depreciation Interest expense Sales commissions Advertising 3 Gross profit 4 5a 0.07 Sales commissions 610.0 660.0 710.0 749.7 5b Advertising 175.0 190.0 202.0 Salaries 5c Salaries 1,061.0 1,082.0 1,103.0 Payroll taxes 5d Payroll taxes 85.0 92.0 99.0 Employee benefits 5e Employee benefits 167.0 174.0 181.0 Rent 5f Rent 60.0 61.0 62.0 Depreciation 60.0 63.0 66.0 Supplies Utilities Legal and accounting Miscellaneous Income Taxes 5g 5h 5i 5j 6 Supplies 26.0 28.0 30.0 Utilities 21.0 22.0 23.0 Legal and accounting 34.0 37.0 40.0 Miscellaneous 12.0 13.0 14.0 Interest expense 105.0 114.0 120.0 Income before tax Link your assumptions in column E to Column L, in a manner so that you can change the assumptions. After you select cell in column E, hit the F4 key to make it static. This allows you to avoid typing a $ for both the row and column in E4. *In the real world, the accountant would be as expansive as necessary in explaining how he/she arrived at the assumption. For purposes of this assignment, your Word document will be your narrative summary of assumptions. You will not remember from year to year how you arrived at assumptions. You will appreciate your documentation! Further, in audit, the documentation allows other members on the audit team to Income taxes Net income Excel matters Consider naming a print range for your income statement, add comment with name Consider labeling your pathway Consider typical conventions with respect to single and double underlines, percent formatting and dollar formatting. 99.0 113.0 126.0 Index Date: Preparec Date: Reviewer