An ordering system by analyzing the data and determining how the next supplier order should be structured.

I’m studying for my Excel class and need an explanation.

to look at the current demand and come up with

an ordering system by analyzing the data and determining how the next supplier order should

be structured. Your goal is to create the right price mixture between the companies that will

maximize profit, reduce spillage, and lower your overall costs.

DISCUSSION QUESTIONS

1.

Create an excel spreadsheet that shows the food requirements for the next month

(January). Use the past three month’s actual usage to formulate a forecast for each item for the

month of January. See Table 2: Product usage for October, November and December.

Normally, the proceeding month’s usage is a good indicator of what will be consumed for the

following month, so use a weighted average method to determine what will be used in January.

Use the following weights: October – 20%, November – 30 %, December – 50%

2.

Create a spread sheet that illustrates the cost of each case from both competitors and the

average price per unit for each competitor. Use the information in Table 1: Comparison Prices.

3.

Once you have completed the forecast, calculate the order cost of each item and compare

between the two. Based on the cost involved, choose the better supplier for each item and then

calculate the total spoilage loss. Then, calculate the monetary loss based on spoilage. Make

sure you calculate the total amount of spoilage expected from each supplier for the month.

Fresh Delights will charge a 2.5% delivery fee for everything that is ordered. Horton’s

deliveries are free. Horton’s will give a 5% discount on your entire purchase as long as the

entire order amount exceeds $5,000. Calculate the total order cost of Horton’s and Fresh

Delight’s based on the information provided to Michael Smith.

4.

Calculate what your total expected revenue will be for the month of January. Using the

same weighted forecasting technique, det

ermine how many dinner and breakfast buffets the

hotel can expect to sell for the month. The hotel is currently charging $28.95 per dinner buffet,

and $14.95 per breakfast buffet. Determine what your profit is for the month of January. Gross

profit is calculated using the expression: Total Revenue – Total Costs = Gross Profit.

Determine what your food cost percentage was for the month of January. Food cost percentage

is calculated using the expression: Total Costs / Total Revenue.

5.

How is this restaurant doing when compared to industry average? (Please make sure to

cite the source from which you collected information on industry average).

6.

Will you change your decision on supplier of any products considering the spoilage cost?

If yes, for which products you will change the supplier based on spoilage?