simple excel project

Project Description:

this is a fairly simple excel project with step by step instructions on the attached document. please complete the attached project by end of day. also on a separate document can you please explain the excel steps you used 1 by 1.

excel problem
forecasting - south bay parcel service

south bay parcel service (sbps) operates a small fleet of delivery trucks, primarily in los angeles county. their main business consists of pickup and same-day delivery of small packages and envelopes for businesses and for private, individual customers. the company must keep prices low and service reliable in order to compete with major carriers such as ups and federal express. low prices are enabled by low operating costs. the company has been able in the past to enter into long-term, fixed-price contracts for gasoline from one of the area’s major wholesale gasoline distributors. in 2014-2015, the 12-month contract, beginning in november 2014, locked-in the company to purchase gasoline at a fixed price of $3.888 per gallon which was the average price for the prior 12 months (november 2013 - october 2014). this fixed price scheme did not work out well for sbps as the actual average price was only $3.329 for the 2014-15 contract (through october 2015). because of high uncertainty about future gas prices, the negotiated price in the new 2015-16 contract will change monthly and will be set retroactively each month at the average los angeles area price as determined by the energy information administration which is part of the u.s. department of energy.

the monthly fluctuation of such an important cost could create cash flow problems for sbps if they cannot accurately anticipate their gasoline cost because gasoline is the company’s largest operating expense, after labor. so the manager of operations and finance would like to be able to forecast gasoline costs, by month, for the entire duration of the new 2015-16 contract which runs from november 1, 2015 through october 31 2016.

the following table shows the average price/gallon of regular gasoline in the los angeles area, by month, from november 2010 through october 2015.

2010 2011 2012 2013 2014 2015
jan 3.363 3.777 3.720 3.661 2.578
feb 3.544 4.061 4.224 3.757 2.772
mar 3.980 4.431 4.256 4.019 3.406
apr 4.189 4.303 4.030 4.257 3.282
may 4.218 4.360 4.042 4.223 3.894
jun 3.951 4.095 4.051 4.163 3.665
jul 3.847 3.815 4.085 4.088 4.062
aug 3.805 4.114 3.913 3.895 3.764
sep 3.955 4.182 3.982 3.755 3.268
oct 3.881 4.464 3.789 3.560 3.150
nov 3.161 3.847 3.876 3.614 3.209
dec 3.265 3.645 3.646 3.658 2.900
data source:
u.s energy information administration
http://www.eia.gov/dnav/pet/hist/leafhandler.ashx?n=pet&s=emm_epmr_pte_y05la_dpg&f=m

download the excel forecasting template from blackboard. copy and paste the gasoline price data into the input worksheet. this document is available on blackboard.

1.(10) on the input worksheet, create a line chart of the historical gas price data from november 2010 through october 2015. label the axes appropriately.

run two forecasts with the excel forecasting template; multiplicative seasonal and additive seasonal; each is set up on a separate worksheet tab on the excel forecasting template. in both seasonal cases enter 60 for the number of periods and 12 for the number of seasons.

2.(30) on each of the two forecast worksheets, above, create a line chart, each with two series; one for the actual prices through october 2015, and one for the forecasted prices through october 2016. label the axes appropriately.
3.(10) create the table, below, in word or excel. fill in the table using three decimal places.

seasonal seasonal
multiplicative additive
mad
mape
bias

the number of gallons, by month, that the company expects to purchase in the next 12 months are as follows:

month gallons
november 2015 6,100
december 2015 7,500
january 2016 4,100
february 2016 3,800
march 2016 4,100
april 2016 4,500
may 2016 4,100
june 2016 5,800
july 2016 6,100
august 2016 5,500
september 2016 4,100
october 2016 5,200

copy the blank table, below, into a blank excel worksheet; or create it from scratch.

seasonal multiplicative seasonal additive
month, year forecasted price gallons
purchased monthly cost forecasted price gallons
purchased monthly cost
november 2015
december 2015
january 2016
february 2016
march 2016
april 2016
may 2016
june 2016
july 2016
august 2016
september 2016
october 2016
total xxxxx xxxxx

4.(30) calculate the monthly costs in the table, above.
calculate the 12 month totals of gallons purchased and total cost in the bottom row of the table.

5.(20) as a result of the above analyses, how much should sbps actually budget for gasoline over the next contract? justify your answer.
Skills Required:
Project Stats:

Price Type: Fixed

Project Budget: $100 to $500
Completed
Total Proposals: 3
1 Current viewersl
64 Total views
Project posted by:

Proposals

Proposals Reputation Price offered