### How JustAnswer Works:

• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee

## Ask Jess M. Your Own Question

Jess M., Computer Support Specialist
Category: Computer
Satisfied Customers: 6395
Experience:  Computer Hardware & Software Specialist - 10yrs Exp, Web Designer & Developer
49766785
Type Your Computer Question Here...
Jess M. is online now

# I'm using excel and need help with the following in an

### Resolved Question:

Hi, I'm using excel and need help with the following in an inventory list.
1) If the quantity on board is added, deduct the value from quantity on order
2) When quantity on board is deducted add this to quantity in stock
3) The quantity on board (unit price per item) is increased, add to stock value - this will be a currency value based on items x unit price + to stock value.
Any help is much appreciated.
Thanks
Nadine
Submitted: 1 year ago.
Category: Computer
Expert:  Jess M. replied 1 year ago.
Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.I am very sorry about your issues. Can you you please send me a sample or dummy Excel file with few sample data that we can work on? You can attach the sample file using the paper clip icon in the reply box below.Please let me know so that I can help you further.Best regards,Jess
Customer: replied 1 year ago.

Attachment: 2015-09-06_122147_dummyexcelfile_sep15.xlsx

Hi Jess

File attached for info.

Regards

Nadine

Expert:  Jess M. replied 1 year ago.
Hi Nadine,Thank you for the sample file. Please give me a moment to check your requirements.
Expert:  Jess M. replied 1 year ago.
Hi Nadine, I want to clarify things first. So your only data entry point in the sample file is H3. You entered 5 in H3 and you want 5 to be deducted from G3 and 5 to be added to F3.Then Available Stock is computed in J3 by adding F3 and H3 and Excel will compute K3 by multiplying J3 and the landed cost.Is this the entire flow of your calculations?Please let me know so that I can help you further.Jess
Customer: replied 1 year ago.

Hi Jess

Thanks for getting back to me. I'm in the UAE hence the delay in getting to you. My response to your queries are given below.

1. That's right and also to be added to J3.
2. Correct.
3. At this moment, yes. We will input from stock take what goes into F3.

Regards

Nadine

Expert:  Jess M. replied 1 year ago.
Thank you for writing back with that information. I am from another time zone so I just started my day (Monday). Please give me a moment to work on your requirements.Jess
Customer: replied 1 year ago.

Hi Jess

Wondered how you were getting on with the queries.

Regards

Nadine

Expert:  Jess M. replied 1 year ago.
Nadine,Thank you for writing back. I have completed the file requirements but I believe there is something missing. Please tell me the following:In F3 (Quantity in Stock), you want to add the value you enter in H3 (Quantity on Board). But is this G3 a blank cell originally? Or does it contain a value that is a result of a formula or calculation?Also, in like manner, in G3, you want to subtract the value in H3, but is this G3 cell originally blank? Or holds a value that is a result of some calculation?I am asking these because I believe you need a dynamic data that automatically updates when you enter a value in H3.
Customer: replied 1 year ago.

Hi Jess

1.In F3 (Quantity in Stock), you want to add the value you enter in H3 (Quantity on Board).
But is this G3 a blank cell originally? Or does it contain a value that is a result of a
formula or calculation?

A: My mistake. We want to add the Quantity on Board (H3) to Available Stock (J3).
Quantity on Order (G3) is manually input at the point of raising a purchase order.

2.Also, in like manner, in G3, you want to subtract the value in H3, but is this G3
cell originally blank? Or holds a value that is a result of some calculation?

A: We need to add Quantity on Order (G3) to Quantity on Board (H3).

We would like anything that is deducted from Available Stock (J3) to be added to deducted from
Quantity in Stock (F3).

Thanks again,

Nadine

Expert:  Jess M. replied 1 year ago.
Nadine,Thank you for writing back with that information. I am almost complete with your inventory file but let me clarify things first. I better do it by definition. Also, I am asking these questions because I want to exactly know what COLUMNS require you to enter values manually (data entry cells) and which columns are CALCULATED or results of formulas.Col J (Available Stock) = the sum of Quantity on Stock and Quantity on BoardCol H (Quantity on Board) = data entry cellsCol G (Quantity on Order) = data entry cellsCol F (Quantity in Stock) = Quantity on Board + Available StockIs that correct?
Customer: replied 1 year ago.

Hi Jess

This is what we've worked out from this side.

a) Quantity on board (H3) + Quantity in stock (F3) = Available stock (J3) (includes stock at sea)

b) Once the goods have landed, they will be deducted from Available Stock (J3) and need to be added to Quantity in Stock (F3).

c) We think we are missing a step. Anything that is in Pending Orders (I3) should be deducted from Available Stock (J3).

Hopefully that should clear things up.

Thanks again,

Nadine

Expert:  Jess M. replied 1 year ago.
Nadine,Thank you for the clarification and I believe I am seeing it clearly this time. Let me tell you what I understood about your process to confirm, so that I can re-create your inventory sheet based on your data and requirements. Yes, I mentioned "re-create" because we might need to add some "helper columns" to hold calculation data in order to derive your needed information.So Available Stock is the sum of Quantity On Board and Quantity In Stock. You mentioned "when goods have landed", are you referring to the Quantity on Board? Are these the goods that "land"? If yes, then, when the "quantity on board" lands, it will be deducted from Available Stock and then ADDED to Quantity in Stock. Let us take an example.Product A:Quantity On Board = 25Quantity in Stock = 50Therefore, Available Stock = 75Now, when this 25 items that are "on board" lands, this is what happens:Quantity On Board = 0Quantity in Stock = 75Available Stock = 50Is that correct?Also, please tell me more about "Pending Orders" and "Quantity of Orders". Do you manually enter the value for Pending Orders? Or are these referring to some values in your columns like "Quantity of Orders"? Keep me posted.Jess
Customer: replied 1 year ago.

Hi Jess

When goods have landed, yes, this is quantity on board which has been added into the available stock minus pending orders. Once landed it becomes quantity in stock. Point 2 is correct. Yes, it will be as described.

In the example, no. This is because quantity on board (25), quantity in stock (50), however, this may be on two containers. Therefore, may possibly only (15) pieces are landed. So, quantity in stock becomes (65) quantity on board becomes (10) but available stock remains (75).

Hope this clarifies things.

Thanks

Nadine

Expert:  Jess M. replied 1 year ago.
Nadine, thank you for the clarification. So it means that quantity on board IS NOT always equal to landed quantity. Please give me some time to rebuild your inventory sheet. Thanks. Jess
Customer: replied 1 year ago.

Hi Jess

I've just received a 'rate the service' email but haven't received the rebuilt inventory sheet as mentioned above. Can you confirm if I'm missing an answer.

Thanks and regards

Nadine

Expert:  Jess M. replied 1 year ago.
Hi Nadine, Please disregard this email yet -- please allow me to finish your worksheet first so that you can rate me positively. I will be sending you the final file in a moment.Jess
Expert:  Jess M. replied 1 year ago.
Hi Nadine,Thank you for patiently waiting. I have completed the recreation of your Inventory Sheet with few additions which I will explain here.First, I have created 2 sheets for the 2 options. In either options, I added "helper" columns to keep track of the values that you want to monitor. I am referring to the Quantity in Stock (QS) and the Quantity on Board (QB) values. I named the "helper" columns as "Actual QS", "Actual QB" and "Quantity Landed".Here is how it works and why you need these helper columns:For instance, for Product A your QS is 50 and QB is 25. Available Stock (AS) is 75 assuming there are no pending orders to subtract to.Now, if 15 of the QB landed, the original or raw QB must be retained and the updated (current, actual or running are appropriate terms) QB will then become 10. Also, QS will be retained and the new or updated QS will be 65. While the AS will remain 75 since there are no orders.If we do not use helper functions for there, there is NO WAY that we can modify QS and QB since these values are manually entered -- they are not results of formulas.Now regarding the 2 worksheets, I suggest Option 2. In this option, before any items land, QS and Actual QS are the same. The same with QB and Actual QB. Once you enter the Quantity Landed, that is when the calculation begins. In option 1 though, you need to enter 0 in all cells to show the calculation results, otherwise, they are blank.Please see the attached final file. I hope that helped.Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.Thank you!Best regards,Jess
Customer: replied 1 year ago.

Hi Jess

Many thanks for this. I need to look over it but it's the weekend here at the moment. Please give me a bit of time before I rate it. I'm sure it's absolutely fine. Thanks again and I'll get back to you as soon as (hopefully tomorrow) with the rating.

Best regards

Nadine

Expert:  Jess M. replied 1 year ago.
Hi Nadine,Thank you for writing back and I am glad you got the modified file I sent you. Yes, please take your time and check the file and see if it fits your requirements. If you need further clarification with that file I gave you, please let me know.Best regards,Jess
Customer: replied 1 year ago.

Hi Jess

Completely aware that I haven't rated your service as yet. It's the weekend here and I haven't had a chance to look through what you sent in details. I hope to get back to you tomorrow, or at the very latest Monday.

Thanks for your patience.

Regards

Nadine

Expert:  Jess M. replied 1 year ago.
Hi Nadine, Thank you for writing back. Please take your time. If you received an email inviting you to rate me, that is system generated. No rush, you can rate me when you have tested the file I gave you. Thank you.Best regards, Jess
Jess M., Computer Support Specialist
Category: Computer
Satisfied Customers: 6395
Experience: Computer Hardware & Software Specialist - 10yrs Exp, Web Designer & Developer
Jess M. and 6 other Computer Specialists are ready to help you

### What Customers are Saying:

• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.

• ### Kamil Anwar

#### Satisfied Customers:

156
8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
< Previous | Next >
• http://ww2.justanswer.com/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg Kamil Anwar's Avatar

### Kamil Anwar

#### Satisfied Customers:

156
8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
• http://ww2.justanswer.com/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

### James K.

#### Satisfied Customers:

93
Technical Director of IT Company
• http://ww2.justanswer.com/uploads/JO/JonTech/2011-10-25_111835_A2c.64x64.jpg Jon-Tech's Avatar

### Jon-Tech

#### Satisfied Customers:

61
20 yrs: Computers, Networking
• http://ww2.justanswer.com/uploads/JA/jamieratliff/2012-2-8_194551_jamie.64x64.jpg jamieratliff's Avatar

### jamieratliff

#### Satisfied Customers:

21
Jamie has worked in the Information Technology field for over a decade.
• http://ww2.justanswer.com/uploads/JA/JACUSTOMERleg4q7o8/2012-3-6_203036_meja.64x64.png TheDoctor's Avatar

### TheDoctor

#### Satisfied Customers:

16
Experienced Software Engineer
• http://ww2.justanswer.com/uploads/FI/FisherEngineering/2012-1-18_194422_Fisher375Headshotcompressed239K.64x64.jpg Bill Fisher's Avatar

### Bill Fisher

#### Satisfied Customers:

1
Computer system design, networks, integration, general support.
• http://ww2.justanswer.com/uploads/ZE/zeyank/2014-12-9_221254_zeyank.64x64.png Ryan H.'s Avatar

### Ryan H.

#### Satisfied Customers:

1739
A+ Certified Technician - 10 Years experience working with all types of computer systems.