could you please work this out step by step i am a beginner and do not know where to begin ckg a

Step 1: Open the workbook named Parts.xlsx and save it as CKG Parts Analysis_yourname.xlsx.

Question

CKG Auto Data files needed: Parts.xlsx and Demand.xlsx CKG Auto's business supplies parts for auto repairs. The most critical component of the parts supply business is having enough of the right parts on hand so that repair shops can receive same-day delivery. The key to profits is minimizing the number of parts that need to be warehoused while also ensuring that sufficient parts are on hand to meet orders. Because each warehouse distribution center serves a different set of customers with different needs, each center must be considered separately. Distribution centers have a five-day lead time for ordering parts. The lead time must be taken into consideration when determining target inventory levels. The cost involved in warehousing the parts includes the working capital tied up in the inventory (cost of each part) as well as the space to store the part. These costs can be substantial. On the other hand, alternative 'generic' parts are often available from rival suppliers, and keeping the auto repair centers supplied and customers satisfied is critical. One of the most problematic items to supply and store are bearings. This part is both high volume and relatively large, taking up considerable warehousing space. A bearing is also relatively expensive. Analyzing bearing needs is a good place for CKG to start, specifically in one of its largest distribution centers such as Central New Jersey, which serves 10 major customers. You have been asked to analyze the bearing inventory level requirements for this center, including simulating demand based on 30-day historical extremes, calculating a target inventory level based on this simulation, and then comparing simulated values to actual values from the past five days from the targeted warehouse. Complete the following. Open the workbook named Parts.xlsx and then save it as CKG Parts Analysis_yourname.xlsx. Modify the format of the data on the BearingData worksheet so that zero values are displayed with a 0 instead of the default dash, aligned on the right side of the column. On the Bearing Data worksheet, take the existing 30-day data for these bearings for each customer to calculate the high and low limits of the bearing demand by customer. Place Low in cells A35:K35 and High in cells A36:K36. (Hint: Use MIN and MAX functions) Create a new worksheet named Simulation. Use the high and low limits you just calculated to simulate daily requirements for each customer (Hint: use RANDBETWEEN function) to obtain a total daily requirement for each day, and place in column L with title Daily Needs (Hint: use SUM function). Assume that the daily requirements will vary for each customer randomly between the high and low limits you have calculated from the existing 30-day data for that customer. Generate the data for approximately 100 instances (days), and then copy the results as values to another new worksheet in the workbook and name it Simulation Data 1. Keep the original analysis intact on the Simulation worksheet so you can use it again later. Be sure that your worksheets have titles and cell formatting just like the initial worksheet (Bearing Data). On the Simulation Data 1 worksheet, in an adjacent column, calculate the total demand by day for all 10 customers. Then, use this daily demand total data to calculate the daily mean, mode, median, and standard deviation for the total requirements of all 10 customers, place information in cells J105:L108. Use cell shading (yellow) to clearly identify your calculation area. mean mode median stdev Extend your analysis (on the same worksheet, Simulation Data 1) to include a ranking of the data (1 to 100), in column M, so that the day with the least total demand has a rank of 1 (Hint: use RANK.EQ function). Again, use cell shading (purple) to clearly identify column with your calculations. To the right of the data (in cells O3:Q8), create a listing of the top and bottom five daily combined demands from the 100 simulated instances (hint: use LARGE & SMALL FUNCTIONS). Clearly identify this listing using borders and shading (yellow). Title the table, Demand Extremes. Demand Extremes: top 5 bottom 5 1 2 3 4 5 Recommend a target inventory level needed for a five-day period based on the following; to be on the cautious side, assume each day's supply will be equal to the average daily demand for all locations combined, plus three standard deviations: Because the mean and standard deviations might not already be integers, round the daily demand up to the nearest whole number. Then, use this calculated daily demand (average plus 3 standard deviations) to calculate demand over a five-day period. (Hint: here is the logic for this formula: Roundup(mean + 3 * stdev,0) * 5 days) Place this recommendation just below the top/bottom analysis, again clearly identifying it, use shading (blue). Add the label Bearing Recommended 5-day Inventory Level to identify the cell containing the actual value. The warehouse manager has tracked a total of five different parts over the past five days, recording for each shipment the value of the part and the number of days it was in storage. One of these tracked parts is the bearing you have just analyzed. The data has been compiled in a workbook named Demand.xlsx. Each line item represents a single shipment of one item. Copy the data from the Demand.xlsx workbook to your workbook and place it on a worksheet named Actual Demand. On the Actual Demand worksheet, summarize the data to determine the number shipped by part, the total values of those shipments by part, and the average number of days that part was stored, using the format shown below. Place this table in cells F1:I6. Item Description #Items Shipped Total Dollar Value of Items Shipped Average #Days Held in Inventory Bearings Timing Belts Air Filters Fan Belt Electronic Board In the #Items Shipped column – write a formula to determine the number of bearings shipped. Write the formula so that it can be copied down the column to automatically determine the number of timing belts shipped, the number of air filters shipped, and so on. (Hint: use absolute values ($) in formula, COUNTIF) In the Total Dollar Value of Items Shipped column – write a formula to determine the total value of all bearings shipped. Again, write the formula so that it can be copied down the column to automatically determine the value of timing belts, air filters, and so on. (Hint: use absolute values ($) in formula, SUMIF) In the Average #Days Held in Inventory column – write a formula to determine the average number of days bearings shipped were held in inventory; again write a formula so that it can be copied down the column. Be sure that these formulas will work even if the data is updated in the future. (Hint: use absolute values ($) in formula, SUMIF) Based on the recommended inventory level you previously calculated for bearings, would you have had enough bearings in the warehouse to cover these orders? Place your answer in a cell below your analysis (cell F9) on the Actual Demand worksheet. Clearly identify this answer and highlight it in yellow. Double-check all values and formulas for correct implementation. Include sufficient formatting and titles to clearly identify the worksheet elements. Save and submit the CKG Parts Analysis_YourName.xlsx workbook.

CKG Auto Data files needed: Parts.xlsx and Demand.xlsx

CKG Auto's business supplies parts for auto repairs. The most critical component of the parts supply business is having enough of the right parts on hand so that repair shops can receive same-day delivery. The key to profits is minimizing the number of parts that need to be warehoused while also ensuring that sufficient parts are on hand to meet orders. Because each warehouse distribution center serves a different set of customers with different needs, each center must be considered separately. Distribution centers have a five-day lead time for ordering parts. The lead time must be taken into consideration when determining target inventory levels.

The cost involved in warehousing the parts includes the working capital tied up in the inventory (cost of each part) as well as the space to store the part. These costs can be substantial. On the other hand, alternative 'generic' parts are often available from rival suppliers, and keeping the auto repair centers supplied and customers satisfied is critical.

One of the most problematic items to supply and store are bearings. This part is both high volume and relatively large, taking up considerable warehousing space. A bearing is also relatively expensive. Analyzing bearing needs is a good place for CKG to start, specifically in one of its largest distribution centers such as Central New Jersey, which serves 10 major customers. You have been asked to analyze the bearing inventory level requirements for this center, including simulating demand based on 30-day historical extremes, calculating a target inventory level based on this simulation, and then comparing simulated values to actual values from the past five days from the targeted warehouse.

Complete the following.

Open the workbook named Parts.xlsx and then save it as CKG Parts Analysis_yourname.xlsx.

Modify the format of the data on the BearingData worksheet so that zero values are displayed with a 0 instead of the default dash, aligned on the right side of the column.

On the Bearing Data worksheet, take the existing 30-day data for these bearings for each customer to calculate the high and low limits of the bearing demand by customer. Place Low in cells A35:K35 and High in cells A36:K36. (Hint: Use MIN and MAX functions)

Create a new worksheet named Simulation. Use the high and low limits you just calculated to simulate daily requirements for each customer (Hint: use RANDBETWEEN function) to obtain a total daily requirement for each day, and place in column L with title Daily Needs (Hint: use SUM function). Assume that the daily requirements will vary for each customer randomly between the high and low limits you have calculated from the existing 30-day data for that customer. Generate the data for approximately 100 instances (days), and then copy the results as values to another new worksheet in the workbook and name it Simulation Data 1. Keep the original analysis intact on the Simulation worksheet so you can use it again later. Be sure that your worksheets have titles and cell formatting just like the initial worksheet (Bearing Data).

On the Simulation Data 1 worksheet, in an adjacent column, calculate the total demand by day for all 10 customers. Then, use this daily demand total data to calculate the daily mean, mode, median, and standard deviation for the total requirements of all 10 customers, place information in cells J105:L108. Use cell shading (yellow) to clearly identify your calculation area.

mean
mode
median
stdev

Extend your analysis (on the same worksheet, Simulation Data 1) to include a ranking of the data (1 to 100), in column M, so that the day with the least total demand has a rank of 1 (Hint: use RANK.EQ function). Again, use cell shading (purple) to clearly identify column with your calculations.

To the right of the data (in cells O3:Q8), create a listing of the top and bottom five daily combined demands from the 100 simulated instances (hint: use LARGE & SMALL FUNCTIONS). Clearly identify this listing using borders and shading (yellow). Title the table, Demand Extremes.

Demand Extremes:
top 5
bottom 5
1
2
3
4
5

Recommend a target inventory level needed for a five-day period based on the following; to be on the cautious side, assume each day's supply will be equal to the average daily demand for all locations combined, plus three standard deviations:

Because the mean and standard deviations might not already be integers, round the daily demand up to the nearest whole number.

Then, use this calculated daily demand (average plus 3 standard deviations) to calculate demand over a five-day period. (Hint: here is the logic for this formula: Roundup(mean + 3 * stdev,0) * 5 days)

Place this recommendation just below the top/bottom analysis, again clearly identifying it, use shading (blue). Add the label Bearing Recommended 5-day Inventory Level to identify the cell containing the actual value.

The warehouse manager has tracked a total of five different parts over the past five days, recording for each shipment the value of the part and the number of days it was in storage. One of these tracked parts is the bearing you have just analyzed. The data has been compiled in a workbook named Demand.xlsx. Each line item represents a single shipment of one item. Copy the data from the Demand.xlsx workbook to your workbook and place it on a worksheet named Actual Demand.

On the Actual Demand worksheet, summarize the data to determine the number shipped by part, the total values of those shipments by part, and the average number of days that part was stored, using the format shown below. Place this table in cells F1:I6.

Item Description
#Items Shipped
Total Dollar Value of Items Shipped
Average #Days Held in Inventory
Bearings
Timing Belts
Air Filters
Fan Belt
Electronic Board

In the #Items Shipped column – write a formula to determine the number of bearings shipped. Write the formula so that it can be copied down the column to automatically determine the number of timing belts shipped, the number of air filters shipped, and so on. (Hint: use absolute values ($) in formula, COUNTIF)

In the Total Dollar Value of Items Shipped column – write a formula to determine the total value of all bearings shipped. Again, write the formula so that it can be copied down the column to automatically determine the value of timing belts, air filters, and so on. (Hint: use absolute values ($) in formula, SUMIF)

In the Average #Days Held in Inventory column – write a formula to determine the average number of days bearings shipped were held in inventory; again write a formula so that it can be copied down the column. Be sure that these formulas will work even if the data is updated in the future. (Hint: use absolute values ($) in formula, SUMIF)

Based on the recommended inventory level you previously calculated for bearings, would you have had enough bearings in the warehouse to cover these orders? Place your answer in a cell below your analysis (cell F9) on the Actual Demand worksheet. Clearly identify this answer and highlight it in yellow.

Double-check all values and formulas for correct implementation. Include sufficient formatting and titles to clearly identify the worksheet elements.

Save and submit the CKG Parts Analysis_YourName.xlsx workbook.

Added by Derrick R.

Thanks for your feedback!

CKG Auto Data files needed: Parts.xlsx and Demand.xlsx CKG Auto's business supplies parts for auto repairs. The most critical component of the parts supply business is having enough of the right parts on hand so that repair shops can receive same-day delivery. The key to profits is minimizing the number of parts that need to be warehoused while also ensuring that sufficient parts are on hand to meet orders. Because each warehouse distribution center serves a different set of customers with different needs, each center must be considered separately. Distribution centers have a five-day lead time for ordering parts. The lead time must be taken into consideration when determining target inventory levels. The cost involved in warehousing the parts includes the working capital tied up in the inventory (cost of each part) as well as the space to store the part. These costs can be substantial. On the other hand, alternative 'generic' parts are often available from rival suppliers, and keeping the auto repair centers supplied and customers satisfied is critical. One of the most problematic items to supply and store are bearings. This part is both high volume and relatively large, taking up considerable warehousing space. A bearing is also relatively expensive. Analyzing bearing needs is a good place for CKG to start, specifically in one of its largest distribution centers such as Central New Jersey, which serves 10 major customers. You have been asked to analyze the bearing inventory level requirements for this center, including simulating demand based on 30-day historical extremes, calculating a target inventory level based on this simulation, and then comparing simulated values to actual values from the past five days from the targeted warehouse. Complete the following. Open the workbook named Parts.xlsx and then save it as CKG Parts Analysis_yourname.xlsx. Modify the format of the data on the BearingData worksheet so that zero values are displayed with a 0 instead of the default dash, aligned on the right side of the column. On the Bearing Data worksheet, take the existing 30-day data for these bearings for each customer to calculate the high and low limits of the bearing demand by customer. Place Low in cells A35:K35 and High in cells A36:K36. (Hint: Use MIN and MAX functions) Create a new worksheet named Simulation. Use the high and low limits you just calculated to simulate daily requirements for each customer (Hint: use RANDBETWEEN function) to obtain a total daily requirement for each day, and place in column L with title Daily Needs (Hint: use SUM function). Assume that the daily requirements will vary for each customer randomly between the high and low limits you have calculated from the existing 30-day data for that customer. Generate the data for approximately 100 instances (days), and then copy the results as values to another new worksheet in the workbook and name it Simulation Data 1. Keep the original analysis intact on the Simulation worksheet so you can use it again later. Be sure that your worksheets have titles and cell formatting just like the initial worksheet (Bearing Data). On the Simulation Data 1 worksheet, in an adjacent column, calculate the total demand by day for all 10 customers. Then, use this daily demand total data to calculate the daily mean, mode, median, and standard deviation for the total requirements of all 10 customers, place information in cells J105:L108. Use cell shading (yellow) to clearly identify your calculation area. mean mode median stdev Extend your analysis (on the same worksheet, Simulation Data 1) to include a ranking of the data (1 to 100), in column M, so that the day with the least total demand has a rank of 1 (Hint: use RANK.EQ function). Again, use cell shading (purple) to clearly identify column with your calculations. To the right of the data (in cells O3:Q8), create a listing of the top and bottom five daily combined demands from the 100 simulated instances (hint: use LARGE & SMALL FUNCTIONS). Clearly identify this listing using borders and shading (yellow). Title the table, Demand Extremes. Demand Extremes: top 5 bottom 5 1 2 3 4 5 Recommend a target inventory level needed for a five-day period based on the following; to be on the cautious side, assume each day's supply will be equal to the average daily demand for all locations combined, plus three standard deviations: Because the mean and standard deviations might not already be integers, round the daily demand up to the nearest whole number. Then, use this calculated daily demand (average plus 3 standard deviations) to calculate demand over a five-day period. (Hint: here is the logic for this formula: Roundup(mean + 3 * stdev,0) * 5 days) Place this recommendation just below the top/bottom analysis, again clearly identifying it, use shading (blue). Add the label Bearing Recommended 5-day Inventory Level to identify the cell containing the actual value. The warehouse manager has tracked a total of five different parts over the past five days, recording for each shipment the value of the part and the number of days it was in storage. One of these tracked parts is the bearing you have just analyzed. The data has been compiled in a workbook named Demand.xlsx. Each line item represents a single shipment of one item. Copy the data from the Demand.xlsx workbook to your workbook and place it on a worksheet named Actual Demand. On the Actual Demand worksheet, summarize the data to determine the number shipped by part, the total values of those shipments by part, and the average number of days that part was stored, using the format shown below. Place this table in cells F1:I6. Item Description #Items Shipped Total Dollar Value of Items Shipped Average #Days Held in Inventory Bearings Timing Belts Air Filters Fan Belt Electronic Board In the #Items Shipped column – write a formula to determine the number of bearings shipped. Write the formula so that it can be copied down the column to automatically determine the number of timing belts shipped, the number of air filters shipped, and so on. (Hint: use absolute values ($) in formula, COUNTIF) In the Total Dollar Value of Items Shipped column – write a formula to determine the total value of all bearings shipped. Again, write the formula so that it can be copied down the column to automatically determine the value of timing belts, air filters, and so on. (Hint: use absolute values ($) in formula, SUMIF) In the Average #Days Held in Inventory column – write a formula to determine the average number of days bearings shipped were held in inventory; again write a formula so that it can be copied down the column. Be sure that these formulas will work even if the data is updated in the future. (Hint: use absolute values ($) in formula, SUMIF) Based on the recommended inventory level you previously calculated for bearings, would you have had enough bearings in the warehouse to cover these orders? Place your answer in a cell below your analysis (cell F9) on the Actual Demand worksheet. Clearly identify this answer and highlight it in yellow. Double-check all values and formulas for correct implementation. Include sufficient formatting and titles to clearly identify the worksheet elements. Save and submit the CKG Parts Analysis_YourName.xlsx workbook.

Play audio

Feedback

Question

Please give Ace some feedback