Suggested Budget Sheet

ACCT$2102$–$Fall$2014$ AITB$Company$Case$Project$ Page$1$ $

Fall$2014$ACCT$2102$$ AITB$Company$Case$Project$ Due:$December$5,$2014,$11:59pm$into$TBSquare$ $ Assignment:$ 1. Complete$a$master$budget$for$AITB$Company’s$second$year$in$operation$based$on$the$attached$

assumption$page.$ a. You$will$need$to$complete$all$relevant$supporting$schedules$/$budgets$to$provide$a$final$

cash$budget$(for$each$quarter$and$year$end),$budgeted$income$statement$(for$year$end),$ and$budgeted$balance$sheet$(at$year$end)$for$the$second$year$of$AITB$Company.$

2. Once$the$master$budget$is$complete,$answer$the$supplemental$questions$listed$below.$ 3. Your$final$deliverable$is$a$master$budget$including:$

a. Supporting$budgets$/$schedules$for$the$assumptions$outlined$below$ b. Year$2$cash$budget$(for$each$quarter$and$year$end)$ c. Year$2$budgeted$income$statement$(for$year$end)$$ d. Year$2$budgeted$balance$sheet$(at$year$end)$ e. A$document$including$answers$to$the$supplemental$questions$listed$below.$

4. There$is$no$minimum$or$maximum$page$count.$$ 5. Please$hand$in$a$project$document$that$is$easy$to$follow.$$ 6. You$will$be$submitting$the$final$product$online$into$TSSquare.$To$that$end,$the$budget$and$

supporting$schedules$should$be$completed$in$excel$or$an$equivalent$tool.$Please$complete$the$ answers$to$the$supplemental$questions$in$a$word$processing$tool$or$on$a$clearly$marked$tab$in$ the$excel$(or$equivalent)$file.$Please$do$not$hand$in$scanned,$handwritten$pages.$

7. In$completing$this$project,$start$by$creating$the$supporting$schedules$/$budgets$(listed$as$#1S4$ in$the$assumptions).$Start$with$year$1$quarter$4$information$and$build$a$budget$for$each$ quarter$of$year$two.$You$will$need$some$relevant$information$from$year$3$quarter$one,$so$ include$this,$as$applicable,$in$your$supporting$schedules$/$budgets$as$well.$$

a. I$have$included$a$suggested$starting$point$for$the$sales$budget.$See$the$final$page.$ $ Group$work:$ You$may$work$in$groups$of$up$to$four$people.$Please$make$sure$that$all$names$are$on$the$ submitted$document.$While$you$may$discuss$the$project$with$other$groups,$I$expect$that$each$ group$will$complete$and$handSin$its$own$unique$project$as$per$the$section$on$academic$honesty$ outlined$in$the$syllabus.$Group$members$can$be$from$different$sections$of$the$class.$ $ My$expectation$is$also$that$each$of$you$will$all$actively$participate$in$the$project.$Therefore,$I$am$ including$the$following$incentives:$ • Each$of$you$will$individually$report$participation$for$each$member$(including$yourself).$You$

will$provide$each$member$of$the$team$a$rating$between$0$S$10.$I$will$take$the$average$of$the$ scores$for$each$group$member$(including$yourself)$and$multiply$that$by$the$10$percent$of$the$ total$score$available$for$participation.$In$other$words,$if$you$have$an$average$participation$of$ 8.5,$you$would$receive$8.5$percent$for$participation.$Please$see$the$Grading$section$for$more$ details.$

• I$reserve$the$option$to$include$a$selection$of$questions$on$the$final$exam$related$to$this$project.$$ o These$questions$would$be$straightforward$for$anyone$actively$participating.$

ACCT$2102$–$Fall$2014$ AITB$Company$Case$Project$ Page$2$ $

Grading:$ As$listed$on$the$syllabus,$this$project$is$worth$15%$of$your$total$semester$grade.$ • 35%$of$the$project$grade$will$be$based$on$your$group’s$“reasonable”$attempt$at$completing$the$

assignment.$In$other$words,$if$you$complete$all$the$necessary$components$of$the$budget$(aSd$in$ Assignment$section$#3,$related$to$deliverables$above),$you$will$receive$up$to$35%$of$the$grade.$

• 40%$of$the$project$grade$is$reserved$for$accuracy$of$the$budget.$ • 15%$of$the$project$grade$is$reserved$for$the$completion$and$accuracy$of$the$supplemental$

questions.$ • 10%$of$the$project$score$is$based$on$the$average$participation$value$provided$by$all$group$

members$(including$your$own$score).$This$means$that$individuals$in$the$same$group$can$ receive$differing$scores.$

• Thus,$completing$the$project$earns$up$to$35%$of$the$grade,$completing$it$accurately$earns$up$to$ the$next$40%,$answering$the$supplemental$questions$accurately$earns$up$to$another$15%,$and$ your$participation$in$the$group$makes$up$the$remaining$10%.$

$ Finally,$I$will$reserve$the$Wednesday$before$Thanksgiving$(November$26th)$as$a$working$day$for$ the$project.$Please$bring$your$questions$to$the$normal$class$session$time$and$I$will$spend$the$class$ period$answering$questions$on$the$project.$To$this$end,$I$would$suggest$including$at$least$one$ person$in$your$group$who$is$not$traveling$on$that$Wednesday$so$that$they$can$ask$any$questions$ you$might$have$as$a$group,$and/or$gain$insights$from$other$groups’$questions.$ $

$ $ $

$ $

ACCT$2102$–$Fall$2014$ AITB$Company$Case$Project$ Page$3$ $

Supplemental*Questions:* *

Complete(these(after(you(have(finished(building(your(master(budget(based(on(the(assumptions(in(the( next(section.( ( 1. After$detailed$analysis,$the$AITB$Company$decided$that$they$would$move$to$an$activity$based$

accounting$system$for$allocating$overhead.$AITB$Company$determined$that$they$would$use$the$ following$activity$based$overhead$drivers:$ • 45%$of$the$overhead$to$orders$ • 10%$of$overhead$to$raw$material$(based$on$total$pounds)$ • 40%$of$overhead$to$labor$hours$ • 5%$of$overhead$to$company$sustaining$(not$specific$to$the$individual$products)$ $ WITHOUT$CREATING$NEW$SCHEDULES$or$BUDGETS,$please$discuss$(in$general$terms)$what$ impact$these$new$activity$based$drivers$(verses$the$current$plant$wide$overhead$rate)$would$ have$on:$ • Company$profits?$ • Individual$product$profitability?$

$ 2. Costco$has$asked$the$company$to$produce$Product$B$under$the$Costco$label.$With$the$deal,$

AITB$Company$has$determined$that$they$would$be$able$to$sell$20%$more$than$they$currently$ sell$in$quarters$two$and$three,$but$would$not$sell$any$more$in$quarters$one$and$four$(as$the$ lost$sales$would$offset$in$those$periods).$$ • The$company$would$not$incur$any$additional$overhead$or$general$/$administrative$costs$

(i.e.$overhead$and$GAA$would$not$increase$or$otherwise$be$impacted$by$this$decision).$$$ • If$they$could$sell$each$unit$for$a$unit$price$of$$45.00,$should$they$take$the$deal?$ • If$you$answered$yes,$at$what$price$would$it$NOT$make$economic$sense$to$take$the$deal?$ • If$you$answered$no,$at$what$price$would$it$make$economic$sense$to$take$the$deal?$ • What$other$factors$should$AITB$Company$consider$before$taking$this$deal?$

$ 3. Discuss$the$pros$and$cons$of$outsourcing$AITB$seasonal$help.$Would$you$advise$them$to$

continue$outsourcing$or$build$the$necessary$infrastructure$“in$house”?$ $

4. AITB$Company$issued$common$stock$to$partially$pay$for$cash$flow$needs$in$year$two.$Would$ you$have$issued$stock$or$taken$out$a$larger$loan?$What$are$the$pros/$cons$of$stock$vs.$debt$ financing?$ $

5. AITB$has$more$sales$in$Q1$and$Q4,$than$in$Q2$and$Q3.$$Why$are$the$cash$flows$negative$in$both$ of$those$quarters$when$the$cash$flows$are$positive$in$Q2$and$Q3?$ $

6. The$allowances$for$bad$debt$balance$changed$from$year$one$balance$sheet$to$the$year$two$ balance$sheet.$How$much$did$it$change?$Give$some$analysis$as$to$what$might$have$happened$to$ make$the$balance$change$in$this$manner?$$

$ 7. What$advice$would$you$provide$the$company$to$increase$net$income?$

ACCT$2102$–$Fall$2014$ AITB$Company$Case$Project$ Page$4$ $

Assumptions*for*AITB*Company:* 1)$Sales:$ $

Sales:$ • Year$one$fourth$quarter$sales$for$product$A,$include$415$orders$

o Each$order$averaged$875$units$(for$a$total$of$363,125$units)$ • Year$one$fourth$quarter$sales$for$product$B,$include$116$orders$

o Each$order$averaged$2,998$units$(347,768$units$total)$ • Due$to$an$aggressive$advertising$campaign,$management$believes$that$ALL$sale$orders$(for$

both$products)$in$year$two$will$be$19%$higher$than$in$year$one.$ • AITB$Company$sales$(both$products)$are$seasonal.$Therefore,$sale$orders:$

o In$first$quarter$are$11%$higher$than$in$fourth$quarter$ o In$second$quarter$are$45%$lower$than$in$fourth$quarter$$ o In$third$quarter$are$22%$lower$than$in$fourth$quarter$ o Hint:$calculate$the$increase$in$sales$for$year$two$based$on$fourth$quarter$first$(19%$

increase$from$year$one),$and$then$adjust$quarter’s$one$through$three$of$year$two$based$ on$the$preceding$three$assumptions$for$seasonality$of$sales.$

o Note:$average$units$per$order$are$unaffected.$$ • Round$all$orders$to$the$whole$order$value$(not$the$final$units)$

o NOTE:(DO(NOT(ROUND(ANY(OTHER(VALUES(IN(THE(CASE(BEYOND(THE(ORDER(VALUES( FOR(PRODUCTS(“A”(AND(“B”(

o However,(you(can(present(the(values(as(whole(numbers((i.e.(you(do(not(need(to(show(the( decimal(values(in(the(schedules(/(budgets)(

• Assume$first$quarter$orders$of$year$three$will$be$7%$lower$than$year$two$first$quarter$sales$$ o Round$all$orders$to$the$whole$order$value$(not$the$final$units)$

• AITB$Company$can$sell$product$A$for$$46.00$per$unit,$and$product$B$at$$55.00$per$unit.$ $ Sales$are$collected:$ • 29%$in$cash$ • Of$the$71%$of$credit$sales:$

o 23%$is$collected$in$the$current$quarter$ o 35%$is$collected$in$the$first$quarter$after$the$sales$quarter$ o 38%$is$collected$in$the$second$quarter$after$the$sales$quarter$ o 4%$is$estimated$to$not$be$collected,$therefore:$

! You$will$need$to$set$up$a$bad$debt$expense$in$each$quarter$of$sales$(for$the$4%$of$ sales$that$is$estimated$to$not$be$collected)$$

• For$example,$if$you$have$sales$in$quarter$one$of$$1,000,000$(example$ only),$then$you$would$need$to$create$a$bad$debt$expense$in$Q1$of$$20,000.$$

! Assume$bad$debt$amounts$written$off$are$as$follows:$ • Quarter$one$of$year$two:$$$1,235,841$ • Quarter$two$of$year$two:$$$3,125,878$ • Quarter$three$of$year$two:$$$798,568$ • Quarter$four$of$year$two:$$$50,000$ • Note:$there$will$be$a$balance$in$the$allowance$for$bad$debt$account$at$the$

end$of$year$two.$This$balance$will$likely$be$different$than$the$balance$at$ the$end$of$year$one.$$

ACCT$2102$–$Fall$2014$ AITB$Company$Case$Project$ Page$5$ $

! Hint:$remember$that$bad$debt$expense$does$not$impact$accounts$receivable$ directly.$It$is$a$contraSasset$account.$Accounts$receivable$will$be$impacted$only$ when$you$write$off$bad$debt$amounts.$Therefore,$the$ending$accounts$receivable$ balance$will$be$impacted$by$the$amounts$of$bad$debt$you$write$off$against$the$ allowance$for$bad$debt$while$the$bad$debt$allowance$will$be$impacted$by$both$ the$bad$debt$expense$and$bad$debt$writeSoffs.$

$ • The$$25,065,549$in$YR$1$accounts$receivable$will$be$collected$in$year$two$as$follows:$

o $12,482,882$in$quarter$one$($8,904,001$from$Q4,$YR1$and$$3,578,881$from$Q3,$YR1)$ o $9,667,201$in$quarter$two$(from$Q4$YR1)$ o The$remaining$amount$of$$2,915,466$relates$to$items$that$have$not$been$collected$and$

likely$will$end$up$written$off$as$a$bad$debt$(see$bad$debt$write$off$amounts$above).$* $

$ 2)$Production/Purchases:$ $

$ There$is$no$WIP$or$finished$goods$inventory$(i.e$AITB$Company$plans$to$make$and$sell$all$product$ units$sold$in$each$quarter)$ $ AITB$Company$has$determined$that$it$needs$the$following$raw$materials:$ • 27.50%$of$next$quarter’s$product$A$raw$material$in$ending$raw$material$inventory$$ • 47.5%$of$next$quarter’s$product$B$raw$material$in$ending$raw$material$inventory$$ $ AITB$Company$pays$for$materials:$ • 30%$in$cash$ • Of$the$70%$paid$on$credit:$

o 60%$in$paid$in$the$current$quarter$of$purchase$$ o 40%$is$paid$in$the$first$quarter$following$quarter$of$purchase$

• Note:$year$one$accounts$payable$is$given.$It$is$not$calculated$by$the$values$available$in$this$case.$ $ 3)$Cost$of$Goods$Sold$(COGS):$ Product$cost:$ • Currently,$overhead$is$applied$based$on$pounds$of$raw$material$used$in$production$

o Total$overhead$is$expected$to$be$$5,325,000$in$year$two$ o Assume$that$all$overhead$is$cash$related.$In$other$words,$there$is$no$depreciation$(or$

other$nonScash$expenses)$included$in$the$overhead$amount.$ o Further,$assume$all$overhead$costs$are$paid$in$cash$evenly$in$each$of$the$four$quarters$

• Each$unit$of$product$A$includes:$ o 2.3$pounds$of$raw$material$XM1$ o 3.2$pounds$of$raw$material$XM2$ o 1.9$hours$of$labor$

• Each$unit$of$Product$B$includes:$ o 3.5$pounds$of$XM1$ o 5.5$pounds$of$XM2$ o 1.6$hour$of$labor$

• The$cost$of$XM1$is$$4.50$per$pound$

ACCT$2102$–$Fall$2014$ AITB$Company$Case$Project$ Page$6$ $

• The$cost$of$XM2$is$$2.45$per$pound$ • Labor$is$$7.25$per$hour$+$18%$in$employer$paid$fringes$(not$included$in$overhead).$Labor$is$

paid$in$the$quarter$it$is$earned.$ $ 4)$Fixed$Assets:$ $

In$year$two:$ • Assume$the$company$depreciates$its$assets$quarterly.$Any$sales/purchases$that$occur$during$

the$quarter$are$accounted$for$as$if$the$transaction$occurred$on$the$first$day$of$the$quarter.$ • AITB$Company$expects$to$purchase,$in$cash,$equipment$valued$at$$750,000,$during$the$first$

quarter$(assume$the$first$day$of$the$quarter$for$depreciation$purposes)$ • Sell$equipment$on$the$last$day$of$the$second$quarter,$for$$180,000$cash.$The$equipment$has$an$

original$purchase$price$of$$400,000$and$current$(end$of$year$one)$depreciation$of$$240,000$ • Assume$straight$line$depreciation,$over$10$years,$for$all$fixed$assets$(no$salvage$value)$ • Fixed$assets$at$the$end$of$year$one$(that$will$not$be$sold)$have$4$years$of$their$10$year$life$

remaining$ $ 4)$Other$(Specific$“Other”$schedule$not$necessary.$These$impact$other$schedules):$ $

Selling$and$administrative$costs$are$as$follows:$ • Commissions$are$3.95%$of$sales$ • Marketing$is$$50,000$per$quarter$+$5.50%$of$sales$ • Other$administration$costs$are$$18,000$per$quarter$ • AITB$Company$contracts$with$an$outside$vendor$to$provide$seasonable$help$in$the$first$and$

fourth$quarters.$They$pay$this$vendor$$1,250,000$in$the$first$quarter$and$$1,750,000$in$the$ fourth$quarter.$

$ Minimum$Cash$Balance:$ AITB$Company$would$like$to$maintain$a$minimum$cash$balance$of$$100,000$at$the$end$of$each$ quarter.$In$YR2,$AITB$has$an$agreement$with$a$local$bank$that$allows$the$company$to$borrow$any$ amount$necessary$at$the$beginning$of$each$quarter,$up$to$a$total$loan$balance$of$$7,500,000,$in( increments(of($50,000.$The$interest$rate$on$these$loans$is$2.15$%$per$quarter$(nonScompounded).$ AITB$Company,$as$far$as$it$is$able,$repays$any$outstanding$loan$plus$accumulated$interest$at$the$ end$of$the$quarter.$For$interest$calculation$purposes,$assume$that$any$loan$is$taken$out$at$the$ beginning$of$the$deficient$quarter$and$paid$back$at$the$end$of$any$quarter$with$a$surplus.$Assume$ interest$is$paid$when$the$loan$is$repaid,$EXCEPT$at$yearSend.$At$yearSend,$all$accrued$interest$is$ paid$so$that$there$is$no$accrued$interest$carried$between$years.$

• Therefore,$all$accrued$interest$in$year$one$was$paid$in$year$one$for$any$outstanding$loans$ • And,$any$interest$owed$based$on$year$2$loans$are$paid$at$yearSend,$if$applicable$

$ Dividends:$ AITB$Company$will$pay$dividends$of$$1,000,000,$during$the$third$quarter$of$year$two.$AITB$ Company$will$also$sell$$615,000$in$additional$common$stock$during$the$fourth$quarter$of$year$ two.$ $ Taxes:$ Ignore$all$tax$implications$while$completing$the$budget.$ $

ACCT$2102$–$Fall$2014$ AITB$Company$Case$Project$ Page$7$ $

Year$One$Balance$Sheet:$ $ $ $

$ $ $

ACCT$2102$–$Fall$2014$ AITB$Company$Case$Project$ Page$8$ $

$ $

Suggested(Sales(Budget(Starting(Point( $ $

$