Cost Accounting Excel 2

Please assist me. I want the excel file to be created today because the lecturers check that.

Thanks!

ACC#3400:#Summer#2015# Excel#project#2:#30#points# # The#purpose#of#this#assignment#is#to#provide#an#introduction#to#Excel's#whatDif#analysis.## In# cost# accounting# determining# the# relationships# between# cost,# volume# and# profit# are# very#important.#This#assignment#will#have#you#use#scenarios#and#goal#seek#to#calculate# breakeven#and#changes#in#cost#and#volume.# ! Introduction!to!what.if!analysis!(using!Excel!2007)! By#using#whatDif#analysis#tools#in#Microsoft#Office#Excel,#you#can#use#several#different# sets#of#values#in#one#or#more#formulas#to#explore#all#the#various#results.# For#example,#you#can#do#whatDif#analysis#to#build#two#budgets#that#each#assumes#a# certain#level#of#revenue.#Or,#you#can#specify#a#result#that#you#want#a#formula#to#produce,# and#then#determine#what#sets#of#values#will#produce#that#result.#Excel#provides#several# different#tools#to#help#you#perform#the#type#of#analysis#that#fits#your#needs.# ! Overview! WhatDif#analysis#is#the#process#of#changing#the#values#in#cells#to#see#how#those# changes#will#affect#the#outcome#of#formulas#on#the#worksheet.# Three#kinds#of#whatDif#analysis#tools#come#with#Excel:#scenarios,#data#tables,#and#Goal# Seek.#Scenarios#and#data#tables#take#sets#of#input#values#and#determine#possible# results.#A#data#table#works#only#with#one#or#two#variables,#but#it#can#accept#many# different#values#for#those#variables.#A#scenario#can#have#multiple#variables,#but#it#can# accommodate#only#up#to#32#values.#Goal#Seek#works#differently#from#scenarios#and# data#tables#in#that#it#takes#a#result#and#determines#possible#input#values#that#produce# that#result.# # Use!scenarios!to!consider!many!different!variables! A#scenario#is#a#set#of#values#that#Excel#saves#and#can#substitute#automatically#in#cells# on#a#worksheet.#You#can#create#and#save#different#groups#of#values#on#a#worksheet#and# then#switch#to#any#of#these#new#scenarios#to#view#different#results.# # For#example,#suppose#you#have#two#budget#scenarios:#a#worst#case#and#a#best#case.# You#can#use#the#Scenario#Manager#to#create#both#scenarios#on#the#same#worksheet,# and#then#switch#between#them.#For#each#scenario,#you#specify#the#cells#that#change# and#the#values#to#use#for#that#scenario.#When#you#switch#between#scenarios,#the#result# cell#changes#to#reflect#the#different#changing#cell#values.# # # # # Worst#case#scenario# # #Changing#cells# #Result#cell# # Best#case#scenario# # #Changing#cells# #Result#cell# # After#you#have#created#all#the#scenarios#that#you#need,#you#can#create#a#scenario# summary#report#that#incorporates#information#from#those#scenarios.#A#scenario#report# displays#all#the#scenario#information#in#one#table#on#a#new#worksheet.# # Scenario#summary#report# # # #NOTE# ##Scenario#reports#are#not#automatically#recalculated.#If#you#change#the#values# of#a#scenario,#those#changes#will#not#show#up#in#an#existing#summary#report.#Instead,# you#must#create#a#new#summary#report.# # Create!a!scenario! Before#you#create#a#scenario,#you#should#have#an#initial#set#of#values#already#on#the# worksheet.#To#make#scenario#summary#reports#easier#to#read,#you#should#also#consider# naming#the#cells#that#you#plan#to#use#in#scenarios.## # 1.# 2.# 3.# 4.# 5.# 6.# 7.# 8.# On#the#Data#tab,#in#the#Data!Tools#group,#click#What.If!Analysis,#and#then#click# Scenario!Manager.# Click#Add.# In#the#Scenario!name#box,#type#a#name#for#the#scenario.# In#the#Changing!cells#box,#enter#the#references#for#the#cells#that#you#want#to# specify#in#your#scenario.#For#example,#if#you#want#to#see#how#changing#the#values#of# cells#B1#and#B2#will#affect#the#outcome#of#a#formula#based#on#those#cells,#enter# B1,B2.# #NOTE# ##To#preserve#the#initial#values#for#the#changing#cells,#add#a#scenario# that#uses#those#values#before#you#create#additional#scenarios#that#use#different# values.# Click#OK.# In#the#Scenario!Values#dialog#box,#type#the#values#that#you#want#to#use#in#the# changing#cells#for#this#scenario.# To#create#the#scenario,#click#OK.# If#you#want#to#create#additional#scenarios,#repeat#steps#2#through#8.#After#you# finish#creating#scenarios,#click#OK,#and#then#click#Close#in#the#Scenario!Manager# dialog#box.# # Display!a!scenario! When#you#display#a#scenario,#you#switch#to#the#set#of#values#that#are#saved#as#part#of# that#scenario.#The#scenario#values#are#displayed#in#the#cells#that#change#from#scenario# to#scenario,#in#addition#to#the#results#cells.#For#example,#using#the#preceding#scenarios,# if#you#display#the#Best#Case#scenario,#cell#B1#displays#150000,#cell#B2#displays#26000,# and#cell#B3#displays#124000.# 1.# On#the#Data#tab,#in#the#Data!Tools#group,#click#What.If!Analysis,#and#then#click# Scenario!Manager.# 2.# Click#the#name#of#the#scenario#that#you#want#to#display.# 3.# Click#Show.# #NOTE# ##After#you#close#the#Scenario!Manager#dialog#box,#the#values#from# the#last#scenario#that#you#displayed#remain#on#the#worksheet.#If#you#saved#your# initial#values#as#a#scenario,#you#can#display#those#values#before#you#close#the# Scenario!Manager#dialog#box.# # Create!a!scenario!summary!report! 1.# On#the#Data#tab,#in#the#Data!Tools#group,#click#What.If!Analysis,#and#then#click# Scenario!Manager.# 2.# Click#Summary.# 3.# Click#Scenario!summary#or#Scenario!PivotTable!report.# 4.# In#the#Result!cells#box,#enter#the#references#for#the#cells#that#refer#to#cells#whose# values#are#changed#by#the#scenarios.#Separate#multiple#references#with#commas.# ! ! Use!Goal!Seek!to!find!out!how!to!get!a!desired!result! If#you#know#the#result#that#you#want#from#a#formula,#but#you#are#not#sure#what#input# value#the#formula#requires#to#get#that#result,#you#can#use#the#Goal#Seek#feature.#For# example,#suppose#that#you#need#to#borrow#some#money.#You#know#how#much#money# you#want,#how#long#a#period#you#want#in#which#to#pay#off#the#loan,#and#how#much#you# can#afford#to#pay#each#month.#You#can#use#Goal#Seek#to#determine#what#interest#rate## you#must#secure#in#order#to#meet#your#loan#goal.# # # # # #NOTE# ##Goal#Seek#works#with#only#one#variable#input#value.#If#you#want#to#determine# more#than#one#input#value,#for#example,#the#loan#amount#and#the#monthly#payment# amount#for#a#loan,#you#should#instead#use#the#Solver#addDin.## # Let's#look#at#the#example,#stepDbyDstep.# Because#you#want#to#calculate#the#loan#interest#rate#needed#to#meet#your#goal,#you#use# the#PMT#function.#The#PMT#function#calculates#a#monthly#payment#amount.#In#this# example,#the#monthly#payment#amount#is#the#goal#that#you#seek.# PREPARE#THE#WORKSHEET# 1.# Open#a#new,#blank#worksheet.# 2.# First,#add#some#labels#in#the#first#column#to#make#it#easier#to#read#the#worksheet.# 1.# In#cell#A1,#type#Loan!Amount.# 2.# In#cell#A2,#type#Term!in!Months.# 3.# In#cell#A3,#type#Interest!Rate.# 4.# In#cell#A4,#type#Payment.# 3.# Next,#add#the#values#that#you#know.## 1.# In#cell#B1,#type#100000.#This#is#the#amount#that#you#want#to#borrow.# 2.# In#cell#B2,#type#180.#This#is#the#number#of#months#that#you#want#to#pay#off# the#loan.# #NOTE# ##Although#you#know#the#payment#amount#that#you#want,#you#do#not# enter#it#as#a#value,#because#the#payment#amount#is#a#result#of#the#formula.# Instead,#you#add#the#formula#to#the#worksheet#and#specify#the#payment#value# at#a#later#step,#when#you#use#Goal#Seek.# 4.# Next,#add#the#formula#for#which#you#have#a#goal.#For#the#example,#use#the#PMT# function:# # In#cell#B4,#type#=PMT(B3/12,B2,B1).#This#formula#calculates#the#payment# amount.#In#this#example,#you#want#to#pay#$900#each#month.#You#don't#enter#that# amount#here,#because#you#want#to#use#Goal#Seek#to#determine#the#interest#rate,# and#Goal#Seek#requires#that#you#start#with#a#formula.# The#formula#refers#to#cells#B1#and#B2,#which#contain#values#that#you# specified#in#preceding#steps.#The#formula#also#refers#to#cell#B3,#which#is# where#you#will#specify#that#Goal#Seek#put#the#interest#rate.#The#formula# divides#the#value#in#B3#by#12#because#you#specified#a#monthly#payment,# and#the#PMT#function#assumes#an#annual#interest#rate.# Because#there#is#no#value#in#cell#B3,#Excel#assumes#a#0%#interest#rate# and,#using#the#values#in#the#example,#returns#a#payment#of#$555.56.#You# can#ignore#that#value#for#now.# 5.# Finally,#format#the#target#cell#(B3)#so#that#it#displays#the#result#as#a#percentage.# 1.# On#the#Home#tab,#in#the#Number#group,#click#Percentage.# 2.# Click#Increase!Decimal#or#Decrease!Decimal#to#set#the#number#of#decimal# places.# USE#GOAL#SEEK#TO#DETERMINE#THE#INTEREST#RATE# 1.# On#the#Data#tab,#in#the#Data!Tools#group,#click#What.If!Analysis,#and#then#click# Goal!Seek.# 2.# In#the#Set!cell#box,#enter#the#reference#for#the#cell#that#contains#the#formula#that# you#want#to#resolve.#In#the#example,#this#reference#is#cell#B4.# 3.# In#the#To!value#box,#type#the#formula#result#that#you#want.#In#the#example,#this#is# D900.#Note#that#this#number#is#negative#because#it#represents#a#payment.# 4.# In#the#By!changing!cell#box,#enter#the#reference#for#the#cell#that#contains#the# value#that#you#want#to#adjust.#In#the#example,#this#reference#is#cell#B3.# 1.# #NOTE# ##The#cell#that#Goal#Seek#changes#must#be#referenced#by#the# formula#in#the#cell#that#you#specified#in#the#Set!cell#box.# 5.# Click#OK.# Goal#Seek#runs#and#produces#a#result,#as#shown#in#the#following#illustration.# # # ! Assignment! Going#Across#is#a#ferry#boat#operator.##Going#Across#currently#charges#$0.60#per# passenger.##On#an#average#day,#toll#revenue#is#$1470.##Currently#variable#cost#is#$0.25# per#passenger#and#fixed#cost#is#$1420.### Required:# 1.# Enter#this#information#into#the#\"Data#Table\"#in#your#Excel2Template#(Hint#you#will# need#to#calculate#the#number#of#passengers).# 2.# Set#up#formulas#to#calculate,#Revenue,#Cost,#Income# 3.# Use#formulas#to#complete#the#contribution#margin#income#statement.# 4.# Use#scenario#manager#to#save#this#original#data#that#you#added#to#the#Data# Table.##[name#this#scenario:#Original]#Hint:#only#save#the#four#variables#in#the# data#table.##If#you#put#a#formula#in#any#of#these#cells,#allow#scenario#manager#to# change#these#values#to#constants.# 5.# Use#Goal#Seek#to#find#out#how#many#passengers#are#necessary#to#breakeven.# (Hint:#you#will#need#to#set#income#to#0)# 6.# Use#scenario#manager#to#save#this#breakeven#information#[name#this#scenario:# BE#Original]#*note#if#passengers#are#not#whole#round#up# 7.# Evaluate#the#following#changes#and#report#their#affects#on#income.#(Each! scenario!is!independent,!so!return!to!the!original!scenario!before!making! changes)# a.# Toll#increases#to#$.70,#but#passengers#fall#by#10%#(use#scenario#manager# to#save#this#as:#Scenario#One)# b.# Fixed#cost#decrease#by#$600#and#the#variable#rate#per#passenger# increases#to#$0.30#(use#scenario#manager#to#save#this#as:#Scenario#Two)# c.# Fixed#cost#increases#by#$500#and#the#variable#rate#per#passenger# decreases#to#$0.10#(use#scenario#manager#to#save#this#as:#Scenario# Three)# 8.# Use#goal#seek#to#find#out#how#many#passengers#are#necessary#to#breakeven# under#scenario#two#(use#scenario#manager#to#save#this#as:#BE#Scenario#Two#)# 9.# Use#goal#seek#to#find#out#how#many#passengers#are#necessary#to#breakeven# under#scenario#three#(use#scenario#manager#to#save#this#as:#BE#Scenario#Three)# 10.##Evaluate#passenger#rates#on#scenario#two# a.# Using#scenario#two#change#passengers#to#6000#(use#scenario#manager#to# save#this#as:#6000#Scenario#Two)# b.# Using#scenario#two#change#passengers#to#2000#(use#scenario#manager#to# save#this#as:#2000#Scenario#Two)# 11.#Evaluate#passenger#rates#on#scenario#three# a.# Using#scenario#three#change#passengers#to#6000#(use#scenario#manager# to#save#this#as:#6000#Scenario#Three)# b.# Using#scenario#Three#change#passengers#to#2000#(use#scenario#manager# to#save#this#as:#2000#Scenario#Three)# 12.#Create#scenario#summary,#include#revenue,#cost,#and#income#in#the#results#(set# result#cells#as#the#cells#that#contain#this#information)# 13.#Set#up#the#scenario#summary#table#to#print#on#one#page.### 14.#Create#a#table#(using#the#data#in#the#scenario#summary#table)#that#highlights#the# tradeoffs#between#fixed#and#variable#cost#(cost#structure).##Hint:##you#will#need#to# use#the#passenger#combinations#of#scenario#two#and#three.##Note#you#do#not# need#to#create#a#pivot#table#or#a#data#table,#just#a#simple#table#that#illustrates#the# tradeoffs.### 15.#Save#your#Excel#file#in#a#manner#that#contains#your#name#(example:## Excel1_lmpleis.xls)# 16.#Submit#your#Excel#file#through#Blackboard# Please#remember#that#the#work#you#submit#is#expected#to#be#your#own#and#created# during#this#semester.##Please#review#the#syllabus#and/or#contact#me#if#you#have#any# questions.# # # DATA TABLE Toll Variable Cost Per Passenger Passengers Fixed Costs Revenue Cost Income CM Income Statement Revenue Variable Cost Contribution Margin Fixed Cost Income ENTER YOUR NAME HERE Set up formulas to calculate these amounts Set the CM Income statement up using formulas