excel assignment hi lo method

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

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.

 
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.