Instructions When preparing financial projections for a business plan part of the process is to be able toestimate fixed and variable costs Please use the following data to practice cost analysis using Excel Upload your answers and results in a Word or PDF document to the designated area You do not need to upload your Excel file High Low Method The city of Rockville reported the following annual cost data for maintenance work performed on its fleet of trucks Reporting Period (Year) Total Costs Level of Activity (Miles Driven) Year 1 $750,000 225,000 Year 2 850,000 240,000 Year 3 1,100,000 430,000 Year 4 1,150,000 454,000 Year 5 1,250,000 560,000 Year 6 1,550,000 710,000 Required Use the high low method to estimate total fixed costs per year and the variable cost per mile State your results in the cost equation form Y f v X Required What would the estimated costs be if the trucks drove 500,000 miles in year 7 Scattergraph Method Refer to the data in the previous exercise for the city of Rockville Required Use the scattergraph method to estimate total fixed costs per year and the variable cost per mile State your results in the cost equation form Y f v X by filling in the dollar amounts for f and v Required What would the estimated costs be if the trucks drove 500,000 miles in year 7 Regression Analysis Regression analysis was run using the same data for the city of Rockville Required Use the regression output to develop the cost equation Y f v X by filling in the dollar amounts for f and v Required What would the city of Rockville's estimated costs be if its trucks drove 500,000 miles in year 7

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 23, 2024

Instructions : When preparing financial projections for a business plan part of the process is to be able toestimate fixed and variable costs. Please use

Instructions: When preparing financial projections for a business plan part of the process is to be able toestimate fixed and variable costs. Please use the following data to practice cost analysis using Excel.Upload your answers and results in a Word or PDF document to the designated area. You do not need to upload your Excel file.

High-Low Method.The city of Rockville reported the following annual cost data for maintenance work performed on its fleet of trucks.

Reporting Period (Year)	Total Costs	Level of Activity (Miles Driven)
Year 1	$750,000	225,000
Year 2	850,000	240,000
Year 3	1,100,000	430,000
Year 4	1,150,000	454,000
Year 5	1,250,000	560,000
Year 6	1,550,000	710,000

[Required]Use the high-low method to estimate total fixed costs per year and the variable cost per mile. State your results in the cost equation form Y =f+vX.
[Required]What would the estimated costs be if the trucks drove 500,000 miles in year 7?

Scattergraph Method.Refer to the data in the previous exercise for the city of Rockville.

[Required]Use the scattergraph method to estimate total fixed costs per year and the variable cost per mile. State your results in the cost equation form Y =f+vX by filling in the dollar amounts forfandv.
[Required]What would the estimated costs be if the trucks drove 500,000 miles in year 7?

Regression Analysis.Regression analysis was run using the same data for the city of Rockville.

[Required]Use the regression output to develop the cost equation Y =f+vX by filling in the dollar amounts forfandv.
[Required]What would the city of Rockville's estimated costs be if its trucks drove 500,000 miles in year 7?