5-1 Working Capital Management : Tesla
JMooreHelpNeeded
2-2FIN320JournalExcel_Tesla_JMoore.xlsxCash Flow
| TESLA MOTORS INC | ||||||||||||
| Cash Flow | ||||||||||||
| January, 2020 | February, 2020 | March, 2020 | April, 2020 | May, 2020 | June, 2020 | July, 2020 | August, 2020 | September, 2020 | October, 2020 | November, 2020 | December, 2020 | |
| Cash Balance at Beginning of Month | $ 22,320.00 | $ 4,205,563,548.85 | $ 8,418,116,382.02 | $ 12,637,692,505.54 | $ 16,864,303,624.88 | $ 21,097,961,465.04 | $ 25,338,677,770.55 | $ 29,586,464,305.52 | $ 33,841,332,853.67 | $ 38,103,295,218.35 | $ 42,372,363,222.58 | $ 46,648,548,709.11 |
| Cash Flow From Operating Activities | ||||||||||||
| Changes in accounts receivables | $ (362.50) | $ (362.50) | $ (362.50) | $ (362.50) | $ (362.50) | $ (362.50) | $ (362.50) | $ (362.50) | $ (362.50) | $ (362.50) | $ (362.50) | $ (362.50) |
| Changes in inventory | $ 929.17 | $ 929.17 | $ 929.17 | $ 929.17 | $ 929.17 | $ 929.17 | $ 929.17 | $ 929.17 | $ 929.17 | $ 929.17 | $ 929.17 | $ 929.17 |
| Changes in accounts payable | $ (643.58) | $ (643.58) | $ (643.58) | $ (643.58) | $ (643.58) | $ (643.58) | $ (643.58) | $ (643.58) | $ (643.58) | $ (643.58) | $ (643.58) | $ (643.58) |
| Changes in depreciation | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 |
| Net Profit | $ 4,206,891,281.87 | $ 4,213,902,886.20 | $ 4,220,926,176.54 | $ 4,227,961,172.37 | $ 4,235,007,893.18 | $ 4,242,066,358.53 | $ 4,249,136,587.99 | $ 4,256,218,601.17 | $ 4,263,312,417.70 | $ 4,270,418,057.26 | $ 4,277,535,539.55 | $ 4,284,664,884.32 |
| Total Changes in Cash from Operations | $ 4,206,897,645.51 | $ 4,213,909,249.84 | $ 4,220,932,540.18 | $ 4,227,967,536.00 | $ 4,235,014,256.82 | $ 4,242,072,722.17 | $ 4,249,142,951.63 | $ 4,256,224,964.81 | $ 4,263,318,781.34 | $ 4,270,424,420.90 | $ 4,277,541,903.19 | $ 4,284,671,247.95 |
| Cash Flow from Investing Activities | ||||||||||||
| Capital Expenditures - New Equipment | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) | $ (272,925.00) |
| Capital Expenditures - New Vehicles | $ (475.00) | $ (475.00) | $ (475.00) | $ (475.00) | $ (475.00) | $ (475.00) | $ (475.00) | $ (475.00) | $ (475.00) | $ (475.00) | $ (475.00) | $ (475.00) |
| Total Changes in Cash from Investing | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) | $ (273,400.00) |
| Cash Flow from Financing Activities | ||||||||||||
| Long Term Notes Payable Repayments | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) | $ (1,082,313.33) |
| Short Term Notes Payable Repayment | $ (703.33) | $ (703.33) | $ (703.33) | $ (703.33) | $ (703.33) | $ (703.33) | $ (703.33) | $ (703.33) | $ (703.33) | $ (703.33) | $ (703.33) | $ (703.33) |
| Total Changes in Cash from Financing | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) | $ (1,083,016.66) |
| Cash Balance at End of Month | $ 4,205,563,548.85 | $ 8,418,116,382.02 | $ 12,637,692,505.54 | $ 16,864,303,624.88 | $ 21,097,961,465.04 | $ 25,338,677,770.55 | $ 29,586,464,305.52 | $ 33,841,332,853.67 | $ 38,103,295,218.35 | $ 42,372,363,222.58 | $ 46,648,548,709.11 | $ 50,931,863,540.40 |
Source Data
| Company Name: | TESLA MOTORS INC | ||||||||||||
| Opening Day of Business (Year 1) | 5/5/20 Jason Ripley: Jason Ripley: Insert start date for business Format = year-month-day so 21-1-1 for Jan 1, 2021 | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| Closing Day of Business (Year 1) | 5/5/21 Jason Ripley: Jason Ripley: Insert year end date for business in format year-month-day, so 21-12-31 for Dec 31, 2021 |
||||||||||||
| Calculating Depreciationi Expense: | |||||||||||||
| Startup Financing: | Straight Line technique. Depreciation expense = (Original Cost - Salvage Value) / Number of years in life span | ||||||||||||
| Initial Capital invested | $ 240,000.00 | Item | Purchase Price | Salvage Value | Usefull Life | Depreciation Expense | |||||||
| Additional investment from sole-proprietorship Stakeholders | $ 32,123.00 | Office Acccessories, Electronics e.g., Monitors, Displays | $ 3,220,900.00 | $ 3,105,000.00 | 13.00 | $ 8,915.38 | |||||||
| Investment from other Managerial Staff/ Board Members/ Board Directors | $ 23,132.00 | Light/ Design Equipment | $ 2,370,000.00 | $ 1,950,432.00 | 9.00 | $ 46,618.67 | |||||||
| Capital from Financial Insitutions, Grants, Loans or Government | $ 12,421,000.00 Jason Ripley: Jason Ripley: This will sum the two cells below: Short Term + Long Term debt | Machines | $ 900,021.00 | $ 700,021.00 | 11.00 | $ 18,181.82 | |||||||
| Short Term Debt (line of credit, notes payable etc.) | $ 9,340.00 | Raw materials | $ 1,200,978.00 | $ 1,150,987.00 | 14.00 | $ 3,570.79 | |||||||
| Long Term Debt Long Term Loans etc. | $ 2,334,000.00 | ||||||||||||
| Startup Expenses: | |||||||||||||
| Cast for retrofit or renovating buildings or leased space? | $ 4,400.00 | Sales | |||||||||||
| Cost of purchased Equipemnt? | $ 12,332.00 | ||||||||||||
| Cost of purchased Furniture (tables/chairs), Public Address Systems. | $ 234,223.00 | ||||||||||||
| Cost of Purchased Office Stationeries, Computers | $ 33,240.00 | ||||||||||||
| Cost of Advanced Marketing & Advertising? | $ 8,900.00 | ||||||||||||
| Cost of Operational Licenses to operate, or Legal fees | $ 9,300.00 | ||||||||||||
| Cost of Purchased Inventory | $ 12,500.00 | Calculation of the Total sales: | |||||||||||
| Mischallenous Costs | $ 13,200.00 | ||||||||||||
| Cost of Purchasing/Hiring delivery vehicles | $ 25,992.00 | Variable 1 (covers, $sales/hr, REVPAR etc.) | Variable 2 (Labor output$/cover, # hours, # rooms etc.) | Total Sales | |||||||||
| Cash reserve for short term debt or emergency | $ 22,320.00 | 72300 | 90000 | $ 6,507,000,000.00 | |||||||||
| Outstanding Accounts Payable | $ 9,223.00 | ||||||||||||
| Profit & Loss: | Labour Costs | ||||||||||||
| Sales | $ 6,507,000,000.00 | What salaries will you pay your managers (and owners)? These are fixed costs and do not change based on revenue, you incur them | |||||||||||
| Average, or expected annual growth rate for Industry sales? | 2.00% | even when your business is closed. Your Labour costs for the rest of your employees however are considered variable, because you | |||||||||||
| Mean rate of returns & Discounts for Industry. | 1.52% | alter them depending on business volumes. | |||||||||||
| Mean price volatility of item | 2.21% | Annual Salary | |||||||||||
| Mean profitability of items | 3.25% | Manager 1 | $ 10,000.00 | ||||||||||
| Salaries for Executive Staff? | $ 40,000.00 | Manager 2 | $ 10,000.00 | ||||||||||
| Employee Compensation cost | 10.37% | Manager 3 | $ 10,000.00 | ||||||||||
| Monthly lease costs | $ 62,962.50 | Manager 4 | $ 10,000.00 | ||||||||||
| Office Expenses e.g., Paper rolls, pens/pencils, envelopes, printer paper etc. | 8.62% | ||||||||||||
| Ongoing marketing budget | 2.31% | Industry average variable labour cost | 10.37% | ||||||||||
| Repairs & Maintenance (R&M) costs | 2.41% | ||||||||||||
| Utilities expense | 3.24% | Rent/Lease Costs | |||||||||||
| Licence and other fees include things like a business licence, a liquor licence, consulting/accounting/legal fees. | 3.42% | If you are not purchasing your physical location, then you will likely be leasing a space. Lease costs are typically based on square | |||||||||||
| Interest expense or Mean borrowing costs. | 2.41% | footage (or square meters). The cost per square foot will be different depending on where you plan to open your business. Use | |||||||||||
| Mean Corporate tax rate for business operation. | 2.14% | sources such as www.realtor.ca, or search for commercial leasing companies in the area you are planning your virtual business. | |||||||||||
| Cash Flow: | |||||||||||||
| Accounts receivable i.e.credit one willing that affects your cash flow position. | $ 4,350.00 | Industry average per square foot cost | $ 230.00 | ||||||||||
| Total value of inventory expected at the end of the year. | $ 1,350.00 | Number of square feet required | 3285.00 | $ 755,550.00 | Annual Lease Costs | ||||||||
| Additional purchases e.g maintenance, purchases: | $ 250.00 | ||||||||||||
| Electronics e.g., Monitors, Displays | $ 3,220,900.00 | Calculating Interest Expense | |||||||||||
| Light/ Design Equipment | $ 20,200.00 | Interest Rate | Length of Loan (yrs) | Amount Borrowed | Interest Expense (per month) | ||||||||
| Machines | $ 34,000.00 | 1% | 1.00 | $ 12,421,000.00 | $ 10,350.83 | $ (12,421,000.00) | |||||||
| Raw Materials | $ 5,700.00 | Based on these numbers, your monthly payment will be: | $1,092,664.16 | ||||||||||
| Creditor's Payback @end of the year. | $ 1,500.00 | ||||||||||||
| Short term debts & Notes payable balance for end of the year. | $ 900.00 |
Income Statement
| TESLA MOTORS INC | ||||||||||||
| Income Statement for Year Ending: | May 5, 2021 | |||||||||||
| Income | May, 2020 | June, 2020 | July, 2020 | August, 2020 | September, 2020 | October, 2020 | November, 2020 | December, 2020 | January, 2021 | February, 2021 | March, 2021 | April, 2021 |
| Gross Sales | $ 6,507,000,000.00 | $ 6,517,845,000.00 | $ 6,528,708,075.00 | $ 6,539,589,255.13 | $ 6,550,488,570.55 | $ 6,561,406,051.50 | $ 6,572,341,728.25 | $ 6,583,295,631.13 | $ 6,594,267,790.52 | $ 6,605,258,236.84 | $ 6,616,267,000.56 | $ 6,627,294,112.23 |
| Less Returns & Discounts | $ 98,906,400.00 | $ 99,071,244.00 | $ 99,236,362.74 | $ 99,401,756.68 | $ 99,567,426.27 | $ 99,733,371.98 | $ 99,899,594.27 | $ 100,066,093.59 | $ 100,232,870.42 | $ 100,399,925.20 | $ 100,567,258.41 | $ 100,734,870.51 |
| Net Sales | $ 6,408,093,600.00 | $ 6,418,773,756.00 | $ 6,429,471,712.26 | $ 6,440,187,498.45 | $ 6,450,921,144.28 | $ 6,461,672,679.52 | $ 6,472,442,133.98 | $ 6,483,229,537.54 | $ 6,494,034,920.10 | $ 6,504,858,311.64 | $ 6,515,699,742.16 | $ 6,526,559,241.73 |
| Cost of Goods Sold: | ||||||||||||
| Sales within U.S | $ 143,804,700.00 | $ 144,044,374.50 | $ 144,284,448.46 | $ 144,524,922.54 | $ 144,765,797.41 | $ 145,007,073.74 | $ 145,248,752.19 | $ 145,490,833.45 | $ 145,733,318.17 | $ 145,976,207.03 | $ 146,219,500.71 | $ 146,463,199.88 |
| Sales outside U.S | $ 211,477,500.00 | $ 211,829,962.50 | $ 212,183,012.44 | $ 212,536,650.79 | $ 212,890,878.54 | $ 213,245,696.67 | $ 213,601,106.17 | $ 213,957,108.01 | $ 214,313,703.19 | $ 214,670,892.70 | $ 215,028,677.52 | $ 215,387,058.65 |
| Gross Profit | $ 6,052,811,400.00 | $ 6,062,899,419.00 | $ 6,073,004,251.37 | $ 6,083,125,925.12 | $ 6,093,264,468.33 | $ 6,103,419,909.11 | $ 6,113,592,275.62 | $ 6,123,781,596.08 | $ 6,133,987,898.74 | $ 6,144,211,211.91 | $ 6,154,451,563.93 | $ 6,164,708,983.20 |
| Expenses | ||||||||||||
| Fixed Labour Cost (manager salaries) | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 | $ 3,333.33 |
| Variable Labour Cost | $ 674,947,488.53 | $ 676,072,401.01 | $ 677,199,188.34 | $ 678,327,853.66 | $ 679,458,400.08 | $ 680,590,830.74 | $ 681,725,148.80 | $ 682,861,357.38 | $ 683,999,459.64 | $ 685,139,458.74 | $ 686,281,357.84 | $ 687,425,160.10 |
| Rent (Lease) | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 | $ 62,962.50 |
| Office Expenses | $ 560,881,362.96 | $ 561,816,165.24 | $ 562,752,525.51 | $ 563,690,446.39 | $ 564,629,930.46 | $ 565,570,980.35 | $ 566,513,598.65 | $ 567,457,787.98 | $ 568,403,550.96 | $ 569,350,890.21 | $ 570,299,808.36 | $ 571,250,308.04 |
| Marketing Expense | $ 150,175,816.20 | $ 150,426,109.22 | $ 150,676,819.41 | $ 150,927,947.44 | $ 151,179,494.02 | $ 151,431,459.84 | $ 151,683,845.61 | $ 151,936,652.02 | $ 152,189,879.77 | $ 152,443,529.57 | $ 152,697,602.12 | $ 152,952,098.12 |
| Repairs & Maintenance | $ 156,925,291.08 | $ 157,186,833.23 | $ 157,448,811.29 | $ 157,711,225.97 | $ 157,974,078.02 | $ 158,237,368.15 | $ 158,501,097.10 | $ 158,765,265.59 | $ 159,029,874.37 | $ 159,294,924.16 | $ 159,560,415.70 | $ 159,826,349.72 |
| Utilities | $ 210,921,090.16 | $ 211,272,625.31 | $ 211,624,746.36 | $ 211,977,454.27 | $ 212,330,750.02 | $ 212,684,634.61 | $ 213,039,109.00 | $ 213,394,174.18 | $ 213,749,831.14 | $ 214,106,080.86 | $ 214,462,924.32 | $ 214,820,362.53 |
| Licenses & Other Fees | $ 0.00 | $ 0.00 | $ 0.00 | $ 0.00 | $ 0.00 | $ 0.00 | $ 0.00 | $ 0.00 | $ 0.00 | $ 0.00 | $ 0.00 | $ 0.00 |
| Total Operating Expenses | $ 1,753,917,344.77 | $ 1,756,840,429.85 | $ 1,759,768,386.74 | $ 1,762,701,223.56 | $ 1,765,638,948.44 | $ 1,768,581,569.53 | $ 1,771,529,094.98 | $ 1,774,481,532.98 | $ 1,777,438,891.71 | $ 1,780,401,179.37 | $ 1,783,368,404.18 | $ 1,786,340,574.36 |
| EBITDA | $ 4,298,894,055.23 | $ 4,306,058,989.15 | $ 4,313,235,864.62 | $ 4,320,424,701.56 | $ 4,327,625,519.89 | $ 4,334,838,339.58 | $ 4,342,063,180.64 | $ 4,349,300,063.10 | $ 4,356,549,007.03 | $ 4,363,810,032.54 | $ 4,371,083,159.75 | $ 4,378,368,408.84 |
| Depreciation Expense | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 | $ 6,440.55 |
| Interest | $ 0.02 | $ 0.02 | $ 0.02 | $ 0.02 | $ 0.02 | $ 0.02 | $ 0.02 | $ 0.02 | $ 0.02 | $ 0.02 | $ 0.02 | $ 0.02 |
| Taxes | $ 91,996,332.78 | $ 92,149,662.37 | $ 92,303,247.50 | $ 92,457,088.61 | $ 92,611,186.13 | $ 92,765,540.47 | $ 92,920,152.07 | $ 93,075,021.35 | $ 93,230,148.75 | $ 93,385,534.70 | $ 93,541,179.62 | $ 93,697,083.95 |
| Net Profit | $ 4,206,891,281.87 | $ 4,213,902,886.20 | $ 4,220,926,176.54 | $ 4,227,961,172.37 | $ 4,235,007,893.18 | $ 4,242,066,358.53 | $ 4,249,136,587.99 | $ 4,256,218,601.17 | $ 4,263,312,417.70 | $ 4,270,418,057.26 | $ 4,277,535,539.55 | $ 4,284,664,884.32 |
Balance Sheet
| TESLA MOTORS INC | ||
| Balance Sheet As At: | ||
| May 5, 2020 | May 5, 2021 | |
| Current Assets | ||
| Cash and Cash Equivalents | $ 22,320.00 | $ 50,931,863,540.40 |
| Accounts Receivable | $ - 0 | $ 4,350.00 |
| Inventories | $ 12,500.00 | $ 1,350.00 |
| Total Current Assets | $ 34,820.00 | $ 50,931,869,240.40 |
| Fixed Assets | ||
| Electronics e.g., Monitors, Displays | $ 12,332.00 | $ 3,233,232.00 |
| Less Accumulated Depreciation | $ - 0 | $ 8,915.38 |
| Light/ Design Equipment | $ 234,223.00 | $ 254,423.00 |
| Less Accumulated Depreciation | $ - 0 | $ 46,618.67 |
| Machines | $ 33,240.00 | $ 67,240.00 |
| Less Accumulated Depreciation | $ - 0 | $ 18,181.82 |
| Raw Materials | $ 25,992.00 | $ 31,692.00 |
| Less Accumulated Depreciation | $ - 0 | $ 3,570.79 |
| Total Fixed Assets | $ 305,787.00 | $ 3,509,300.34 |
| Total Assets | $ 340,607.00 | $ 50,935,378,540.75 |
| Current Liabilities | ||
| Accounts Payable | $ 9,223.00 | $ 1,500.00 |
| Short Term Notes Payable | $ 9,340.00 | $ 900.00 |
| Total Current Liabilities | $ 18,563.00 | $ 2,400.00 |
| Long-term debt | ||
| Long Term Notes Payable | $ 2,334,000.00 | $ (10,653,759.95) |
| Total Long Term Debt | $ 2,334,000.00 | $ (10,653,759.95) |
| Total Liabilities | $ 2,352,563.00 | $ (10,651,359.95) |
| Owners Equity | ||
| Owners Equity/Partnership Equity/Retained Earnings | $ (2,011,956.00) | $ 50,946,029,900.70 |
Data Summary
| Metric | Value |
| Debt-Equity Ratio | 0.0002091152 |
| Debt Ratio | 0.0002091152 |
| Return on Assets | 0.0833540513 |
| Gross Profit Margin | 0.9727 |
| Cash Ratio | 21221609.808501 |
| Current Ratio | 21221612.183501 |
