TWUFinancial Statement Project
Instructions
5093-52 Fall 2023
(60 points)
Team Names:
1. Enter Name One Here
2. Enter Name Two Here
You are the Controller for the Minnesota Vikings Sports Merchandise Company (MVSMC). You are honored to be the Controller of MVSMC, because the Minnesota Vikings are your favorite football
team. The CFO has informed you that MVSMC plans to obtain a bank loan to purchase a 5,000 sq ft warehouse in downtown Minnesota and asked for your assistance in analyzing the financial
performance of MVSMC as of December 31, 2022. As the Controller, you are presented with the current year end (12/31/22) Trial Balance (see Trial Balance tab) and the Prior Year Trial Balance (see
Prior Year Balances tab). You are responsible to prepare financial statements and calculate key ratios (see Ratio Analysis tab) for MVSMC as of December 31, 2022 (and December 31, 2021 for the
Balance Sheet Only). The Financial Statements will include; a) Balance Sheet, b) Income Statement and c) Statement of Cash flows. Once you prepare the financial statements and calculate the ratios,
you will assist the CFO with obtaining a bank loan by preparing a “write up”, which includes your evaluation of the financial performance of MVSMC as of December 31, 2022. Your write up/evaluation
should be completed by preparing a Word document of at least two pages or a PowerPoint presentation of at least five slides. The write up should utilize and reference information from the financial
statements and ratios.
REQUIREMENTS:
Utilizing the Trial Balance and Prior Year Balances tabs, prepare:
1. Prepare a Balance Sheet on the Balance Sheet tab. The balance sheets should included two years; as of December 31, 2022 and 2021.
2. Prepare an Income Statement (multi-step format) on the Income Statement tab. The Income Statement should include the year ended December 31, 2022 (one period).
3. Prepare a Statement of Cash Flows (utilizing the Indirect Method) on the Statement of Cash Flow tab. The Statement of Cash Flows should include the year ended December 31, 2022 (one period).
Utilize the Prior Year Balances tab for changes in accounts.
4. Calculate the ratios on the Ratio Analysis tab. The calculation must be labelled properly; i.e. %, number of days, times, etc.
5. Based on the completed financial statements and ratios, prepare a presentation to the CFO utilizing Word or Power, with you analysis of MVSMC. Provided detailed analysis and support for
presentation.
The Financials statements must be prepared in the proper form. Students are required to utilize (embed) excel formulas for the financial statements and ratios, to received full credit.
Other Notes:
The 5,000 sq ft warehouse is expected to cost $1,000,000. With the expanded warehouse space, sales are expected to increase by 250% and will maintain the 2022 gross profit %, operating expenses
are expected to increase by 30% and the company’s tax rate will remain at 15.5%. The loan will have an interest rate of 7%, monthly principal and interest of $11,611, with a 10 year term.
All sales are credit sales (no cash sales)
All financial statements must be properly formatted in Excel.
As a reminder, on the Balance Sheet, Assets = Liabilities + Owners’ Equity
Assets and Expenses increase with a Debit and Liabilities, Revenue and Owners’ Equity increase with a Credit.
Remember that the Financial Statements are linked with specific accounts; i.e. Cash on the Balance Sheet Links to Cash on the Statement of Cash Flows. Please verify the linked balances between the
financial statements to verify the statements are in balance.
Enter names of team members in the green cells above.
The Financial Statement project is an individual assignment or an assignment for a team of two students. Students should not collaborate with students outside their team of two.
Project must be emailed to Professor Klein at kklein2@twu.edu by 5P CST October 12, 2023. The email must include this Excel File with the Financial Statements, Ratios and a Word or PowerPoint File
with you analysis of the financial performance of MVSMC for the year ended December 31, 2022. The files must be “readable files” attached to the email. I will not accept links to files. I strongly
recommend that you submit the files early, in case I have any issues opening the files. Per the syllabus, I will not accept the project after the deadline (October 12, 2023 at 5P).
MVSMC
Trail Balance
As of December 31, 2022
Accounts
Other current assets
Interest expense, net
Income tax expense
Depreciation expense
Other long-term assets
Cash and cash equivalents
Accounts receivable
Research and development expenses
General and administrative expense
Property, plant and equipment, net
Marketing expense
Inventory
Operating expense
Less cost of goods sold
Common stock
Other current liabilities
Retained earnings
Accrued expenses
Accounts payable
Long-term debt
Sales revenue, net
Debit
Credit
1,000
4,500
7,790
8,000
10,000
13,500
18,500
22,000
42,840
48,000
54,000
78,420
105,250
278,200
1,000
3,500
7,000
8,500
12,000
95,000
565,000
692,000
692,000
MVSMC
Balance Sheets
December 31,
2022
December 31,
2021
$
$
Assets
Total assets
–
–
Liabilities
Total liabilities
–
–
Stockholders’ Equity
Total stockholders’ equity
Total liabilities and stockholders’ equity
$
–
$
–
MVSMC
Income Statement
For the Year Ended December 31, 2022
Revenues
Operating expenses
Total operating expenses
Income from operations
Other non-operating income and expense
Income before income taxes
Net income
MVSMC
Statement of Cash Flows (Indirect Method)
For the Year Ended December 31, 2022
Net income
Adjustments to reconcile net income to net cash
form operating activities:
Changes in assets and liabilities:
Net cash provided (used) by operating activities
Investing activities:
–
Net cash used for investing activities
Financing activities:
–
Net cash provided (used) for financing activities
Net change in cash and cash equivalents
Cash and cash equivalents – beginning of period
Cash and cash equivalents – end of period
–
$
MVSMC
Ratio Analysis
For the Year Ended December 31, 2022
Return to Shareholders
Net Income
Average Stockholders Equity
$
$
–
#DIV/0!
Average Collections Period
365 days
Receivables Turnover
Return on Assets
Net Income
Average Total Assets
Inventory Turnover
Cost of Goods Sold
Average Inventory
Gross Profit Ratio
Net Sales – COGS
Net Sales
Average Days in Inventory
365 days
Inventory Turnover
Profit Margin
Net Income
Net Sales
Current Ratio
Current Assets
Current Liabilities
Asset Turnover
Net Sales
Average Total Assets
Times Interest Earned Ratio
Net income + Interest Expense + Income Tax Expense
Interest expense
Receivables Turnover
Net Credit Sales
Average net accounts receivable
Debt to Equity Ratio
Total interest bearing debt
Stockholders Equity
MVSMC
Prior Year Trial Balance
As of December 31, 2021
Accounts
Accounts receivable
Other long-term assets
Other current assets
Inventory
Cash and cash equivalents
Property, plant and equipment, net
Accrued expenses
Common stock
Accounts payable
Other current liabilities
Retained earnings
Long-term debt
Debit
1,000
5,000
6,000
10,000
42,500
51,000
Credit
1,000
2,500
5,000
7,000
100,000
115,500
115,500
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
