Hourly Fee - Efficient Way of Calculating Changing Rates Based on Many Input Variables

**Sharpster** · 03-08-2017, 06:12 AM

Hi All,

I have calculated hourly rates for outsourcing work using multiple input variables, which include individual item costs, individual operating costs, number of employees, time of project (months), salary (based on foreign language spoken), working hours, VAT rate, foreign currency exchange rates etc etc. Capital costs are one off purchases eg IT but I also need to include recurring monthly costs eg payroll. In all I have about 40 input variables - tell me about it!

I know how to solve and to display the results in 2 tables containing hourly rate by number of employees (5 - 20) and length of campaign (1, 3, 6 or 12 months). One table is for English speakers and the other is for speakers of European foreign languages. I would like to break foreign languages down further into 8 individual languages eg Spanish, Italian, German etc. My methodology is somewhat long-winded and ugly and I am convinced that a simpler, more elegant solution exists. I am dabbling with data tables, LOOKUP etc but I do not know VBA. I'm not worried at present about formula errors, formatting errors etc in the workbook as I will correct all of those when I have the neatest solution.

The workbook link is here:

https://1drv.ms/x/s!AiSzlYnQ8Qa2gdNu1cTv6ZONaedPpA

and any guidance to help turn my sow’s ear of a project into a silk purse would be most welcome.

Many thanks in advance.

Sharpster

**dflak** · 03-08-2017, 06:17 PM

Please attach the workbook to the forum.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**Sharpster** · 03-09-2017, 02:26 AM

Hi dflak,

File attached as requested. Thanks for that and have a good one.

Sharpster

**dflak** · 03-09-2017, 02:06 PM

I am looking at the workbook and I can see formulas all over the place. It would take quite a while to reverse engineer what you are doing here. Is there any specific section or cells that need the help?

I am thinking that you might be able to use the indirect function for lookups. For example Cell E22 on the input sheet has the formula: =IF(E5=0, 0, European!I31).Cell E1 currently has "European". Suppose you had a validated dropdown list for cell E1 for German, Italian, French ... Then the formula in E22 could be =IF(E5=0, 0, INDIRECT(E1&"! I31") and in fact, this formula could be copied to cell C22 in which case it becomes =IF(C5=0, 0, INDIRECT(C1&"! I31") .

So all you will need to do is set up the lookup values on sheets named for the languages.

**Sharpster** · 03-10-2017, 05:35 AM

Hi dflak,

I did say the workbook was ugly

. I'll look at using the INDIRECT function so thanks for that.

I'm essentially okay with putting the workbook together, even if my solution isn't the neatest. I was really trying to find an efficient way to test various input values to then churn out a corresponding hourly rate. So, for example, the client wants to know the hourly rate for 5, 6, 7 or 8 English on X salary, 6 - 10 Germans on Y salary and 2 French on Z salary, calling for 6, 7 or 8 hours a day for 3-6 months. The calculated cost for each permutation, and hence the hourly rate, will be different, so I would ideally like to "automate" the "whatif" analysis if possible.

As you say, I think it might be easier to have a single sheet for each Language. I can then have the bulk of the calculations doe on each of those sheets.

I will continue to dabble

Thanks and have a great weekend.

Hourly Fee - Efficient Way of Calculating Changing Rates Based on Many Input Variables

LinkBack

Thread Tools

Rate This Thread

Display

Hourly Fee - Efficient Way of Calculating Changing Rates Based on Many Input Variables

Re: Hourly Fee - Efficient Way of Calculating Changing Rates Based on Many Input Variable

Re: Hourly Fee - Efficient Way of Calculating Changing Rates Based on Many Input Variable

Re: Hourly Fee - Efficient Way of Calculating Changing Rates Based on Many Input Variable

Re: Hourly Fee - Efficient Way of Calculating Changing Rates Based on Many Input Variable

Thread Information

Users Browsing this Thread

Similar Threads

Hourly Rates

Here's a challenge for the brainiacs!..Way beyond my excel skills I'm afraid.

Calculating wages from hours worked and hourly rates?

[SOLVED] hourly rates

Hourly rates

Calculating Daily Pay, Incorporating Variable Hourly Rates

Calculating Total Pay, Based on Hours Worked Per Day and Hourly Rates

Bookmarks

Bookmarks

Posting Permissions