Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Metrics Metrics Total % Revenue Region 2018 Volume Active Account Active Carriers Active Facilities Revenue Cost Mileage OTP OTD Northeast Southeast Midwest Southwest West Jan
Metrics | Metrics | Total | % | Revenue | Region | ||||||||||
2018 | Volume | Active Account | Active Carriers | Active Facilities | Revenue | Cost | Mileage | OTP | OTD | Northeast | Southeast | Midwest | Southwest | West | |
Jan | 1784 | 18 | 51 | 357 | $23,19,274 | $2,040,961 | 488,832 | 94% | 93% | 9% | 41% | 7% | 11% | 32% | |
Feb | 1874 | 18 | 53 | 375 | $1,928,746 | $1,647,728 | 616,674 | 93% | 94% | 7% | 37% | 9% | 22% | 25% | |
March | 2032 | 18 | 56 | 406 | $2,898,302 | $2,453,703 | 766,241 | 96% | 95% | 11% | 15% | 21% | 26% | 27% | |
April | 2078 | 19 | 58 | 416 | $1667,795 | $1,565,399 | 571,350 | 94% | 94% | 11% | 17% | 15% | 34% | 23% | |
May | 2371 | 19 | 60 | 431 | $3,393,209 | $2,950,395 | 927,145 | 96% | 97% | 16% | 24% | 33% | 18% | 9% | |
June | 2642 | 18 | 62 | 423 | $4,415,135 | $3,876,930 | 850,792 | 98% | 99% | 22% | 5% | 46% | 13% | 14% | |
July | 2687 | 19 | 64 | 430 | $4,650,198 | $4,461,290 | 1,566,741 | 97% | 95% | 21% | 23% | 27% | 18% | 11% | |
August | 2,597 | 19 | 66 | 433 | $3,957,897 | $3,712,111 | 2,319,965 | 98% | 97% | 24% | 19% | 31% | 14% | 12% | |
September | 3613 | 20 | 68 | 602 | $7,721,579 | $7,404,995 | 1,141,796 | 95% | 93% | 12% | 26% | 23% | 20% | 19% | |
October | 1942 | 17 | 63 | 388 | $2,214,037 | $2,169,757 | 1,141,796 | 92% | 91% | 11% | 28% | 20% | 21% | 20% | |
November | 1784 | 17 | 59 | 357 | $1582,459 | $1598,283 | 717,191 | 90% | 89% | 5% | 23% | 9% | 25% | 38% | |
December | 1649 | 15 | 49 | 330 | $2,107,421 | $2,173,643 | 565,456 | 89% | 86% | 2% | 26% | 7% | 22% | 43% |
Please solve The assignment question it by using Excel
Case Study Scope: Conduct commercial and operational planning for 2021 regarding a new service line for a freight company. Your aim is to use applicable excel modeling techniques (related to textbook chapters 1 - 8) to identify a feasible strategy. The business challenge is to overcome operational and product constraints to help optimize service performance, revenue growth, and profitability. Considerations: The freight company's demand is generated from a mixture of small-to-medium, mid-market, and enterprise customers The supply base is owner-operators with small fleets Active accounts, carriers, or facilities are considered active if they have been associated with at least one (1) shipment in the same month Mileage is the sum of all miles operated by freight carrier shipment in that month On time performance (OTP) or on time delivery (OTD) is measured with a 30-minute buffer Regions (Northeast, Southeast, Midwest, Southwest, and West) indicate the percentage of loads originating from that specific geographic area Assignment: Identify a data driven strategy and improvements needed to accelerate the growth plan Produce a 6 month and 12 month forecast in 2021 comparing types and summarize findings Identify if there is a relationship between specific points in the data or any trends to incorporate Identify metrics and goals to set for the 2021 based on on-time delivery (OTD) trends Identify optimization modeling opportunities that can help advance overall profitable growth Consider if a new distribution location makes economic sense in a specific region with yearly rent of $250,000 Case Study Scope: Conduct commercial and operational planning for 2021 regarding a new service line for a freight company. Your aim is to use applicable excel modeling techniques (related to textbook chapters 1 - 8) to identify a feasible strategy. The business challenge is to overcome operational and product constraints to help optimize service performance, revenue growth, and profitability. Considerations: The freight company's demand is generated from a mixture of small-to-medium, mid-market, and enterprise customers The supply base is owner-operators with small fleets Active accounts, carriers, or facilities are considered active if they have been associated with at least one (1) shipment in the same month Mileage is the sum of all miles operated by freight carrier shipment in that month On time performance (OTP) or on time delivery (OTD) is measured with a 30-minute buffer Regions (Northeast, Southeast, Midwest, Southwest, and West) indicate the percentage of loads originating from that specific geographic area Assignment: Identify a data driven strategy and improvements needed to accelerate the growth plan Produce a 6 month and 12 month forecast in 2021 comparing types and summarize findings Identify if there is a relationship between specific points in the data or any trends to incorporate Identify metrics and goals to set for the 2021 based on on-time delivery (OTD) trends Identify optimization modeling opportunities that can help advance overall profitable growth Consider if a new distribution location makes economic sense in a specific region with yearly rent of $250,000Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started