SOLUTION IN EXCEL EXCEL EXCEL EXCEL EXCEL EXCEL!!!!!!!!!!!!!!!!!!!!!

Procurement logistics AT Initial situation: You are a medium$-$sized logistics service provider with headquarters in AT and offer FTL$,$ LTL and groupage transports for a large number of customers from industry and trade. For the groupage segment, you have your own transshipment hubs in AT and the CEE countries. In the European countries where you are not represented, you work together with forwarding partners. Across the company, you handle around $1/3$ of the transports with your own fleet, the remaining capacities have to be purchased. In addition to transportation services, you also offer contract logistics services; you have several warehousing locations in AT and CEE where you store goods for customers.

Tender: A large potential customer from the manufacturing industry puts its European procurement logistics out to tender. He divides Europe into different regions so that one provider does not have to cover all countries. The tender is based on the regional forwarder concept. They will regularly collect consignments from suppliers, consolidate them in their hubs and then drive the bundled quantities to the production plants in Nuremberg and Berlin. Nuremberg is the larger plant, to which around $70\%$ of the volume is delivered. Time slots $(90$ minutes long$)$ are booked via a platform for deliveries to the plants, which must be adhered to as far as possible. A strict penalty system applies to late deliveries. If a delivery does not arrive until the next day, downtime must be expected. One minute of downtime costs EUR $100.$ The delivery time is fixed by the customer and is one working day for the plants. It is generally assumed that both the plants and the suppliers only produce on working days. The transport orders are sent to the logistics service provider via a newly created platform using an API. If the platform is temporarily unavailable, an alternative solution must be generated so that the transit times can still be met. Day A: Provision of the transport order at material number level on the platform by $12\mathrm{.}00$ noon. Attention! Changes to the order $($quantity$,$ weight, etc.$)$ can be uploaded by the supplier until $3\mathrm{.}00$ pm$.$ Day B: Physical collection from the supplier, generally between $06$:$00$ and $16$:$00$ Day C: Delivery according to booked time slots in the plants. The goods acceptance times are generally $6\mathrm{.}00$ am to $4\mathrm{.}00$ pm$.$ Friday only until $12\mathrm{.}00$ noon. The time of collection must be agreed with each supplier. Take into account the production cycles. Quite often orders are not ready until the afternoon of day B$,$ so the supplier has a preference for afternoon collection. The customer requires a dedicated customer service on weekdays between $6\mathrm{.}00$ am and $6\mathrm{.}00$ pm$.$ An ETA system with very high reliable forecasts is to be implemented. All scan statuses of the shipment $($including the pick$-$up at the sender$)$ and the ETA should be made available immediately after scanning. Take into account that the recipient of the goods asks at material number level when the consignment is expected to arrive. Transportation is generally carried out in pallet cages with external dimensions of $1\mathrm{.}20$ x $0\mathrm{.}80$ x $0\mathrm{.}99$ m$.$ These must be returned to the supplier. These must be returned to the supplier; they are neither foldable nor collapsible, but can be stacked. The following quantities for a reference month are as follows.

Place AT Total pallet cages in reference month

$170$

$2300$

$3800$

$41600$

$51300$

$6400$

$7100$

$81500$

$9300$

A collection shipment has an average of $10$ pallet cages, whereby the standard deviation is very small. The standard collection from a supplier takes place twice a week $($e$.$g$.$ Mon and Thu$).$ There are only $3$ suppliers $($all from zip code $8)$ who produce such large quantities that the use of the FTL concept is worthwhile $($a collection consignment consists on average of $90$ pallet cages$).$ It can also be assumed with these $3$ suppliers that there will be two collections per week from the supplier. The customer is aiming for a contract term of $3$ years. In addition to a concept, he expects a price sheet in pallet cages per zip code region and per plant location. Task: Develop a detailed concept for service provision and calculate the production factor quantities and the costs of service provision for the individual processes $($detailed statement of all costs!$)$ and create a remuneration model for the individual services. Determine the economic advantage of this customer!