The problem required you to generate the data and create a 1.) Pro-Forma Balance Sheet 2.) Pro-Forma Income Statement 3.) Pro-Forma Cash Budget 4.) Pro-Forma Collateral Schedule

Also could you show the formulas you used on excel to create the Pro-Formas.

Problems 1. Southwest Trading Company, Taos, New Mexico. Summer is approaching, and Ste- ven and Sue Mahan have finally decided that their idea of a successful Southwestern furniture, art, and jewelry trading company has come of age. They know that sumis mer is a popular tourist time in New Mexico and could be the best time to start this new business. The Mahans 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 Dali las, Texas, as the controller of a major wholesale distributor company for many years. His wife, Sue, who will be a full partner in the business, spent the first 10 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 busi- ness, 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 elsewhere in 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 for displaying and selling 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 Mahans have decided to open a shop called Southwest Trading Company in Taos, New Mexico, and act as both a retailer and a supplier to furniture and art out- lets 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 Southwest Trading Com- pany's products. Southwest 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 has been well received, and tentative orders are already in place. Steven has found an ideal location in Taos that is currently available. The owner is asking $275,000 for the space, but Southwest Trading has a contract, contingent on financing, for $250,000. Steven and Sue have gotten bids on remodeling and should be able to renovate the space for about $45,000. Although they will purchase the building, the land is leased on a transferable lease with 65 years remaining. The Mahan's have decided to invest $235,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 Southwest Trading $90,000. Repayment on the note to Sue's VER sister is not expected to begin for five years. They have estimated that they will build goodwill with the local craftspeople. They also estimate that they will be $130,000 in inventory to start the business, and they will buy the inventory incasso $20,000 in cash to conduct day-to-day operations and bill payment. Wanting to use a local bank, Steven has approached Cary Farmer, the senior lo officer at Santa Fe National Bank in Santa Fe, New Mexico, for financing, Stevens background in finance has allowed him to put together the following assumption for their preliminary business plan Steven believes that all renovations to the built ing and inventory can be in place by the end of June 2014 1. Sales are expected to be a bit lower the first year (July through December, 2016 because only six months will be included in the first fiscal year. Sales are expected to grow significantly in the first full year, 2015, with growth leveling off in the third and fourth years. Sales are expected to be $275,000, $675.000, $300.000, and $900,000 in 2014, 2015, 2016, and 2017, respectively. Sales are expected to level off after 2017 2. Based on tentative agreements and orders, it is expected that COGS will about 63 percent of sales. 3. General and administrative expenses are expected to be $70,000 for the months of operations in 2014. increase to $100,000 in 2015, and level of $120,000 from 2016 forward. The land lease expenses and interest expenses included in operating expenses 4. Selling expenses are expected to be about 12 percent of sales and Sue is expecting to undertake extensive marketing and promotion efforts throughout Tetes after the business is opened. It is expected that these additional promotional expenses will be about $30,000 in 2014 only. 5. The company will use 10-year straight-line depreciation of the building and improvements. 6. Southwest's effective tax rate is expected to be 34 percent. 7. Because they expect a good deal of sales to be paid by credit card and to ship goods to customers in Texas on credit, they expect to carry about 48 days af accounts receivable. They also expect that, because of the type of business they are entering, they will turn their inventory over about three times a year 8. On the basis of the negotiations they have had with their craftspeople, suppliers and other wholesale distributors, they estimate that they can count on about 25 days of accounts payable to help finance the business. In preparing to go to the bank for the necessary loan, the Mahans want to prepare projected financial statements showing that Southwest Trading Company can make profit and pay back the loan. They also want to know more precisely how much the will need to borrow from the bank to open the doors for business. The Mabans per 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 open the doors, June 30, 2014. These pro forma financial statements will aid the and the bank officer, in answering many questions, including the following 1. How much financing will be needed to open the doors of the business in der 2014 2. Five years of pro forma balance sheet and income statement data must be pube pared to determine if additional financing is needed, and if so, how much ven'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 pro- jected assets exceed liabilities and equity, the difference will be the bank's borrow- ing needs. If liabilities and equity exceed projected asset purchases, these funds will be used to pay off debt or increase cash or marketable securities. 3. Because this is a start-up business, it is even more important to identify what the loan proceeds will be used for, what the primary source of repayment is, and 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. 4. A cash budget or cash-based income statement needs to be prepared because Ste- ven knows the only thing that matters to the bank is cash. 5. 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. 6. Prepare a list of questions to which you would need the answers. Be sure to explain the specifics of the questions 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? d. What is your recommendation concerning the loan request? Conduct the analysis suggested by the above questions. What is your recommen- dation concerning the loan request? tamant and balance Assumptions: Cost of Goods Sold Days A/R Inventory turnover Days Accts Pay 63.00% of sales 48.00 Days 3.00 times 28.00 Days 2014 $275,000 $70,000 $30,000 2015 $675,000 $100,000 $0 2016 $800 000 $120.000 $0 2017+ $900.000 $120.000 Sale Projections G&A Expenses Other Expenses Selling expenses Depreciation Tax Rate $0 12.00% of sales 10 yr straight line 34.00% Problems 1. Southwest Trading Company, Taos, New Mexico. Summer is approaching, and Ste- ven and Sue Mahan have finally decided that their idea of a successful Southwestern furniture, art, and jewelry trading company has come of age. They know that sumis mer is a popular tourist time in New Mexico and could be the best time to start this new business. The Mahans 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 Dali las, Texas, as the controller of a major wholesale distributor company for many years. His wife, Sue, who will be a full partner in the business, spent the first 10 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 busi- ness, 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 elsewhere in 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 for displaying and selling 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 Mahans have decided to open a shop called Southwest Trading Company in Taos, New Mexico, and act as both a retailer and a supplier to furniture and art out- lets 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 Southwest Trading Com- pany's products. Southwest 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 has been well received, and tentative orders are already in place. Steven has found an ideal location in Taos that is currently available. The owner is asking $275,000 for the space, but Southwest Trading has a contract, contingent on financing, for $250,000. Steven and Sue have gotten bids on remodeling and should be able to renovate the space for about $45,000. Although they will purchase the building, the land is leased on a transferable lease with 65 years remaining. The Mahan's have decided to invest $235,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 Southwest Trading $90,000. Repayment on the note to Sue's VER sister is not expected to begin for five years. They have estimated that they will build goodwill with the local craftspeople. They also estimate that they will be $130,000 in inventory to start the business, and they will buy the inventory incasso $20,000 in cash to conduct day-to-day operations and bill payment. Wanting to use a local bank, Steven has approached Cary Farmer, the senior lo officer at Santa Fe National Bank in Santa Fe, New Mexico, for financing, Stevens background in finance has allowed him to put together the following assumption for their preliminary business plan Steven believes that all renovations to the built ing and inventory can be in place by the end of June 2014 1. Sales are expected to be a bit lower the first year (July through December, 2016 because only six months will be included in the first fiscal year. Sales are expected to grow significantly in the first full year, 2015, with growth leveling off in the third and fourth years. Sales are expected to be $275,000, $675.000, $300.000, and $900,000 in 2014, 2015, 2016, and 2017, respectively. Sales are expected to level off after 2017 2. Based on tentative agreements and orders, it is expected that COGS will about 63 percent of sales. 3. General and administrative expenses are expected to be $70,000 for the months of operations in 2014. increase to $100,000 in 2015, and level of $120,000 from 2016 forward. The land lease expenses and interest expenses included in operating expenses 4. Selling expenses are expected to be about 12 percent of sales and Sue is expecting to undertake extensive marketing and promotion efforts throughout Tetes after the business is opened. It is expected that these additional promotional expenses will be about $30,000 in 2014 only. 5. The company will use 10-year straight-line depreciation of the building and improvements. 6. Southwest's effective tax rate is expected to be 34 percent. 7. Because they expect a good deal of sales to be paid by credit card and to ship goods to customers in Texas on credit, they expect to carry about 48 days af accounts receivable. They also expect that, because of the type of business they are entering, they will turn their inventory over about three times a year 8. On the basis of the negotiations they have had with their craftspeople, suppliers and other wholesale distributors, they estimate that they can count on about 25 days of accounts payable to help finance the business. In preparing to go to the bank for the necessary loan, the Mahans want to prepare projected financial statements showing that Southwest Trading Company can make profit and pay back the loan. They also want to know more precisely how much the will need to borrow from the bank to open the doors for business. The Mabans per 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 open the doors, June 30, 2014. These pro forma financial statements will aid the and the bank officer, in answering many questions, including the following 1. How much financing will be needed to open the doors of the business in der 2014 2. Five years of pro forma balance sheet and income statement data must be pube pared to determine if additional financing is needed, and if so, how much ven'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 pro- jected assets exceed liabilities and equity, the difference will be the bank's borrow- ing needs. If liabilities and equity exceed projected asset purchases, these funds will be used to pay off debt or increase cash or marketable securities. 3. Because this is a start-up business, it is even more important to identify what the loan proceeds will be used for, what the primary source of repayment is, and 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. 4. A cash budget or cash-based income statement needs to be prepared because Ste- ven knows the only thing that matters to the bank is cash. 5. 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. 6. Prepare a list of questions to which you would need the answers. Be sure to explain the specifics of the questions 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? d. What is your recommendation concerning the loan request? Conduct the analysis suggested by the above questions. What is your recommen- dation concerning the loan request? tamant and balance Assumptions: Cost of Goods Sold Days A/R Inventory turnover Days Accts Pay 63.00% of sales 48.00 Days 3.00 times 28.00 Days 2014 $275,000 $70,000 $30,000 2015 $675,000 $100,000 $0 2016 $800 000 $120.000 $0 2017+ $900.000 $120.000 Sale Projections G&A Expenses Other Expenses Selling expenses Depreciation Tax Rate $0 12.00% of sales 10 yr straight line 34.00%