Problem # $1$: Statewide College Fleet Management Efficiency Analysis

Background: Statewide College offers a broad range of adult education courses across multiple

locations. To support its operations effectively and economically, the college manages a fleet

service for transporting its teaching staff and administrators. In March, recognizing the need for

additional capacity, the college expanded its fleet from $20$ to $21$ vehicles. The fleet service provides

fuel, maintenance supplies, and logistical support. Routine servicing is managed in$-$house, while

more significant repairs are outsourced to a specialized automotive service provider.

The data below represents the actual fleet operation costs for March compared to the planned

budget. The planning budget figures were determined based on specific cost assumptions:

March Actual Planning

Budget $($Over$)$ Under Budget

Miles. $.........63,00050,000$

Autos.. $.......2120$

Gasoline.. $.....$ $$9,350$ $$7,500($$$1,850)$

Oil, minor repairs, parts. $...2,3602,000(360)$

Outside repairs. $1,4201,50080$

Insurance.. $....2,1202,000(120)$

Salaries and benefits. $.....7,5407,5400$

Vehicle depreciation. $.....5,2505,000(250)$

Total.. $........$ $$28,040$ $$25,540$ $$(2,500)$

Cost Assumptions for Fleet Operation:

Gasoline: $$0\mathrm{.}15$ per mile

Oil and minor maintenance: $$0\mathrm{.}04$ per mile

External repairs: $$75$ per vehicle per month

Insurance: $$100$ per vehicle per month

Staff salaries and benefits: $$7,540$ per month

Vehicle depreciation: $$250$ per vehicle per month

The fleet manager has expressed concerns that the current report does not accurately reflect the

fleet service's operational efficiency and could misrepresent the service's performance.

Required Analysis:

$1.$ Spending Variances for March: Analyze and calculate the spending variances for the month of

March, taking into consideration the fleet expansion and actual operational activities. Your

analysis should quantify the differences between the actual expenses and what was budgeted,

providing a clear picture of where and why these variances occurred.

Evaluating the Report's Effectiveness: Identify and discuss the shortcomings in the existing cost

control report. Consider how these deficiencies might lead to misconceptions about the fleet

service's performance.

Demonstrate how your variance calculations address these issues, offering a more nuanced and

accurate assessment of the fleet's financial and operational efficiency.

CASE # $2,$ Due date $04/21$

Problem # $1$: Statewide College Fleet Management Efficiency Analysis

Background: Statewide College offers a broad range of adult education courses across multiple

locations. To support its operations effectively and economically, the college manages a fleet

service for transporting its teaching staff and administrators. In March, recognizing the need for

additional capacity, the college expanded its fleet from $20$ to $21$ vehicles. The fleet service provides

fuel, maintenance supplies, and logistical support. Routine servicing is managed in$-$house, while

more significant repairs are outsourced to a specialized automotive service provider.

The data below represents the actual fleet operation costs for March compared to the planned

budget. The planning budget figures were determined based on specific cost assumptions:

Cost Assumptions for Fleet Operation:

Gasoline: $$0\mathrm{.}15$ per mile

Oil and minor maintenance: $$0\mathrm{.}04$ per mile

External repairs: $$75$ per vehicle per month

Insurance: $$100$ per vehicle per month

Staff salaries and benefits: $$7,540$ per month

Vehicle depreciation: $$250$ per vehicle per month

The fleet manager has expressed concerns that the current report does not accurately reflect the

fleet service's operational efficiency and could misrepresent the service's performance.

Required Analysis:

Spending Variances for March: Analyze and calculate the spending variances for the month of

March, taking into consideration the fleet expansion and actual operational activities. Your

analysis should quantify the differences between the actual expenses and what was budgeted,

providing a clear picture of where and why these variances occurred.

Evaluating the Report's Effectiveness: Identify and discuss the shortcomings in the existing cost

control report. Consider how these deficiencies might lead to misconceptions about the fleet

service's performance.

Demonstrate how your variance calculations address these issues, offering a more nuanced and

accurate assessment of the fleet's financial and operational efficiency.Give me the answer in Excel form, not code!!!