I'm trying to figure out how to formulate the balance sheet, income statement and statement of cash flows.

I have attached my excel document for what we figured out on our own. I am particularly struggling with assigning inventory over the 5 years. Please let me know if you have any helpful suggestions!

Santa Fe Trading Company, Santa Fe, New Mexico th (Adapted from Problem 1, Chapter 11 in Bank Management, 6 ed. By T.W. Koch and S. Scott Macdonald) Summer is approaching and Steven and Sue Mahon have finally decided that their idea of a successful furniture, art, and jewelry trading company has come of age. They know that summer is a popular tourist time in New Mexico and could be the best time to start this new business. The Mahons have had a long time interest in Southwestern art and furniture. Steven graduated from college with an economics degree about 15 years ago and received his MBA in finance a few years later. He has been working in Dallas, Texas, as the controller of a major wholesale distributor company for many years. His wife, Sue, who will be a full time partner in the business, spent the first ten years of her career in retail sales. Over the last several years she has assumed more administrative duties for the group she works with. Steven and Sue know they bring the expertise and skill to run a successful business, but to ensure success they have been researching the market for over five years. They also know that they must be very careful and thoroughly research the business and industry they are pursuing. They have traveled extensively to New Mexico and have spent a good deal of time getting to know the local artists (primarily ski bums). They have found that there is a great demand for Southwestern furniture in Texas and the Southwest, and prices are high. Through their contacts with the local craftspeople they have found that many of them would like a reliable source to display and sell their goods. They have been able to make tentative arrangements with a large and dependable group to supply the furniture and art pieces they will need to run the business. The Mahons have decided to open a shop called Santa Fe Trading Company in Santa Fe, New Mexico, and act as both a retailer and supplier to furniture and art outlets in Texas. Steven's extensive contacts with businesses in Dallas and Houston have given him the orders needed to make the business a success as soon as they begin shipping the goods. Sue has already begun marketing the Southwestern products. Santa Fe Trading Company's arrangements with the local crafts people will allow very aggressive pricing of the goods to retail establishments in Texas. This aggressive pricing have been well received and tentative orders are already in place. Steven has found an ideal location in Santa Fe that is currently available. The owner was asking $475,000 for the space, but company just signed a contract, contingent on financing, for $400,000. Steven and Sue have gotten bids on remodeling and should be able to renovate the space for about $90,000. Although they will purchase the building, the land is leased on a transferable lease with 65 years remaining. The Mahons have decided to invest $350,000, which represents most of their savings, into the venture. Sue's sister is also interested in the possibilities that the company exhibits and is lending Santa Fe $150,000. Repayment on the note to Sue's sister is not expected to begin for five years. They have estimated that they will need $200,000 in inventory to start the business and they will buy the inventory in cash to build goodwill with the local craftspeople. They also estimate that they will need to maintain $30,000 in cash to conduct day-to-day operations and for bill payments. Wanting to use a local bank, Steven has approached Cary Marmer, the senior loan officer at Santa Fe National Bank in Santa Fe, New Mexico, for financing. Steven's background in finance has allowed him to put together the following assumptions for their preliminary business plan. Steven believes that all renovations to the building and inventory can be in place by the end of June 2013. 1. Sales are expected to be a bit lower the first year since only six months will be included in the first fiscal year. Sales are expected to grow significantly in the first full year, 2014. Sales are expected to be: 350,000; 800,000; 900,000; 1,100,000; in the first four years and level off after 2016 Page 1 of 2 2. 3. 4. 5. 6. 7. 8. Based on tentative agreement and orders, it is expected that cost of goods sold will average about 60% of sales General and administrative expenses are expected to be $90,000 in 2013, $130,000 in 2014, and level off at $135,000 from 2015 on. The land lease expenses and interest expenses are included in operational expenses. Selling expenses are expected to be about 12 percent of sales and Sue is expecting to undertake extensive marketing and promotion efforts throughout Texas after the business is opened. It is expected that these additional promotional expenses will be about $50,000 in 2013 only. The company will use ten-year straight-line depreciation of the building and improvements. Santa Fe's effective tax rate is 34 percent. Since they expect a good deal of business to be paid by credit card and to ship goods to Texas on credit, they expect to carry about 50 days of accounts receivables. They also expect that, based on the type of business they are entering, they will turn their inventory over about three times a year. Based on the negotiations they have had with their craftspeople, suppliers, and other wholesale distributors, they estimate that they can count on about 30 days of accounts payable to help finance the business. In preparing to go to the bank for the necessary loan, the Mahons want to prepare projected financial statements showing that Santa Fe Trading Company can make a profit and pay back the loan. They also want to know more precisely how much they will need to borrow from the bank to open the doors for business. The Mahons plan to prepare five years of balance sheet, income statement, and cash budget data for the bank. They must also develop an opening balance sheet as of the day they plan to open the doors, June 30, 2013. These pro forma financial statements will aid them, and the bank officer, in answering many questions including: 1. 2. 3. 4. 5. 6. How much financing will be needed to open the doors of the business in July 2013? Five years of pro forma balance sheet and income statement data must be prepared to determine if additional financing is needed, and if so, how much. Steven's finance background tells him that the estimated financing needed each year will be an accounting plug figure to ensure that the balance sheet balances. If projected assets exceed liabilities and equity, the difference will be the bank's borrowing needs. If liabilities and equity exceed projected assets purchases, these funds will be used to pay off debt or increase cash or marketable securities. Because this is a start-up business it is even more important to identity the following: a. what the loan proceeds will be used for b. what the primary source of repayment and what is the secondary source of repayment c. when the total loan proceeds will be repaid (using the pro forma projections, the primary source of repayment and when the loan will be repaid can be determined) A cash budget or cash-based income statement needs to be prepared because Steven knows the only thing that matters to the bank is cash. Finally, Steven needs to prepare a collateral schedule. He knows that the banker does not want the collateral, but will need all he can get if the business is not as successful as expected. Be sure to explain the specifics of the questions below as they relate to this case. a. What types of loan covenants would you require? b. Identify the bank's largest risks in making this loan. c. How would you structure the loan to protect the bank (what term, line or loan, etc.)? d. What is your recommendations concerning the loan request? Page 2 of 2 Pro Forma Balance Sheet Santa Fe Trading Company Day of Open 2013 2014 2015 2016 2017 30,000 200,000 0 30,000 200,000 95,628 109,589 123288 150685 150685 490,000 490,000 441,000 392,000 343,000 294,000 Liabilities Accounts Payable Notes Payable 0 150,000 150,000 150,000 150,000 150,000 150,000 Equity Paid in Capital Retained Earnings 350,000 0 350,000 -91,000 350,000 45,000 350,000 68,000 350,000 124,000 350,000 92,260 Assets Current Assets Cash Inventory Accounts Receivable Fixed Assets Building and Facilities (Net of Dep.) Year of Operation Revenue Gross Sales Cost of Goods Sold Cost of Goods Sold Gross Profit (Loss) Operating Expenses Selling Expenses General and Admin. Promotional Expenses Depreciation Pro Forma Income Statement Santa Fe Trading Company For 2013-2017 2013 2014 2015 2016 2017 350,000 800,000 900,000 1,100,000 1,100,000 210,000 480,000 540,000 660,000 660,000 140,000 320,000 360,000 440,000 440,000 42,000 90,000 50,000 49,000 96,000 130,000 0 49,000 108,000 135,000 0 49,000 132,000 135,000 0 49,000 132,000 135,000 0 49,000 Future Tax Deductions from 2013 Net Operating Loss 2013 NOP/Tax Credit Amount: 91000 Total Operating Exp. 231,000 275,000 292,000 316,000 316,000 Year Operating Income Taxes on Income* Net Income After Taxes (91,000) 0 (91,000) 45,000 0 45,000 68,000 0 68,000 124,000 0 124,000 124,000 31,740 92,260 Normal Tax Amt DeductedTaxes Payable Tax Credit Remaining 2014 15,300 -15,300 0 -15,300 2015 23,120 -23,120 0 -38,420 2016 42,160 -42,160 0 -80,580 2017 42,160 -10,420 31,740 0 loss ($91,000). This credit will cover tax expenses in 2014, 2015, 2016, and partially in 2017. g Statement of Cash Flows Santa Fe Trading Company For 2013-2017 Cash Flow from Operatio Net Income Increases in Cash Depreciation Decrease in Accounts Rec Increase in Accounts Pay Increase in Taxes Pay Decreases in Cash Increase in Inventory Increase in A/R Net Cash from Operations Cash Flow from Investing Cash Flow from Investing Notes Payable Total Cash Flows 2,013 (91,000) 2,014 45,000 2,015 68,000 2,016 124,000 2,017 92,260 49,000 0 49,000 49,000 49,000 49,000 95,628 13,961 13,699 27,397 0 July-Dec 31st = 183 days 350,000 revenue / 183 days = $1913 of receivables per day $1913 * 50 = $95,628 is 50 days of receivables