Design of table for Product Market Cost Calculate

[avk]

I have create sheet of subject. But i m not satisfied of design. Using excel 2007.

Basically, i want to know how many product wise & date wise enquiries receive at my deskshop. In respective column i just input total number of quantity which i have receive enquiries.

After input of data I calculate on week basis, monthly basis in other sheets, how many enquiries receiving at our deskshop.

Actually i required all data record productwise & datewise in one singal table.

Also how to calculate total product market cost (PMC). Is their any formula to calculate PMC.

Your valuable suggestion most welcome.

[TechRetard]

avk, it looks like you are going to need an individual sheet for each month. Just because you change the date up top doesn't mean the data is going away.

The first thing that runs through my head is Access DB...(create a form to enter data, export to excel to run pivot table or just run a query by date or product, etc) but I could be wrong

[MarvinP]

Hi avk,

Take all that date stuff off the top and have a single date in Col G. In Col H have a Number that came in (bought?).
Get rid of the grouping (outline) stuff on the left of your sheet1 also.

Use you existing table as a validation lookup table to fill in a table sheet.
The table needs these columns
PartNo, Desc, Series, Type, Date, Quantity

Then do a Pivot table by date to count by month, day etc
You can also filter by PartNo or by Type or Series etc.

I suggest you look into the DTPicker for entering your dates. I'd also try to automate some way to quickly add/find parts.

How do you know what comes across your desk. Do you have the PartNo or do you have to look up the Description? Would filtering by Type let you find the part faster?

[avk]

Mr. MarvinP
I read your valuable suggestion. Let us more clear idea explain.

1] I have using this table life long.

2] From this table i calculting how many enquiries in quantity (Part number wise & datewise) we have receive from customer.?

3] From this data we have calculate monthly potential of particular Part number. How many quantity enquire from customer?

4] After that i m tried to how much quantity fulfill against enquire quantity? And how many quantity available in my stock on that particular date.

5] How many days we have not receiving enquiry & on that period how many quantity available in my stock.

6] I m tried to show all above data result in single sheet. (i.e. monthly summary report)

In view of above let me know the your valuable view. If possible post example file.

[MarvinP]

Hi avk,

I think I had to show you what I meant.
1. I used your sheet1 and built what I wanted on Sheet3.
2. I changed your formula on Sheet1 in Row3 so it uses real dates (see formula in 4G - 4AK
3. I wrote VBA code called MakeFakeCallsOnSht1 to make random calls on your sheet
4. I ran the above code to put call numbers on your sheet1
5. I wrote VBA code called MoveSht1DataToSheet3 to move calls from sheet1 to sheet3, like I wanted to see the data
6. I changed the month on sheet1 and did the above for a few months to build up some data on sheet3

NOW - On sheet3 with the data like I wanted it, I used Pivot Tables and gave you two examples.
The first grouped by date is the number of calls by day and the part no. You can collapse the day and see calls by month just as easily. You can also filter what you see by Type or Series.
The second shows the minimum quantity of the parts and the sum of calls about that part.

The reason I did this example is to show you how you might use Pivot Tables on you data if it arranged in a TABLE like sheet3. There can be many more pivot tables to answer many more questions about your parts, calls, dates and quantity in stock.

See the Attached on Sheet3 and use the filters above the Pivot Tables. Then read a little about Pivot Tables and see how much more can be done with your data in this kind of TABLE format.

I hope this helps.

[avk]

Hellow MarvinP:
I have attach modified file for your ready reference. Since last week i also tried out to make data table and now i m going on track.

In result sheet : I m trying how to calculate interval days between two enquies & their avg. i.e. non enquiries days. Also i know about regarding calculate monthly potencial.

Secondly, how to calculate regarding safety stock. (on monthly)

Also can be possible all mentioned partnos. (approx 540 parts) to display in single sheet with all 365 days i.e. yearly (date,month & year). let me know.

[MarvinP]

Hi avk,

I thought my suggestion was to put all months on a single sheet, with a single date column. This then allowed Excel to treat it as a single table.

It looks like you decided to go to separate sheets for each month. Then you went for Index functions to build your Results tab. With your data on separate months, it seems hard to get this differnece in time between two pages. It would be much easier if all months were on the same page.

A Pivot table like I created in my example would allow you to easily show all part numbers.

I'm a little stuck on this question. You asked for our valuable suggestions and then reply with a rejection of the suggestion. I'm not angry or thinking my suggestion is the best but simply showing how I might do the problem, as I understand it. If your requirements are different than I assumed or the results don't meet what you need, then certainly, don't use them.

[avk]

Hi MarvinP,

Firstly i clarify you, my intention is not at all to hurt you or reject your suggestion. I just tried out of different way.

Yes, what you say Pivot table is a easily show data what i required and go with your suggestion. But i just curiositily ask with you. If you hurt, i deeply say from bottom my heart "i m very very sorry".

[MarvinP]

Hi,

If I was Australian I'd say "No worries mate!".
You asked for a way to get your data to do more for you. I looked at your first attachment and realized the dates across the top kept it from being a good data structure to work with in Tables. I also saw that you had formulas to change the month or year so you could use the same worksheet over and over. This meant, each time you changed the month or year you needed to erase your data and start again. With this method, unless you saved sheets, you weren't going to be able to do history reports, which is what I thought you needed.

I suggested ... etc. You came back with a lot of sheets, one for each month, which got you closer to what I was suggesting, but kept it from being put in a Table or Pivot Table.

This is certainly your problem. You have experience with the data and how it comes to you. Sometimes our suggestions just simply don't work out for you, given your circumstances and/or environment.

All we can do is try to help. No problems if what we suggest doesn't fit.

That said, many times a problem is solved with a table and someone ask for it to be solved in a single cell formula. Or it can be solved with a formula but they want VBA Code to do the same thing. These secondary questions rub me wrong a little bit. One of the Forum Gurus has a signature line claiming there are many ways to solve these problems, just pick one. If you don't like the answer, try somethine else.