BUS180spreadsheetW13Project05JJewelry

.xlsx
School
University of Washington **We aren't endorsed by this school
Course
BUS 180
Subject
Accounting
Date
Oct 20, 2023
Pages
1
Uploaded by AgentBear2982 on coursehero.com
W13 Financial Statement Project 5 - Jewelry 23 Projected Financial Statements for Julie's Jewelry Business For the 3 months ended March 31, 2022 Business Activity: $2,250 will be contributed by the owner in December. Julie expects to purchase $2000 equipment with a bank loan in January. Assumptions Sun design necklace sales price $ 150 Days Receivable from Total Sales 5 Moon design necklace sales price $ 175 Inventory (% of next month COGS) 25% Cost of Sun design necklace $ 75 A/P (% of Advertising plus General Expenses) 15% Cost of Moon design necklace $ 90 Equipment Life in months 60 Annual Growth Rate 50% 0.04166667 Advertising as a percent of total sales 5% General expense $ 1,500 Jan Feb Mar April Units Sun necklace sold 23 24 25 26 Units Moon necklace sold 18 19 20 20 Jan Feb Mar April P&L Statement Sun Necklace Sales $ 3,450 $ 3,594 $ 3,743 $ 3,899 Moon Necklace Sales 3,150 3,281 3,418 3,560 Total Sales $ 6,600 $ 6,875 $ 7,161 $ 7,460 Cost of Sun Necklaces Sold 1,725 1,797 1,872 1,950 Cost of Moon Necklaces Sold 1,620 1,688 1,758 1,831 Total Cost of Goods Sold $ 3,345 $ 3,484 $ 3,630 $ 3,781 Gross profit $ 3,255 $ 3,391 $ 3,532 $ 3,679 Expenses Advertising $ 330 $ 344 $ 358 $ 373 General Expenses 1,500 1,500 1,500 1,500 Depreciation 33 33 33 33 Total Expenses $ 1,863 $ 1,877 $ 1,891 $ 1,906 Net Income $ 1,392 $ 1,514 $ 1,641 $ 1,773 Check: April N/I = $1,773 Statement of Cash Flows Jan Feb Mar Operating Activities Net Income $ 1,392 $ 1,514 $ 1,641 Changes in Cur. Assets and Liabilities Change In Accounts Receivable (1,100) (46) (48) Change In Inventory on Hand (871) (36) (38) Change In Accounts Payable 275 2 2 Non-cash expenses Depreciation 33 33 33 Cash Flow from Operating Activities $ (271) $ 1,467 $ 1,590 Investing Activities Change In Equipment $ (2,000) $ - $ - Cash Flow from Investing Activities $ (2,000) $ - $ - Financing Activities Change In Notes Payable $ 275 $ 2 $ 2 Change In Paid-in-Capital - - - Change In Less: Owner's Withdrawal - - - Cash Flow from Financing Activities $ 1,800 $ 2 $ 2 Net increase/(decrease) in cash $ (471) $ 1,469 $ 1,592 Cash--beginning of month 2,250 1,178 3,245 Cash--end of month $ 1,779 $ 2,647 $ 4,837 Check: March Cash--end of month = $5,036 Balance Sheet Dec Jan Feb Mar Assets Cash $ 2,250 $ 1,178 $ 3,245 $ 4,835 Accounts Receivable - 1,100 1,146 1,194 Inventory on Hand - 871 907 945 Equipment - 2,000 2,000 2,000 2,000 Less Accumulated Depreciation (33) (66) (99) Total Assets $ 2,250 $ 5,116 $ 7,232 $ 8,875 Check: March Assets = $9,074 Liabilities Accounts Payable $ - $ 275 $ 277 $ 279 Notes Payable - 1,800 1,800 1,800 Total Liabilities $ 2,075 $ 2,077 $ 2,079 Owner's Equity Paid-in-Capital $ 2,250 $ 2,250 $ 2,250 $ 2,250 Less: Owner's Withdrawal - - - - Retained Earnings 1,392 2,905 4,546 Total Owner's Equity $ 2,250 $ 3,642 $ 5,155 $ 6,796 Total Liabilities and Owner's Equity $ 5,717 $ 7,232 $ 8,875 After completing projected financial statements, answer the following questions: (round to whole numbers) KEY FINANCIAL RATIOS January February March Quick Ratio 8 16 22 Gross Profit Margin (as a %) 49% 49% 49% Inventory Turnover 3.8 3.8 3.8 Return on Assets (as a %) 27% 21% 18% Quiz Questions: 2. What is the cash balance at the end of February? _____ NOTE: Change the price back to $150. 3. How much Cash was generated by Financing Activities in January? _____ 4. What caused the increase in Retained Earnings for March? __ 5. What was the Quick Ratio for March? _____ 6. What was the average Inventory Turnover for the three months? _____ 7. What is Break-Even analysis: 1. If the price for the Sun Necklace increases to $175, what is the profit Net Income or (Loss) in February? ______
Page1of 1
Uploaded by AgentBear2982 on coursehero.com