Vlookup

During the 2019 baseball season the company expects to sell 38,000 cones of pink bubblegum cotton candy and 43,500 cones of blue raspberry cotton candy. Each cone, regardless of color, sells for $3.50.

The production process is simple. Three tablespoons of flavored floss sugar concentrate are mixed with four pounds of plain white granulated sugar and poured into the cotton candy machine.

1. The first step in making cotton candy is converting the granular sugar into fine filaments. To do this, solid sugar is placed in a large, stainless steel hopper. This hopper has a tapered bottom, which funnels the sugar into the extruder. The extruder is a 

The portable cotton candy machine consists of a large pan with a rotating heating core in the middle. Operators make individual servings at popular venues such as the circus, carnival, and ball park.

rotating metal cylinder, which has holes along its sides and is equipped with a heating element.

2. Inside the extruder, the sugar is heated such that it melts and becomes a molten liquid. The spinning extruder then throws the strands of liquid sugar out in all directions through the holes in its sides. As it exits the extruder, the liquid sugar cools and forms solid strands. These strands, which are the fibers used to make cotton candy, are collected in a large circular pan surrounding the extruder.

3. The strands of cotton candy are then collected by the machine operator. He takes a stiff paper cone and passes it around the sides of the collection pan. As the cone is passed around, the sticky sugar strands adhere to it. When enough is collected on the cone, a clear polymer plastic bag is slipped over the cotton candy and the cone is placed into the carry carts of the walking crowd servers and stationery stands of the food alley snack purveyors.

4. All candy not sold each day is discarded.

There are five direct materials purchased weekly from mid-March through September, with very little inventory of these on hand at the beginning of baseball season and very little at the end. The cost of each is the same as last year’s, $5 per pound for flavored floss sugar concentrate, $0.70 per pound of sugar, $13 per case of 100 paper cones and $28 per case of 1,000 polymer plastic bags.

Cotton Concatenations Inc. employs cooks to make the cotton candy, walking crowd-servers to sell it to fans in their seats and dedicated salespeople in the food alley stationery carts. All employees get paid on the first and fifteenth of each month during the season, with just one paycheck in March and one in October (unless the Phillies make it to the World Series).

Other costs are paid monthly for the seven months of regular baseball season.

· The company must carry workers’ compensation insurance in case any cook gets hurt.

· The cotton candy machines are still being depreciated since they were purchased new two years ago.

· These machines must be repaired and maintained to keep melted sugar from clogging the extruder.

· Helping the cooks are those workers who bring materials from storage, sweep and wash up work areas, assure that cooks have a constant supply of fresh hair nets and aprons.

· There are marketing, distribution and customer service costs as well as

· general and administrative costs.

Your assignment is to

1. prepare a complete “Sales” worksheet.

2. compute cost of each material for beginning inventory, all purchases and ending inventory using VLookup. A lookup table appears on the Direct Materials Data worksheet.

3. complete the “Direct Materials Lookup table” using the excel function SUMIFS for Beginning Direct Materials, Purchases and Ending Direct Materials. Use simple formulas you create to fill in Direct Materials available for use and Direct Materials used.

4. total direct labor on its worksheet.

5. classify each “Other cost” as “Product Cost” or “Period Cost” in the tick marks column provided, then summarize product costs and period costs using SUMIFS to complete the “Other costs Lookup table.”

5. bring all totals from Sales, Direct Materials, Direct Labor and Other costs worksheets to the “Lookup Table” worksheet using VLookup, NOT just copying and pasting the totals.

6. use VLookup once again to prepare the income statement for Cotton Concatenations Inc. using the flow of inventoriable costs worksheet format.