Please see the attachment for the instruction and example. Attached is your assigned Excel Financial Case (Financial Case_yourinitials.xlsx). You may have to scroll down to see it.There is a study guide on the Content tab to help you with this assignment.Do not turn the study guide in as it is only for your use!
excel_financial_case_study_guide.xlsx
excel_financial_case_study_guide_2_.xlsx
financial_case_la_1.xlsx
Unformatted Attachment Preview
Excel Financial Case Study Guide
For your use only – Do not submit for a grade
This study guide is for your use only and is no
***IMPORTANT**
Complete the Income Statement first, complete the Statement of Owners’ Equity second, complete the Balance S
Your Individually assigned Excel Financial case is located on the “Assignments” link/tab on the Falcon online Portal – see pic
The 2013 Financials for Lovelady Travel Services worksheet tab labeled “2013 Financials” for informationally purposes only
information on the “2014 Financials” Statement of Cash Flows.
1) Complete 2014 Financials for Lovelady Travel Services-tab labled “2014 Financials” – An Answer key is available on the “201
Complete the Income Statement first , complete the Statement of Owners’ Equity second , complete the Balance Sh
For detailed instructions view your individually assigned Excel Financial Case – “Information” worksheet. See picture above
2) Review the “2014 Income Statement” worksheet to understand how to compute the “2014 Income Statement” workshee
case. On your individually assigned Excel Financial Case’s “12 Month Income Statement” worksheet you will complete th
questions. This study guide only gives you an example of how to complete the computational part not the questions (Scro
example of how to complete the computations).
3) Review the “Cash Analysis” worksheet to understand how to compute the “Cash Analysis” worksheet on your individually
case. On your individually assigned Excel Financial Case’s “Cash Analysis” worksheet you will complete the computations
questions. This study guide only gives you an example of how to complete the computational part not the questions (Scro
example of how to complete the computations).
Note: To see more worksheets use the bottom right hand corner arrow button to scroll to the right
Be Careful not to use Study Guide information on your graded Excel Financia
cial Case Study Guide
– Do not submit for a grade
e only and is not to be submitted!!
MPORTANT**
second, complete the Balance Sheet third, and complete the Statement of Cash Flows last.
he Falcon online Portal – see picture below how to access your assignment.
r informationally purposes only! You will use some of that
swer key is available on the “2014 Financial Answer Key” worksheet
second , complete the Balance Sheet third , and complete the Statement of Cash Flows last .
n” worksheet. See picture above.
4 Income Statement” worksheet on your individually assigned Excel Financial
worksheet you will complete the computations as well as answer the three
nal part not the questions (Scroll to the right of the workbook to see an
worksheet on your individually assigned Excel Financial
will complete the computations as well as answer the four
nal part not the questions (Scroll to the right of the workbook to see an
raded Excel Financial Case assignment!!!
This study guide is for your use only and is not to be
The account balances of Lovelady Travel Services at December 31, 2013 are listed below:
Accounts Payable
$12,000
Accounts Receivable
Cash
Computer Equip
Fees Earned Revenue
Rent Expense
6,000
16,000
13,000
47,000
6,000
Lovelady, Capital
(beginning balance)
Supplies Inventory
Taxes Expense
Utilities Expense
Wages Expense
Supplies Expense
Depreciation Expense
$10,000
2,000
300
4,000
21,000
700
5,200
Lovelady Travel Services
Balance Sheet
December 31, 2013
Assets
Cash
Accounts Receivable
Computer Equipment
Accumulated Depreciation
Supplies
Total Assets
$
$
16,000
6,000
13,000
(5,200)
2,000
31,800
Liabilities
Accounts Payable
Total Liabilities
Owner’s Equity
Lovelady,Capital
Total Owner’s Equity
Total Liabilities and
Owner’s Equity
$
$
12,000
12,000
$
19,800
19,800
$
31,800
$
47,000
Lovelady Travel Services
Income Statement
For the Year Ended December 31, 2013
Fees Earned Revenue
Operating Expenses:
Wages
Expense
Rent Expense
Utilities
Expense
$
21,000
6,000
4,000
Supplies
Expense
Depreciation
Expense
Taxes
Expense
Total Operating Expenses
Net Income
700
5,200
300
$
$
37,200
9,800
$
$
10,000
9,800
19,800
$
9,800
$
5,200
(2,000)
3,500
16,500
Lovelady Travel Services
Statement of Owner’s Equity
For the Year Ended December 31, 2013
Lovelady, Capital 1/1/13
Net Income for the year
Lovelady, Capital, 12/31/13
Lovelady Travel Services
Statement of Cash Flows
For the Year Ended December 31, 2013
Cash Flows from Operating Activities:
Net Income
Add (Deduct) items not affecting cash:
Depreciation Expense
(Increase)Decrease Accounts Receivable
(Increase)Decrease Inventory
(Decrease)Increase Accounts Payable
Net cash provided by operating activities
Cash Flows from Investing Activities:
Proceeds from sale of Equipment
Investment in Equipment
Net cash provided by investing activities
Cash Flows from Financing Activities:
Long-term borrrowing
Payment of long-term debt
Payment of dividends on common stock
Net cash provided by financing activities
Increase (Decrease) in cash
$
$
$
(13,000)
(13,000)
$
–
$
3,500
Cash balance, December 31, 2012
Cash balance, December 31, 2013
$
12,500
16,000
d is not to be submitted!!
This study guide is for your use only and is not to
The account balances of Lovelady Travel Services at December 31, 2014 are listed below:
Accounts Payable
$10,000
Accounts Receivable
Cash
Computer Equip
Fees Earned Revenue
Rent Expense
Accumulated Depreciation
Inventory
Vehicle
19,000
2,400
13,000
42,000
6,000
11,120
3,600
12,000
Lovelady, Capital
(beginning balance)
Supplies Inventory
Taxes Expense
Utilities Expense
Wages Expense
Supplies Expense
Depreciation Expense
Bank Loan
Dividends
Lovelady Travel Services
Balance Sheet
December 31, 2014
Assets
Cash
Accounts Receivable
Computer Equipment
Vehicle
Accumulated Depreciation
Inventory
Supplies Inventory
$
–
Total Assets
Liabilities
Accounts Payable
Bank Loan
Total Liabilities
Owner’s Equity
Lovelady,Capital
Total Owner’s Equity
Total Liabilities and
Owner’s Equity
Lovelady Travel Services
Income Statement
For the Year Ended December 31, 2014
Fees Earned Revenue
Operating Expenses:
Wages
Expense
Rent Expense
$
–
Utilities
Expense
Supplies
Expense
Depreciation
Expense
Taxes
Expense
Total Operating Expenses
Net Income(Loss)
Lovelady Travel Services
Statement of Owner’s Equity
For the Year Ended December 31, 2014
Lovelady, Capital 1/1/14
Dividend
Net Income(Loss) for the year
Lovelady, Capital, 12/31/14
Lovelady Travel Services
Statement of Cash Flows
For the Year Ended December 31, 2014
Cash Flows from Operating Activities:
Net Income(Loss)
Add (Deduct) items not affecting cash:
Depreciation Expense
(Increase)Decrease Accounts Receivable
(Increase)Decrease Inventory
(Decrease)Increase Accounts Payable
(Increase)Decrease Supplies
Net cash provided by operating activities
Cash Flows from Investing Activities:
Proceeds from sale of Equipment
Investment in Equipment
Net cash provided by investing activities
Cash Flows from Financing Activities:
–
Long-term borrrowing
Payment of long-term debt
Payment of dividends on common stock
Net cash provided by financing activities
Increase (Decrease) in cash
Cash balance, December 31, 2013
Cash balance, December 31, 2014
and is not to be submitted!!
$19,800
2,500
500
3,000
19,000
1,000
5,920
10,000
5,000
$
$
–
$
$
–
$
–
$
–
$
$
–
$
$
–
$
–
$
$
$
$
–
2013
2014 Difference
–
6,000.00
–
19,000.00
3,600.00
(13,000.00)
(3,600.00)
–
12,000.00
2,000.00
10,000.00
2,500.00
(2,000.00)
(500.00)
–
If the difference from prior year is an increase in A/R, Inventory, o
are asset accounts) then that difference is subtracted on cash flow
from prior year is an increase in A/P (remember accounts payable
differently than asset accounts) then that difference is added on th
If the difference from prior year is a decrease in A/R, Inventory, or
are asset accounts) then that difference is added on the cash flow
from prior year is a decrease in A/P (remember accounts payable
$
$
$
$
–
are asset accounts) then that difference is added on the cash flow
from prior year is a decrease in A/P (remember accounts payable
differently than asset accounts) then that difference is subtracted
Below is information that will help you complete the operating sec
Note: You will need to use some 2013 information
Example: 2013 A/R $6,000 – 2014 A/R $19,000 is a $13,000 A/
2013 A/P $12,000 – 2014 A/P $10,000 is a $2,000 A
Assets
2013 A/R
6,000
2014 A/R
19,000
Increase to
Cash Flow
Statement
Note: when
you see
(brackets)
you will
(13,000)
subtract
from the
cash flow
statement.
(increase)
(increase)
A/R -Accounts Receivable
Inventory
(decrease)
(increase)
A/P-Accounts Payable
Supplies Inventory
ncrease in A/R, Inventory, or Supplies Inventory (remember these
ce is subtracted on cash flow statement, conversely, if the difference
remember accounts payable is a liability account so it behaves
that difference is added on the cash flow statement.
ecrease in A/R, Inventory, or Supplies Inventory (remember these
nce is added on the cash flow statement, conversely, if the difference
emember accounts payable is a liability account so it behaves
nce is added on the cash flow statement, conversely, if the difference
emember accounts payable is a liability account so it behaves
that difference is subtracted on the cash flow statement.
u complete the operating section of the statement of cash flows:
013 information
A/R $19,000 is a $13,000 A/R increase but subtracted on the Cash Flow Statement
14 A/P $10,000 is a $2,000 A/P decrease and subtracted on the Cash Flow Statement
Liabilities
2013 A/P
12,000
2014 A/P
10,000
Decrease
to Cash
Flow
Statement
(2,000)
This study guide is for your use only and is not t
Study Guide Answers:
The account balances of Lovelady Travel Services at December 31, 2014 are listed below:
Accounts Payable
$10,000
Accounts Receivable
Cash
Computer Equip
Fees Earned Revenue
Rent Expense
Accumulated Depreciation
Inventory
Vehicle
19,000
2,400
13,000
42,000
6,000
11,120
3,600
12,000
Lovelady, Capital
(beginning balance)
Supplies Inventory
Taxes Expense
Utilities Expense
Wages Expense
Supplies Expense
Depreciation Expense
Bank Loan
Dividends
Lovelady Travel Services
Balance Sheet
December 31, 2014
Assets
Cash
Accounts Receivable
Computer Equipment
Vehicle
Accumulated Depreciation
Inventory
Supplies Inventory
Total Assets
$
$
2,400
19,000
13,000
12,000
(11,120)
3,600
2,500
41,380
Liabilities
Accounts Payable
Bank Loan
Total Liabilities
Owner’s Equity
Lovelady,Capital
Total Owner’s Equity
Total Liabilities and
Owner’s Equity
Total Assets MUST agree to Total Liabilities and Owner’s Equity or the entire balance sheet is wrong!!!!
Lovelady Travel Services
Income Statement
For the Year Ended December 31, 2014
Fees Earned Revenue
Operating Expenses:
Wages
Expense
$
19,000
Rent Expense
6,000
Utilities
Expense
Supplies
Expense
Depreciation
Expense
Taxes
Expense
Total Operating Expenses
3,000
Net Income(Loss)
Lovelady Travel Services
Statement of Owner’s Equity
For the Year Ended December 31, 2014
Lovelady, Capital 1/1/14
Dividend
Net Income (Loss)for the year
Lovelady, Capital, 12/31/14
Lovelady Travel Services
Statement of Cash Flows
For the Year Ended December 31, 2014
Cash Flows from Operating Activities:
Net Income(Loss)
Add (Deduct) items not affecting cash:
Depreciation Expense
(Increase)Decrease Accounts Receivable
(Increase)Decrease Inventory
(Decrease)Increase Accounts Payable
(Increase)Decrease Supplies
Net cash provided by operating activities
Cash Flows from Investing Activities:
Proceeds from sale of Equipment
Investment in Equipment
Net cash provided by investing activities
1,000
5,920
500
Cash Flows from Financing Activities:
Long-term borrrowing
Payment of long-term debt
Payment of dividends on common stock
Net cash provided by financing activities
Increase (Decrease) in cash
Cash balance, December 31, 2013
Cash balance, December 31, 2014
e only and is not to be submitted!!
$19,800
2,500
500
3,000
19,000
1,000
5,920
10,000
5,000
$
$
10,000
10,000
20,000
$
$
21,380
21,380
$
41,380
ire balance sheet is wrong!!!!
$
42,000
$
$
35,420
6,580
$
$
19,800
(5,000)
6,580
$
21,380
$
6,580
$
5,920
(13,000)
(3,600)
(2,000)
(500)
(6,600)
$
$
(12,000)
(12,000)
2013
2014
6,000.00
12,000.00
2,000.00
19,000.00
3,600.00
10,000.00
2,500.00
Difference
(13,000.00)
(3,600.00)
(2,000.00)
(500.00)
Change
increase
increase
decrease
increase
A/R
Inv
A/P
Supp Inv.
$
$
$
$
10,000
(5,000)
5,000
(13,600)
16,000
2,400
This study guide is for your use only and is no
Make sure you answer question 1-3 on your individually assigned Excel Financial Case; this study guide does not provide th
You assigned questions 1-3 will be given on your individually as
The data below is just available to help you understand how to
of this assignment.
Lovelady Travel Services
12 Month Revenue and Expenses
2014
Fees Earned
Wages
Expense
Rent Expense
Utilities
Expense
Supplies
Expense
Depreciation
Expense
Taxes
Expense
Net Income
or (Loss)
$
JAN
FEB
MAR
APR
MAY
JUN
JUL
42000
3,500.00
3,500.00
3,500.00
3,500.00
3,500.00
3,500.00
3,500.00
19000
6000
1,583.33
1,583.33
1,583.33
1,583.33
1,583.33
1,583.33
1,583.33
500.00
500.00
500.00
500.00
500.00
500.00
500.00
3000
250.00
250.00
250.00
250.00
250.00
250.00
250.00
1000
83.33
83.33
83.33
83.33
83.33
83.33
83.33
5920
493.33
493.33
493.33
493.33
493.33
493.33
493.33
500
41.67
41.67
41.67
41.67
41.67
41.67
41.67
548 $
548 $
548 $
548 $
548 $
548 $
548
6,580 $
Fill in the box for #1
Fees Earned
Wages
Expense
Rent Expense
Utilities
Expense
Supplies
Expense
Depreciation
Expense
Taxes
Expense
Net Income or Net Loss
2013
47,000
2014
Difference
42,000
5,000
21,000
19,000
2,000
6,000
6,000
–
4,000
3,000
1,000
700
1,000
(300)
5,200
5,920
(720)
300
9,800
500
6,580
(200)
(3,220)
ly and is not to be submitted!!
uide does not provide the question!
dividually assigned Excel Financial Case.
tand how to complete the computational areas
s
$
AUG
SEP
OCT
NOV
DEC
3,500.00
3,500.00
3,500.00
3,500.00
3,500.00
1,583.33
1,583.33
1,583.33
1,583.33
1,583.33
500.00
500.00
500.00
500.00
500.00
250.00
250.00
250.00
250.00
250.00
83.33
83.33
83.33
83.33
83.33
493.33
493.33
493.33
493.33
493.33
41.67
41.67
41.67
41.67
41.67
548 $
548 $
548 $
548 $
548
This study guide is for your use only and is not
Make sure you answer question 1-4 on your individually assigned Excel Financial Case; this study guide does not provide th
You assigned questions 1-4 will be given on your individually as
The data below is just available to help you understand how to
of this assignment.
Cash Analysis
Below is the 2014 profit or (loss) end of the year Information after the changes from #1 of
worksheet “12 Month Income Statement” it will automatically populate once you have completed the
“12 Month Income Statement” worksheet information:
JAN
$
FEB
548 $
548 $
MAR
548 $
APR
MAY
JUN
JUL
548 $
548 $
548 $
2013
2014 Difference
Fill in the box for #1
Cash
Accounts Receivable
Computer Equipment
16,000
6,000
13,000
–
2,400
(13,600)
19,000
13,000
13,000
–
12,000
12,000
(11,120)
(5,920)
3,600
3,600
2,000
2,500
Total Assets
31,800
41,380
*accumlated depreciation is a contra asset
9,580
Vehicle
Accumulated Depreciation
Inventory
Supplies Inventory
(5,200)
–
500
548 $
AUG
548 $
SEP
548
nly and is not to be submitted!!
guide does not provide the question!
ndividually assigned Excel Financial Case.
stand how to complete the computational areas
OCT
$
548 $
NOV
548 $
DEC
548
Excel Financial Case Study Guide
For your use only – Do not submit for a grade
This study guide is for your use only and is no
***IMPORTANT**
Complete the Income Statement first, complete the Statement of Owners’ Equity second, complete the Balance S
Your Individually assigned Excel Financial case is located on the “Assignments” link/tab on the Falcon online Portal – see pic
The 2013 Financials for Lovelady Travel Services worksheet tab labeled “2013 Financials” for informationally purposes only
information on the “2014 Financials” Statement of Cash Flows.
1) Complete 2014 Financials for Lovelady Travel Services-tab labled “2014 Financials” – An Answer key is available on the “201
Complete the Income Statement first , complete the Statement of Owners’ Equity second , complete the Balance Sh
For detailed instructions view your individually assigned Excel Financial Case – “Information” worksheet. See picture above
2) Review the “2014 Income Statement” worksheet to understand how to compute the “2014 Income Statement” workshee
case. On your individually assigned Excel Financial Case’s “12 Month Income Statement” worksheet you will complete th
questions. This study guide only gives you an example of how to complete the computational part not the questions (Scro
example of how to complete the computations).
3) Review the “Cash Analysis” worksheet to understand how to compute the “Cash Analysis” worksheet on your individually
case. On your individually assigned Excel Financial Case’s “Cash Analysis” worksheet you will complete the computations
questions. This study guide only gives you an example of how to complete the computational part not the questions (Scro
example of how to complete the computations).
Note: To see more worksheets use the bottom right hand corner arrow button to scroll to the right
Be Careful not to use Study Guide information on your graded Excel Financia
cial Case Study Guide
– Do not submit for a grade
e only and is not to be submitted!!
MPORTANT**
second, complete the Balance Sheet third, and complete the Statement of Cash Flows last.
he Falcon online Portal – see picture below how to access your assignment.
r informationally purposes only! You will use some of that
swer key is available on the “2014 Financial Answer Key” worksheet
second , complete the Balance Sheet third , and complete the Statement of Cash Flows last .
n” worksheet. See picture above.
4 Income Statement” worksheet on your individually assigned Excel Financial
worksheet you will complete the computations as well as answer the three
nal part not the questions (Scroll to the right of the workbook to see an
worksheet on your individually assigned Excel Financial
will complete the computations as well as answer the four
nal part not the questions (Scroll to the right of the workbook to see an
raded Excel Financial Case assignment!!!
This study guide is for your use only and is not to be
The account balances of Lovelady Travel Services at December 31, 2013 are listed below:
Accounts Payable
$12,000
Accounts Receivable
Cash
Computer Equip
Fees Earned Revenue
Rent Expense
6,000
16,000
13,000
47,000
6,000
Lovelady, Capital
(beginning balance)
Supplies Inventory
Taxes Expense
Utilities Expense
Wages Expense
Supplies Expense
Depreciation Expense
$10,000
2,000
300
4,000
21,000
700
5,200
Lovelady Travel Services
Balance Sheet
December 31, 2013
Assets
Cash
Accounts Receivable
Computer Equipment
Accumulated Depreciation
Supplies
Total Assets
$
$
16,000
6,000
13,000
(5,200)
2,000
31,800
Liabilities
Accounts Payable
Total Liabilities
Owner’s Equity
Lovelady,Capital
Total Owner’s Equity
Total Liabilities and
Owner’s Equity
$
$
12,000
12,000
$
19,800
19,800
$
31,800
$
47,000
Lovelady Travel Services
Income Statement
For the Year Ended December 31, 2013
Fees Earned Revenue
Operating Expenses:
Wages
Expense
Rent Expense
Utilities
Expense
$
21,000
6,000
4,000
Supplies
Expense
Depreciation
Expense
Taxes
Expense
Total Operating Expenses
Net Income
700
5,200
300
$
$
37,200
9,800
$
$
10,000
9,800
19,800
$
9,800
$
5,200
(2,000)
3,500
16,500
Lovelady Travel Services
Statement of Owner’s Equity
For the Year Ended December 31, 2013
Lovelady, Capital 1/1/13
Net Incom …
Purchase answer to see full
attachment
