Excel Assignment: Hi-Lo Method

This exercise will be an application of the Hi-Lo method and its use in forecasting future costs:

Save your time - order a paper!

Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines

Order Paper Now

The ZZ Company wants to forecast their utility costs for next year (2017). There is a relationship between the number of welds and the number of applications of glue and the total cost of utilities for the business. For 20×6 the activity and utility cost for the various months are as follows:

Number of Welds

Utilities Cost

Number of
Applications

Utilities Cost

January

60

2200

January

60

1800

February

70

2600

February

70

2100

March

90

2900

March

90

2700

April

120

3300

April

120

3600

May

100

3000

May

100

3000

June

130

3600

June

130

3900

July

150

4000

July

150

4500

August

140

3600

August

140

4200

September

110

3100

September

110

3300

October

80

2500

October

80

2400

The forecasted activity for 20×7 is as follows:

Estimated Number
of Welds

Estimated Number
of Applications

January

50

January

50

February

85

February

85

March

100

March

100

April

110

April

110

May

95

May

95

June

135

June

135

July

165

July

165

August

125

August

125

September

115

September

115

October

90

October

90

Calculate the total forecasted utility cost for 2017 for the following:

  • The total utility cost for welds
  • The total utility cost for applications
  • The total utility cost

Please use the following Excel template: Hi-Lo Assignment Template

Note that the “mixed cost check” columns are where you verify whether the cost is variable or mixed. Divide the monthly cost by the number of units produced that month – if the cost per unit varies from month to month, the cost is mixed. If the cost per unit remains constant, the cost is variable.