Calculating MoQ Against a Forecasted Demand

%3CLINGO-SUB%20id%3D%22lingo-sub-1421594%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20MoQ%20Against%20a%20Forecasted%20Demand%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1421594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F681309%22%20target%3D%22_blank%22%3E%40df_knapp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DCEILING(B1%2FA1%2C1)*A1%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1421766%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20MoQ%20Against%20a%20Forecasted%20Demand%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1421766%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20worked!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20response%20and%20the%20solution%2C%20Jos.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1421833%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20MoQ%20Against%20a%20Forecasted%20Demand%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1421833%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20be%20simplified%20a%20little%20to%20read%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20CEILING(Forecast%2C%20MoQ)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1421470%22%20slang%3D%22en-US%22%3ECalculating%20MoQ%20Against%20a%20Forecasted%20Demand%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1421470%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20assistance%20in%20calculating%20a%20Minimum%20Order%20Quantity%20(MoQ)%20against%20a%20Forecast%20when%20the%26nbsp%3BForecast%20is%20greater%20than%20the%20MoQ%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20Example%3A%3C%2FP%3E%0A%3CP%3ECell%20A1%20contains%20an%20MoQ%20of%20%2275%22%3A%20Cell%20B1%20contains%20a%20Forecast%20of%20%22100%22%3A%20Cell%20C1%20contains%20Order%20Quantity%3C%2FP%3E%0A%3CP%3ECell%20A2%20contains%20an%20MoQ%20of%20%22150%3A%20Cell%20B2%20contains%20a%20Forecast%20of%20%22500%22%3A%26nbsp%3BCell%20C2%20contains%20Order%20Quantity%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20know%20that%20C1%20result%20is%20150%20and%20C2%20is%20600%2C%20but%20I%20want%20a%20flexible%20formula%20that%20calculates%20this%20without%20my%20having%20to%20change%20it%20for%20every%20cell%2C%20since%20my%20MoQ's%20vary.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnybody%20with%20any%20ideas%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHopefully%20this%20makes%20sense.%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1421470%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

I need assistance in calculating a Minimum Order Quantity (MoQ) against a Forecast when the Forecast is greater than the MoQ:

 

For Example:

Cell A1 contains an MoQ of "75": Cell B1 contains a Forecast of "100": Cell C1 contains Order Quantity

Cell A2 contains an MoQ of "150: Cell B2 contains a Forecast of "500": Cell C2 contains Order Quantity

 

I know that C1 result is 150 and C2 is 600, but I want a flexible formula that calculates this without my having to change it for every cell, since my MoQ's vary.

 

Anybody with any ideas?

 

Hopefully this makes sense. Thanks in advance.

4 Replies
Highlighted

@df_knapp 

 

Hi,

 

=CEILING(B1/A1,1)*A1

 

Regards

Highlighted

Hi @Jos_Woolley 

 

It worked!

 

Thank you for the response and the solution, Jos.

 

Dan

Highlighted

@Jos_Woolley 

This can be simplified a little to read

= CEILING(Forecast, MoQ)

Highlighted

@df_knapp 

 

You're very welcome!