Question

Pleasanton Raft Company, Inc.

As the management accountant for Pleasanton Raft Company, Inc., you have been asked to attend a planning session for the next season. The owner, Mara Mason specifically wants to be able to predict her fuel costs each coming week.

Mara typically staffs the desk at the river – answering the phone, checking in customers, and taking payment – while her staff drives the customers and rafts out to the river. Since she is not actively driving the van, it has been her process to maintain a separate bank account specifically for fuel costs, and giving the debit card tied to the account to her staff. That way, staff members can easily fill the tank on the way back from dropping off customers. To limit her risk, Mara wants to keep a low balance in this account, just sufficient to pay fuel costs for the upcoming week. To that end, she currently checks the balance in the account each Monday morning, and transfers in just enough funds to bring the balance to $275.

This past season, Mara had some cash flow issues – there were a couple of occasions this year where the company debit card was declined for insufficient funds at the gas station. This can be a serious problem. If the van runs out of gas, she is not able to transport customers to the river, and she will potentially lose customers. Also, if the charge does go through, she is liable for overdraft fees. Therefore, Mara wants to improve from her current process of refilling that account to $275 at the beginning of each week.

Mara started out by collecting data – on each Monday morning, she recorded the price of gas and the number of reservations for the coming week (see the attached Excel spreadsheet). She would like to use one of these measures to estimate the week’s fuel costs, but she is not sure which one would give her the best estimate. The number of reservations tells her how many customers have reserved boats for the coming week, but some reservations are later cancelled (or no-shows) and many customers show up unannounced for a trip down the river. In addition, the price of gas on Monday morning usually does not stay the same through the entire week. Mara knows that neither of these variables will perfectly predict her fuel costs, but figures that anything would be better than her current process.

DATA

Week	# of reservations	Gas price (as of Monday morning)	Fuel cost (total cost for the week)
1	3	$2.55	$153.52
2	20	$2.67	$170.79
3	23	$2.64	$249.39
4	10	$2.43	$204.00
5	15	$2.34	$218.31
6	8	$2.64	$170.17
7	24	$2.58	$212.86
8	13	$2.64	$223.35
9	10	$2.33	$160.63
10	1	$2.26	$139.91
11	29	$2.29	$208.70
12	22	$2.75	$244.41
13	41	$2.61	$287.18
14	15	$2.37	$243.92
15	5	$2.31	$198.09
16	30	$2.43	$250.39
17	32	$2.35	$225.19
18	6	$2.21	$286.29
19	35	$2.87	$274.92
20	18	$2.50	$186.28
21	27	$2.58	$211.33
22	27	$2.72	$246.94
23	19	$2.23	$188.97
24	11	$2.25	$177.65
25	7	$2.30	$149.27
26	9	$2.41	$177.60

1. Which of the two variables (number of reservations or gas price) is a better predictor of fuel costs? Attach graphs or tables to illustrate your response. --> Best way to do the work find the answer?

Answer 1

				X1	X2	Y
			Week	# of Reservation	Gas Price	Fuel Cost
			1	3	$2.55	$153.52
			2	20	$2.67	$170.79
			3	23	$2.64	$249.39
			4	10	$2.43	$204.00
			5	15	$2.34	$218.31
			6	8	$2.64	$170.17
			7	24	$2.58	$212.86
			8	13	$2.64	$223.35
			9	10	$2.33	$160.63
			10	1	$2.26	$139.91
			11	29	$2.29	$208.70
			12	22	$2.75	$244.41
			13	41	$2.61	$287.18
			14	15	$2.37	$243.92
			15	5	$2.31	$198.09
			16	30	$2.43	$250.39
			17	32	$2.35	$225.19
			18	6	$2.21	$286.29
			19	35	$2.87	$274.92
			20	18	$2.50	$186.28
			21	27	$2.58	$211.33
			22	27	$2.72	$246.94
			23	19	$2.23	$188.97
			24	11	$2.25	$177.65
			25	7	$2.30	$149.27
			26	9	$2.41	$177.60
		Analysis of effect of # of Reservation on Fuel Cost
		Click "Data"
		Click "Data Analysis"
		Select "Regression" tool
		Click"OK"
		Input y_Range (Fuel Costs)
		Input x_Range (# of reservations=X1)
		Click "OK"
		Fuel Cost (Y)=Intercept(a)+X Variable(b)*# of reservations(X1)
		Intercept	165.1753
		X variable	2.5337
		Y=165.1753+2.5337*X1
		R Square Value	0.418722
		Analysis of effect of Gas Price on Fuel Cost
		Click "Data"
		Click "Data Analysis"
		Select "Regression" tool
		Click"OK"
		Input y_Range (Fuel Costs)
		Input x_Range (Gas Price)
		Click "OK"
		Fuel Cost (Y)=Intercept(a)+X Variable(b)*Gas Price(X2)
		Intercept	18.52988
		X variable	77.47095
		Y=18.52988+77.47095*X2
		R Square Value	0.116086
		R Square Value indicates goodness of fit
		CONCLUSION:
		Number of Reservation has a better goodness of fit
		# of reservation (X1) is better predictor of Fuel Cost
								SUMMARY OUTPUT FUEL COST VS # OF RESERVATIONS Regression Statistics Multiple F 0.647087 R Square 0.418722 Adjusted 0.394502 Standard [ 32.64257 Observati - 26 ANOVA df 2 Regressio 3 Residual Total SS MS F gnificance F 1 18421.39 18421.39 17.28835 0.000353 24 25572.9 1065.538 25 43994.29 Coefficientsandard Errt Stat 7 Intercept165.1753 12.53851 13.17344 B X Variable 2.5337 0.609366 4.157926 P-value Lower 95%Upper 95%ower 95.0%pper 95.0% 1.77E-12 139.2971 191.0535 139.2971 191.0535 0.000353 1.27603 3.79137 1.27603 3.79137 А в E G Ι Η 1 SUMMARY OUTPUT 2 FUEL COST VS GAS PRICE 3 Regression Statistics 4 Multiple F 0.340714 5 R Square 0.116086 6 Adjustedi 0.079256 7 Standard I 40.25294 8 Observati 26 10 ANOVA 11 df SS MS F gnificance F 12 Regressio 1 5107.118 5107.118 3.15196 0.08852 13 Residual 24 38887.17 1620.299 14 Total 25 43994.29 15 16 Coefficientandard Errt Stat P-value Lower 95%Upper 95%ower 95.09 pper 95.0% 17 Intercept 18.52988 108.1375 0.171355 0.865381 -204.655 241.7146 -204.655 241.7146 18 X Variable 77.47095 43.63636 1.775376 0.08852 -12.5901 167.532 -12.5901 167.532