Question

Name Salary |Tax Rate(%) Tax Amount Tax Table Salary Tax J. Smithl 54,000 J. Smith2 65,000 J. Smith3 37,000 J. Smith444,000 J. Smith5 62,000 J. Smith642,000 J. Smith7 31,000 J. Smith8 88,000 J. Smith9 91,000 J. Smith10 52,000 Rate (%) 8 9 10 30,000 40,000 50,000 60,000 70,000 80,000 90,000 100,000 12 13 14 TOTAL TAX AMOUNT 1. USE the vlookup function to find the appropriate Tax-Rate (in the Tax Table) for each employee 2. Calculate the individual Tax-Amount 3. Get the TOTAL TAX AMOUNT of all Tax-Amounts 4. Use Solver to increase the TOTAL TAX AMOUNT = 70,000, and use the appropriate STEP-1

I really dont know how to use excel very well

so could somebody solve this on EXCEL for me and maybe give it to me through excel file THANKYOU

hopefully u can put all the functions in excel and give me the copy :(

Answer 1

Here for the first I have put the portion of data in my excel and try to solve for the V-lookup for the Tax slab rate and then multiplying the tax rates got after V-lookup with the Actual Salary to get the tax amounts and then making sum to get the total tax amount.

The screen shot is shown below with all the formulas:

Value is shown Formula Value is shown Salary Tax Rate% Tax Slab Tax Rate% Tax Amount Tax Amount J Smith1 J Smith2 J Smith3 」Smith4 J Smith5 J Smith6 J Smith7 J Smith8 J Smith9 J Smith10 10 VLOOKUP(C3,SJS4:SK$11,2,TRUE) 11 VLOOKUP(C4,SJS4:SK$11,2,TRUE) 8 VLOOKUP(C5,SJSA SK$11,2,TRUE) 9 VLOOKUP(C6,SJS4:SK$11,2,TRUE) 11 VLOOKUP(C7,SJS4:SK$11,2,TRUE) 9 VLOOKUP(C8,SJS4:SK$11,2,TRUE) 8 VLOOKUP(C9,SJ$A:SK$11,2,TRUE) 13 VLOOKUP(C10,SJS4:SK$11,2,TRUE) 14 VLOOKUP(C11,SJS4 SK$11,2,TRUE) 10 VLOOKUP(C12,SJSA SKS11,2,TRUE) 5400 D3 C3/100 150 D4 CA/100 2960 D5 C5/100 960 D6 C6/100 6820 D7 C7/100 780 D8 C8/100 2480 D9*C9/100 1440 D10 C10/100 2740 D11 C11/100 5200 D12 C12/100 61930 SUM(F3:F12) Salary Tax Rate % 37000 Total Tax Amounts

In the second part of the problem first we need to install solver in the excel if you are not having solver add in in excel.Go to excel options->Add ins->solver add in and install it .You will get a solver tab when you go to the data tab of the excel worksheet.

Now in the second option our objective is that the total tax amount is increase to $70000 from the old value obtained in the first sheet.

So the goal is to come to a target value for the column total if tax amount to $70000.

Constraint 1:We cannot have a negative rate in the tax rate slab.

Constraint 2:The salary amount of the persons should always be positive .

Now go to Solver and do the following changes and click ok.

F13 fSUM(F3:F12) Solver Parameters 3 Set Target Cel: Equal To: C) Max Sy Changing Cels: Value is shown Formula Value is shown F$13 Solve Salary Tax Rate% Tax Rate% Tax Amount Tax Amount ⓞyalue of: 70000 Close 」Smith! 54000 11 21916965 VLOOKUPIC3,SUS4:SK$11,2,TRUE) 65000 12.46751902 VLOOKUPIC4,SIS4K$11,2,TRUE) 37000 8.835356983 VLOOKUPICS,SISA:SK511,2,TRUE) 000 9.993397493 VLOOKUPIC6,SIS4:SK$11,2,TRUE 62000 12 39978738 VLOOKUPIC7,S 54 SK$11,2,TRUE 42000 9.948243062 VLOOKUPIC8,SJSA:SKS11,2,TRUE) 31000 8.699893688 VLOOKUPIC9,SIS4:K$11,2,TRUE) 83000 1498679499 VLOOKUP(C10,SIS4 SK$11,2,TRUE) 91000 16.05452663 VLOOKUP(C11,SJS4:SK$11,2,TRUE) 52000 11.17401522 VLOOKUP(C12,SI$4 SK$11,2,TRUE) 6058.351611 D3 C3/100 8103.887366 D4 C4/100 3269.082084 D5 C5/100 4397.094897 D6.C6/100 7687 868 174 D7.C7/100 4178.262086 D8 C8/100 2696.967043 D9*C9/100 13188.37959 D10 C10/100 14609.61924 D11 c11/100 5810 487914 D12 C12/100 J Smith2 sDS3:SD$12 Guess Subject to the Constraints: Options J Smith4 SC$3:$C$12-0 J Smith6 」Smith7 Change Reset All J Smith9 」Smith 10 Total Tax Amounts 70000 SUM(F3:F12) To increase the total Tax Amount to $70000 Constraint: The tax rates cannot be negative Constraint The salary for all the persons should be positive

Here you can the following values have been inserted with the constraint in order to achieve a total tax amount of $70000 with the following changes in the tax rate column.

The screen shot shows that and followed by result.