Adjusting for acxounting errors, ommissions and expense analysis

The companys accountant has discovered the following error. In the Adjusted file, insert rows at appropriate places for any omission or include correct figure where it is incorrect in the Data sheet to make the following adjustments as needed:

(i) The accountant did not account for the environmental emission tax due on the companys use of factory utility. The taxable penalty is equal to 30% of annual factory utilities and should be reported as a separate factory overhead cost. You should modify your Data sheet to include this extra factory overhead and allow for the % change to be modified in future years.

Do not hard code this expense amount or add itet it off against the $40,000 of factory utilities for 2021. Do not use this information to adjust the companys income tax calculation.

(ii) The following costs, expenses, and revenues were omitted or not recorded correctly in the original Data sheet and hence, must be included/amended in your Data sheet in the appropriate place:

1. Plastic is required to undertake proper disposal of its manufacturing waste under the state environmental protection authority. It was charged $28,500 for using the service of EnviroPro Inc. for safe waste disposal during the year. This safe factory waste disposal cost was omitted mistakenly while computing factory overhead costs for the year.
2. Advertising expense was overstated by $8,000.
3. Interest expense was understated by $10,000.
4. The amount of revenue provided in the data sheet included $30,000 of unearned revenue.

(iii) The accountant wishes you to provide an analysis of the companys annual operating expenses. Add an extra worksheet called Expense analysis, and add your 2021 data next to the existing 2020 data in the format shown below: (In Picture of Expense Analysis)

You must link this new Expense Analysis table/worksheet (relevant cells in column E) with the respective data from your 2021 income statement. Do not hard code the 2021 data. Use appropriate formula to calculate the total expenses in cell E11, E13 and percentages in column F and G.

Modify your existing worksheets to account for these errors. Add the extra expenses, percentages figures to your Data sheet and check formula references to these in the appropriate financial statement worksheet. DO NOT HARD CODE THESE CHANGES IN THE INCOME STATEMENT.

QUESTION:

WHAT FORMULAS GET THE PERCENTAGES SHOWN IN THE EXPENSE ANALYSIS EXAMPLE?

2019 % of total expenses % of sales revenue A B D 1 Plastic Manufacturing 2 For the year ended June 30, 2021 3 Cost Analysis Report 4 5 2018 6 Type S % of total expenses % of sales revenue S 7 Costs of goods sold 1,200,000 86.83% 70.59% 8 Selling expenses 100,000 7.24% 5.88% 9 Admin expenses 80,000 5.79% 4.71% 10 Finance costs 2,000 0.14% 0.12% 11 Total expenses 1,382,000 100.00% 81.29% 12 13 Sales revenue 1,700,000 14 Data COGM COGS Income statement Expense Analysis B B D E 30-Jun-21 1-Jul-20 92,000 95,000 A 1 Plastic Manufacturing 2 For the year ending June 30, 2021 3 Data section 4 Inventories 5 Beginning raw materials inventory 6 Ending raw materials inventory 7 Beginning work-in-process inventory 8 Ending work-in-process inventory 9 Beginning finished goods inventory 10 Ending finished goods inventory 11 Total inventories 12 13 Direct factory costs incurred during period: 14 Raw materials purchased 15 Direct labor 88,000 75,000 95,000 275,000 110,000 280000 310,000 280,000 16 B C D E 17 other costs incurred during period 10 Rent factory 19 Rent corporate office 20 Utility costs corporate office 21 utility costs factory 22 Insurance and property taxes. Factory 22 Advertising expenses 24 Equipment maintence costs factory 25 indirect labor costs 26 Corporate office personells salanes 27 Sales representive salaries 20 Depreciation expense-factory equipment 29 Depreciation expense office equipment 10 Interest expense 31 Total cost and expense 60.000 22.000 10,500 40,000 24,000 52.000 45,000 172,000 162,000 0.000 110,000 35,000 10.000 340.500 G Factory or Selling Expenses Admin Expenses Finance Costs Total 60,000 22.000 10,500 40,000 24,000 52,000 45,000 172,000 162,000 38,000 110,000 25,000 30,000 451.000 140000 219500 30000 500 33 Sales revenue 34 Tax rate if pre-tax income/profit is less than 35 Tax rate if pre-tax income/profit is at least 36 17 1,850,000 100,000 400.000 20% 30% B D E 1 Plastic Manufacturing 2 For the year ending June 30, 2021 3 Costs of goods manufactured 4 5 Beginning raw materials inventory 6 Raw materials purchased 7 Total RM available for use 8 Less ending RM 9 RM used during the period 10 Direct labor 11 Factory Off 12 Total manufacturing costs incurred during the period 13 Add beginning WIP 14 Total manufacturing costs to account for 15 Less ending WIP 16 Costs of goods manufactured 17 18 92,000 310,000 402,000 (95,000) 307,000 280,000 451,000 1,038,000 88,000 1,126,000 (75,000 1,051.000 19 B A 1 Plastic Manufacturing 2 For the year ending June 30, 2021 3 Cost of goods sold 4 5 Costs of goods manufactured 6 Beginning finished goods inventory 7 Finished goods availiable for sale 8 Ending finished goods inventory 9 Cost of goods sold 10 1,051,000 95,000 1,146,000 (110,000) 1,036,000 A B 1 Plastic Manufacturing 2 For the year ending June 30, 2021 3 Income statement 4 1,850,000 1,036,000 814,000 140,000 219,500 5 Sales revenue 6 Cost of goods sold 7 Gross profit 8 Operating expenses 9 Selling Expenses 10 Admin Expenses 11 Total Operating expenses 12 Operating Income 13 Other Income and expenses 14 Finance Costs 15 Income before tax 16 Tax Expense/(Credit) 17 Net income for year 18 359,500 454,500 30,000 424,500 127,350 297,150