Vlookup

profilenothing3prove
CottonConcatenationsIncdataworksheets1.xlsx
Home>Business & Finance homework help>Accounting homework help>Vlookup

Sales

Lookup table sales
Product selling price qty sold
pink bubblegum cotton candy $ 3.50 38,000
blue raspberry cotton candy $ 3.50 43,500
pink bubblegum cotton candy
blue raspberry cotton candy
Revenue

Data_Direct Materials

Lookup table for computing DM cost
Product purchase cost
pink bubblegum floss sugar concentrate $ 5.00 /pound
blue raspberry floss sugar concentate $ 5.00 /pound
Date Material quantity total cost Tick granulated sugar $ 0.70 /pound
03/18/2019 pink bubblegum floss sugar concentrate 1 Beginning DM cases of polymer plastic bags $ 28.00 /1,000
03/18/2019 blue raspberry floss sugar concentate 1 Beginning DM cases of paper cones $ 13.00 /100
03/18/2019 granulated sugar 200 Beginning DM
03/18/2019 cases of polymer plastic bags 1 Beginning DM DM Lookup table
03/18/2019 cases of paper cones 4 Beginning DM
03/25/2019 pink bubblegum floss sugar concentrate 0 Purchases Beginning DM
03/25/2019 blue raspberry floss sugar concentate 1 Purchases Purchases
03/25/2019 granulated sugar 50 Purchases DM available for use
03/25/2019 cases of polymer plastic bags 5 Purchases Ending DM
03/25/2019 cases of paper cones 60 Purchases DM used
04/01/2019 pink bubblegum floss sugar concentrate 0 Purchases
04/01/2019 blue raspberry floss sugar concentate 1 Purchases
04/01/2019 granulated sugar 50 Purchases
04/01/2019 cases of polymer plastic bags 3 Purchases
04/01/2019 cases of paper cones 30 Purchases
04/05/2019 pink bubblegum floss sugar concentrate 0 Purchases
04/05/2019 blue raspberry floss sugar concentate 0 Purchases
04/05/2019 granulated sugar 0 Purchases
04/05/2019 cases of polymer plastic bags 0 Purchases
04/05/2019 cases of paper cones 0 Purchases
04/12/2019 pink bubblegum floss sugar concentrate 1 Purchases
04/12/2019 blue raspberry floss sugar concentate 1 Purchases
04/12/2019 granulated sugar 100 Purchases
04/12/2019 cases of polymer plastic bags 4 Purchases
04/12/2019 cases of paper cones 40 Purchases
04/19/2019 pink bubblegum floss sugar concentrate 1 Purchases
04/19/2019 blue raspberry floss sugar concentate 1 Purchases
04/19/2019 granulated sugar 100 Purchases
04/19/2019 cases of polymer plastic bags 3 Purchases
04/19/2019 cases of paper cones 30 Purchases
04/26/2019 pink bubblegum floss sugar concentrate 1 Purchases
04/26/2019 blue raspberry floss sugar concentate 1 Purchases
04/26/2019 granulated sugar 100 Purchases
04/26/2019 cases of polymer plastic bags 4 Purchases
04/26/2019 cases of paper cones 40 Purchases
04/30/2019 pink bubblegum floss sugar concentrate 0 Purchases
04/30/2019 blue raspberry floss sugar concentate 0 Purchases
04/30/2019 granulated sugar 0 Purchases
04/30/2019 cases of polymer plastic bags 0 Purchases
04/30/2019 cases of paper cones 0 Purchases
05/07/2019 pink bubblegum floss sugar concentrate 0 Purchases
05/07/2019 blue raspberry floss sugar concentate 1 Purchases
05/07/2019 granulated sugar 50 Purchases
05/07/2019 cases of polymer plastic bags 5 Purchases
05/07/2019 cases of paper cones 50 Purchases
05/13/2019 pink bubblegum floss sugar concentrate 0 Purchases
05/13/2019 blue raspberry floss sugar concentate 1 Purchases
05/13/2019 granulated sugar 50 Purchases
05/13/2019 cases of polymer plastic bags 3 Purchases
05/13/2019 cases of paper cones 30 Purchases
05/20/2019 pink bubblegum floss sugar concentrate 1 Purchases
05/20/2019 blue raspberry floss sugar concentate 2 Purchases
05/20/2019 granulated sugar 150 Purchases
05/20/2019 cases of polymer plastic bags 5 Purchases
05/20/2019 cases of paper cones 60 Purchases
05/27/2019 pink bubblegum floss sugar concentrate 0 Purchases
05/27/2019 blue raspberry floss sugar concentate 0 Purchases
05/27/2019 granulated sugar 0 Purchases
05/27/2019 cases of polymer plastic bags 1 Purchases
05/27/2019 cases of paper cones 10 Purchases
06/03/2019 pink bubblegum floss sugar concentrate 1 Purchases
06/03/2019 blue raspberry floss sugar concentate 1 Purchases
06/03/2019 granulated sugar 100 Purchases
06/03/2019 cases of polymer plastic bags 0 Purchases
06/03/2019 cases of paper cones 3 Purchases
06/10/2019 pink bubblegum floss sugar concentrate 0 Purchases
06/10/2019 blue raspberry floss sugar concentate 1 Purchases
06/10/2019 granulated sugar 50 Purchases
06/10/2019 cases of polymer plastic bags 7 Purchases
06/10/2019 cases of paper cones 65 Purchases
06/17/2019 pink bubblegum floss sugar concentrate 0 Purchases
06/17/2019 blue raspberry floss sugar concentate 0 Purchases
06/17/2019 granulated sugar 0 Purchases
06/17/2019 cases of polymer plastic bags 0 Purchases
06/17/2019 cases of paper cones 0 Purchases
06/24/2019 pink bubblegum floss sugar concentrate 1 Purchases
06/24/2019 blue raspberry floss sugar concentate 1 Purchases
06/24/2019 granulated sugar 100 Purchases
06/24/2019 cases of polymer plastic bags 8 Purchases
06/24/2019 cases of paper cones 80 Purchases
07/01/2019 pink bubblegum floss sugar concentrate 0 Purchases
07/01/2019 blue raspberry floss sugar concentate 1 Purchases
07/01/2019 granulated sugar 50 Purchases
07/01/2019 cases of polymer plastic bags 0 Purchases
07/01/2019 cases of paper cones 0 Purchases
07/08/2019 pink bubblegum floss sugar concentrate 0 Purchases
07/08/2019 blue raspberry floss sugar concentate 0 Purchases
07/08/2019 granulated sugar 0 Purchases
07/08/2019 cases of polymer plastic bags 5 Purchases
07/08/2019 cases of paper cones 50 Purchases
07/15/2019 pink bubblegum floss sugar concentrate 0 Purchases
07/15/2019 blue raspberry floss sugar concentate 1 Purchases
07/15/2019 granulated sugar 50 Purchases
07/15/2019 cases of polymer plastic bags 5 Purchases
07/15/2019 cases of paper cones 50 Purchases
07/22/2019 pink bubblegum floss sugar concentrate 0 Purchases
07/22/2019 blue raspberry floss sugar concentate 0 Purchases
07/22/2019 granulated sugar 0 Purchases
07/22/2019 cases of polymer plastic bags 0 Purchases
07/22/2019 cases of paper cones 0 Purchases
07/29/2019 pink bubblegum floss sugar concentrate 0 Purchases
07/29/2019 blue raspberry floss sugar concentate 0 Purchases
07/29/2019 granulated sugar 0 Purchases
07/29/2019 cases of polymer plastic bags 4 Purchases
07/29/2019 cases of paper cones 40 Purchases
08/05/2019 pink bubblegum floss sugar concentrate 0 Purchases
08/05/2019 blue raspberry floss sugar concentate 1 Purchases
08/05/2019 granulated sugar 50 Purchases
08/05/2019 cases of polymer plastic bags 5 Purchases
08/05/2019 cases of paper cones 50 Purchases
08/12/2019 pink bubblegum floss sugar concentrate 0 Purchases
08/12/2019 blue raspberry floss sugar concentate 0 Purchases
08/12/2019 granulated sugar 0 Purchases
08/12/2019 cases of polymer plastic bags 4 Purchases
08/12/2019 cases of paper cones 0 Purchases
08/19/2019 pink bubblegum floss sugar concentrate 0 Purchases
08/19/2019 blue raspberry floss sugar concentate 1 Purchases
08/19/2019 granulated sugar 50 Purchases
08/19/2019 cases of polymer plastic bags 6 Purchases
08/19/2019 cases of paper cones 60 Purchases
08/26/2019 pink bubblegum floss sugar concentrate 0 Purchases
08/26/2019 blue raspberry floss sugar concentate 1 Purchases
08/26/2019 granulated sugar 50 Purchases
08/26/2019 cases of polymer plastic bags 4 Purchases
08/26/2019 cases of paper cones 40 Purchases
09/02/2019 pink bubblegum floss sugar concentrate 0 Purchases
09/02/2019 blue raspberry floss sugar concentate 0 Purchases
09/02/2019 granulated sugar 0 Purchases
09/02/2019 cases of polymer plastic bags 3 Purchases
09/02/2019 cases of paper cones 30 Purchases
10/01/2019 pink bubblegum floss sugar concentrate 0 Ending DM
10/01/2019 blue raspberry floss sugar concentate 1 Ending DM
10/01/2019 granulated sugar 130 Ending DM
10/01/2019 cases of polymer plastic bags 0.5 Ending DM
10/01/2019 cases of paper cones 1 Ending DM

Data_Direct Labor

DL Lookup table
Date Name Task Gross Pay
03/15/2019 merchandise sales persons $ 1,450
03/15/2019 food alley cooks $ 1,300
03/15/2019 crowd (walking) servers $ 1,400
04/01/2019 merchandise sales persons $ 1,450
04/01/2019 food alley cooks $ 1,300
04/01/2019 crowd (walking) servers $ 1,400
04/15/2019 merchandise sales persons $ 1,450
04/15/2019 food alley cooks $ 1,300
04/15/2019 crowd (walking) servers $ 1,400
05/01/2019 merchandise sales persons $ 1,450
05/01/2019 food alley cooks $ 1,300
05/01/2019 crowd (walking) servers $ 1,400
05/15/2019 merchandise sales persons $ 1,450
05/15/2019 food alley cooks $ 1,300
05/15/2019 crowd (walking) servers $ 1,400
06/01/2019 merchandise sales persons $ 1,450
06/01/2019 food alley cooks $ 1,300
06/01/2019 crowd (walking) servers $ 1,400
06/15/2019 merchandise sales persons $ 1,450
06/15/2019 food alley cooks $ 1,300
06/15/2019 crowd (walking) servers $ 1,400
07/01/2019 merchandise sales persons $ 1,450
07/01/2019 food alley cooks $ 1,300
07/01/2019 crowd (walking) servers $ 1,400
07/15/2019 merchandise sales persons $ 1,450
07/15/2019 food alley cooks $ 1,300
07/15/2019 crowd (walking) servers $ 1,400
08/01/2019 merchandise sales persons $ 1,450
08/01/2019 food alley cooks $ 1,300
08/01/2019 crowd (walking) servers $ 1,400
08/15/2019 merchandise sales persons $ 1,450
08/15/2019 food alley cooks $ 1,300
08/15/2019 crowd (walking) servers $ 1,400
09/01/2019 merchandise sales persons $ 1,450
09/01/2019 food alley cooks $ 1,300
09/01/2019 crowd (walking) servers $ 1,400
09/15/2019 merchandise sales persons $ 1,450
09/15/2019 food alley cooks $ 1,300
09/15/2019 crowd (walking) servers $ 1,400
10/01/2019 merchandise sales persons $ 1,450
10/01/2019 food alley cooks $ 1,300
10/01/2019 crowd (walking) servers $ 1,400
Total Direct Labor

Data other costs

Date Account Description Cost Tick
(product or
period cost?)
03/31/2019 70251 Allocated workers' comp insurance $ 1,000 Other costs Lookup table
03/31/2019 74525 Dep'n- cotton candy machines $ 2,000 Product Cost Manufacturing Overhead
03/31/2019 60541 Repairs & maintenance, machines $ 2,400
03/31/2019 72114 Marketing, Distrib, Cust Service $ 1,250 Period Cost Marketing, Gen & Admin, etc
03/31/2019 60051 General and Admin Costs $ 6,000
03/31/2019 72541 Indirect Manufacturing Labor $ 3,000
04/30/2019 70251 Allocated workers' comp insurance $ 1,000
04/30/2019 74525 Dep'n- cotton candy machines $ 2,000
04/30/2019 60541 Repairs & maintenance, machines $ 3,000
04/30/2019 72114 Marketing, Distrib, Cust Service $ 1,250
04/30/2019 60051 General and Admin Costs $ 6,000
04/30/2019 72541 Indirect Manufacturing Labor $ 3,000
05/31/2019 70251 Allocated workers' comp insurance $ 1,000
05/31/2019 74525 Dep'n- cotton candy machines $ 2,000
05/31/2019 60541 Repairs & maintenance, machines $ 2,500
05/31/2019 72114 Marketing, Distrib, Cust Service $ 1,250
05/31/2019 60051 General and Admin Costs $ 5,700
05/31/2019 72541 Indirect Manufacturing Labor $ 3,000
06/30/2019 70251 Allocated workers' comp insurance $ 1,000
06/30/2019 74525 Dep'n- cotton candy machines $ 2,000
06/30/2019 60541 Repairs & maintenance, machines $ 2,200
06/30/2019 72114 Marketing, Distrib, Cust Service $ 1,250
06/30/2019 60051 General and Admin Costs $ 5,300
06/30/2019 72541 Indirect Manufacturing Labor $ 2,700
07/31/2019 70251 Allocated workers' comp insurance $ 1,000
07/31/2019 74525 Dep'n- cotton candy machines $ 2,000
07/31/2019 60541 Repairs & maintenance, machines $ 3,100
07/31/2019 72114 Marketing, Distrib, Cust Service $ 1,250
07/31/2019 60051 General and Admin Costs $ 5,600
07/31/2019 72541 Indirect Manufacturing Labor $ 3,000
08/31/2019 70251 Allocated workers' comp insurance $ 1,000
08/31/2019 74525 Dep'n- cotton candy machines $ 2,000
08/31/2019 60541 Repairs & maintenance, machines $ 2,700
08/31/2019 72114 Marketing, Distrib, Cust Service $ 1,250
08/31/2019 60051 General and Admin Costs $ 5,800
08/31/2019 72541 Indirect Manufacturing Labor $ 4,000
09/30/2019 70251 Allocated workers' comp insurance $ 1,000
09/30/2019 74525 Dep'n- cotton candy machines $ 2,000
09/30/2019 60541 Repairs & maintenance, machines $ 2,500
09/30/2019 72114 Marketing, Distrib, Cust Service $ 1,250
09/30/2019 60051 General and Admin Costs $ 6,000
09/30/2019 72541 Indirect Manufacturing Labor $ 5,500
$ 112,750

Lookup_Table

Lookup table for income statement
lookup
codes Account Description total
A2 70219 Beginning DM
A3 70239 Ending DM
A4 70249 DM used
A5 WIP beginning
A6 WIP ending
A7 FG beginning
A8 FG ending
d 70229 Purchases
e 60215 Direct Labor
f X1 Manufacturing Overhead
g 72114 Marketing, Gen & Admin, etc
h Revenue

Income statement

DM used
A2 Beginning DM
d Purchases
A3 Ending DM
A4 DM used
total mfg costs
A4 DM used
e Direct Labor
f MOH
total mfg costs
CGM
A5 WIP beginning
total mfg costs
A6 WIP ending
CGM
CGS
A7 FG beginning
CGM
A8 FG ending
CGS
Operating income
h Revenue
CGS
Gross Margin
g Period cost
Operating income