Excel Assignments

Rocking Horse Cost

	Material Purchased	Cost	Usable Unit	Number of Usable Units in Purchased Materal	Cost Per Usable Unit
	12 foot 2X4	$9.45	Linear Foot	12
	14 foot 2X8	$10.50	Linear Foot
	1 Gallon of Paint	$16.50	Ounce	128
	1 Gallon of Stain	$19.95	Ounce
	1 lb of Screws (250 screws)	$8.95	Screw
	1 Yarn Roll (142 yards)	$8.96	Yard
	10 square feet leather	$34.99	Square Foot
	Item	Units Used	Cost Per Horse
	2x4	10
	2x8	6
	Paint	25
	Stain	10
	Screws	58
	Yarn	15
	Leather	2.5
		Labor:	$16.00
		Total Cost of one rocking horse:
		Selling Price:	$45.00
		Profit:

The numbers in the range F3:F9 should reflect the cost per usable unit of the items described in rows 3 through 9. For example, the cost per linear foot of 2X4 is $0.79 and can be calculated as follows: 9.45/12. Use the general formula below, cell reference, and the power of Excel to do the calculations for you. (Do NOT use a calculator except perhaps to check that Excel is producing the correct results.) Cost Per Usable Unit = Cost / Number of Usable Units in Purchased Material Because you are using formulas in F3:F9 that refer to values in columns C and E, when costs change in the future (as is inevitable), only the values in column C will need to be adjusted. The values in F will change automatically. Note that you also need to fill in appropriate values in cells E5:E9. (HINT: Use information in columns B and D.)

The values in cells D12:D18 reflect the cost of materials needed to create one rocking horse, based on the amount of material actually used. For example, $7.88 worth of 2X4 is needed and is calculated from entries in F3 and C12. Use the general formula below and the power of Excel to do the calculations for you. (Do NOT use a calculator except perhaps to check that Excel is producing the correct results.) Cost Per Horse = Cost Per Usable Unit * Units Used Again, updating values in C3:C9 as costs change should automatically update values in D12:D18.

Insert appropriate formulas into cells D22 and D26, so that their values will be adjusted automatically when material costs change. Labor cost is $16, and selling price is $45.

Should the company consider changing the selling price of its rocking horses? Explain.

DIRECTIONS Fill in answers for all 22 orange boxes on this sheet. Then click on the Six Month Forecast sheet tab and fill in answers for the 6 oranges boxes on that sheet. Then click on the Expansion sheet tab and fill in answers for all 6 orange boxes on that sheet. Finally, click on the Predict St. Pete sheet tab and fill in answers for all 5 orange boxes on that sheet.

Six Month Forecast

	July	August	September	October	November	December
	52	72	110	152	195

After being in business for five months and considering the number of rocking horses sold per month, make a prediction about how many will be sold in December. Assume that a linear trend makes the most sense for this data and explore the chart types below before making your prediction. In order to experiment with different graph types in Excel, create each of the following chart types for this data: column, line, pie, scatter. Which one was the least useful in helping you make your prediction about December sales? Please note that there may be more than one correct answer. In fact, this will be the topic of our discussion next week.

How many rocking horses do you predict will be sold in December?

Which chart do you feel did the WORST job helping you predict December sales?

DIRECTIONS Fill in answers for all 6 orange boxes on this sheet.

Place column chart of data in B2:F3 here.

Place line chart of data in B2:F3 here.

Place pie chart of data in B2:F3 here.

Place scatter chart of data in B2:F3 here.

Expansion

		January	February	March	April	May	June	July	August	September	October	November	December	Total
	Brandon	27	77	60	43	78	75	60	89	34	39	77	115	774
	Dunedin	62	92	95	122	82	56	50	124	59	74	112	95	1023
	St. Petersburg	34	134	100	66	136	130	100	158	48	58	134	210	1308

After expanding the Dunedin business to include locations in Brandon and St. Petersburg, investigate operations at all three locations. First create the chart types below for the data in cells B3:B5 and O3:O5. (HINT: Use the Ctrl key to help you highlight all of these cells and only these cells before selecting the chart type.) Then answer the following questions. Which chart was the most useful in helping you answer the question above? Please note that there may be more than one correct answer. In fact, this will be the topic of our discussion next week.

Approximately what percentage of sales (e.g., 0%, 25%, 50%, 100%) is generated by each location? % Brandon % Dunedin % St. Petersburg

Which chart do you feel did the BEST job helping you answer the question above?

DIRECTIONS Fill in answers for all 6 orange boxes on this sheet.

Place column chart of data in B3:B5 and O3:O5 here.

Place line chart of data in in B3:B5 and O3:O5 here.

Place pie chart of data in in B3:B5 and O3:O5 here.

Place scatter chart of data in in B3:B5 and O3:O5 here.

Predict St. Pete

		January	February	March	April	May	June	July	August	September	October	November	December	Total
	Dunedin	22	52	55	62	42	16	10	84	19	34	72	75	543
	Brandon	27	77	60	43	78	75	60	89	34	39	77	115	774
	St. Petersburg	34	134	100	66	136	130	100	158	48	58	134	210	1308

St. Petersberg is the newest location. Which location (Brandon or Dunedin) appears to be the best predictor of sales in St. Petersberg? To answer this, create the first three graphs requested below.

Which location (Brandon or Dunedin) is the best predictor of sales in St. Petersburg?

DIRECTIONS Fill in answers for all 5 orange boxes on this sheet.

Place line chart of data in B3:N5 here.

Place line chart of data in B3:N3 and B5:N5 here.

Place line chart of data in B4:N5 here.

Using only St. Petersburg and the location which is the best predictor of sales in St. Petersburg (your answer to the question above), create a scatter graph here.