**********PLEASE INCLUDE SCREENSHOTS OF EXCEL SPREADSHEETS AND FORMULAS********

Organizing Information Belleco Case Study Background Belle has been managing a small company making signs for the last five years. The company has grown significantly in this time. The company has purchased numerous assets, expanded the number of products, entered new markets, and has consistently expanded their customer base. When Belle started the company, she maintained a ledger with most of her transactions, and this was sufficient for her to get an impression of how the company was doing, or so she thought. She had several friends that also ran their own businesses, and all of them were basically using similar ledgers. More recently, however, Belle has become concerned about her finances. Whether it is due to the growth of the company or changing economic conditions she has begun to feel that she does not have a good understanding of how well her business is doing. She took a class to try and help her with her finances and she has come to the realization that she needs to apply some of the principles that she has learned in graduate school to get a better understanding of BelleCo's financial state. Her first undertaking has been to compile information from her ledgers. Information Belle's need to take stock of everything that her company had at its disposal. She was surprised at how much she had invested in the company given that she basically started out with nothing five years ago. She went through her old bank statements and found that her cash had increased over the years, which she thought was good news. She did the same for her inventories. Collecting the information for her raw materials, packaging, and finished goods that she had in her warehouse, she came up with the following summary table: Cash 1 $9,586.10 $28,000.00 2 $23,374.98 $56,000.00 3 $37,963.65 $84,000.00 4 $30,994.39 $112,000.00 5 $51,953.46 $96,000.00 Inventory From her tax accountant she learned that she depreciated her assets utilizing MACRS. Her building and land ($400,000) is depreciated on a 39-year MACRS nonresidential rea property schedule. Her manufacturing equipment ($260,000) has a depreciable life of 7 years and her delivery vehicle ($35,000) is depreciated based on a 3-year schedule. Two years ago she invested in an expansion to her equipment valued at $130,000, which is depreciable on a 7-year schedule. She has also invested in computers, automation equipment and office furniture over the years, according to her accountant the value for these items are: Value Other Items $2,000.00 $3,500.00 $4,700.00 $5,200.00 $5,000.00 These miscellaneous items will depreciate on a 3 years MACRS. Surprised at the values that she was finding, she decided to dig through her ledger and other notes. She found that her transactions had increased greatly, and all told, the amount of money that she owed for operational expenses and COGS that she had already ordered or received was a lot more than she had thought. Going through her notes she also started adding up the amounts that customers owed her for goods that she had already delivered. Accounts - 5 Receivable $2,400.00 $2,600.00 $3,800.00 $5,400.00 $9,800.00 Payable $10,000.00 $12,000.00 $15,000.00 $15,500.00 $17,000.00 sed She called her bank, and they were able to provide her with a detailed breakdown of her debt and line of credit. Belle had been very nervous to borrow money, but it had become necessary to purchase her present equipment. Five years ago, she had borrowed $500,000, at 4% interest repayable over 10 years. According to the bank, she also had a line of credit, which was opened three years ago, that presently stood at $50,000 and had balances in the preceding years of $50,000 and $70,000 (first year). The interest on the line of credit is 6.5%. There is no principle included in the required payment. Information on Operations BelleCo has shown strong growth in its sales. In the first year of operation, it sold $200,000. Since then, it has steadily grown by about 21% per year. Belle expected this Belle compiled some further information based on her operation. She found that the raw materials and other direct costs (COGS) that go into the production of her product had increased over the last five years. Similarly, her sales expenses and management and administration costs have increased. Belle summarizes these costs. 1 $93,000.00 2 $115,000.00 3 $146,300.00 4 $174,579.00 5 $195,340.00 COGS Sales Expenses $15,987.00 $23,456.00 $20,458.00 $31,345.00 $23,390.00 $39,876.00 $27,489.00 $48,767.00 $29,458.00 $63,254.00 G&A From the accountant, Belle was also told that her tax rate is 29%. Analysis Belle hopes that she will gain insight into her company by organizing and structuring all this information. 1) She would like to start by putting together a standard set of financial statements (Balance Sheet, Income Statement, & Cash Flow), including Amortization Table, and Depreciation Tables. 2) Analyze the information. a. Do you see any trends? b. How is the company doing? c. Provide at least two recommendations for Belle that you believe will improve profitability. Organizing Information Belleco Case Study Background Belle has been managing a small company making signs for the last five years. The company has grown significantly in this time. The company has purchased numerous assets, expanded the number of products, entered new markets, and has consistently expanded their customer base. When Belle started the company, she maintained a ledger with most of her transactions, and this was sufficient for her to get an impression of how the company was doing, or so she thought. She had several friends that also ran their own businesses, and all of them were basically using similar ledgers. More recently, however, Belle has become concerned about her finances. Whether it is due to the growth of the company or changing economic conditions she has begun to feel that she does not have a good understanding of how well her business is doing. She took a class to try and help her with her finances and she has come to the realization that she needs to apply some of the principles that she has learned in graduate school to get a better understanding of BelleCo's financial state. Her first undertaking has been to compile information from her ledgers. Information Belle's need to take stock of everything that her company had at its disposal. She was surprised at how much she had invested in the company given that she basically started out with nothing five years ago. She went through her old bank statements and found that her cash had increased over the years, which she thought was good news. She did the same for her inventories. Collecting the information for her raw materials, packaging, and finished goods that she had in her warehouse, she came up with the following summary table: Cash 1 $9,586.10 $28,000.00 2 $23,374.98 $56,000.00 3 $37,963.65 $84,000.00 4 $30,994.39 $112,000.00 5 $51,953.46 $96,000.00 Inventory From her tax accountant she learned that she depreciated her assets utilizing MACRS. Her building and land ($400,000) is depreciated on a 39-year MACRS nonresidential rea property schedule. Her manufacturing equipment ($260,000) has a depreciable life of 7 years and her delivery vehicle ($35,000) is depreciated based on a 3-year schedule. Two years ago she invested in an expansion to her equipment valued at $130,000, which is depreciable on a 7-year schedule. She has also invested in computers, automation equipment and office furniture over the years, according to her accountant the value for these items are: Value Other Items $2,000.00 $3,500.00 $4,700.00 $5,200.00 $5,000.00 These miscellaneous items will depreciate on a 3 years MACRS. Surprised at the values that she was finding, she decided to dig through her ledger and other notes. She found that her transactions had increased greatly, and all told, the amount of money that she owed for operational expenses and COGS that she had already ordered or received was a lot more than she had thought. Going through her notes she also started adding up the amounts that customers owed her for goods that she had already delivered. Accounts - 5 Receivable $2,400.00 $2,600.00 $3,800.00 $5,400.00 $9,800.00 Payable $10,000.00 $12,000.00 $15,000.00 $15,500.00 $17,000.00 sed She called her bank, and they were able to provide her with a detailed breakdown of her debt and line of credit. Belle had been very nervous to borrow money, but it had become necessary to purchase her present equipment. Five years ago, she had borrowed $500,000, at 4% interest repayable over 10 years. According to the bank, she also had a line of credit, which was opened three years ago, that presently stood at $50,000 and had balances in the preceding years of $50,000 and $70,000 (first year). The interest on the line of credit is 6.5%. There is no principle included in the required payment. Information on Operations BelleCo has shown strong growth in its sales. In the first year of operation, it sold $200,000. Since then, it has steadily grown by about 21% per year. Belle expected this Belle compiled some further information based on her operation. She found that the raw materials and other direct costs (COGS) that go into the production of her product had increased over the last five years. Similarly, her sales expenses and management and administration costs have increased. Belle summarizes these costs. 1 $93,000.00 2 $115,000.00 3 $146,300.00 4 $174,579.00 5 $195,340.00 COGS Sales Expenses $15,987.00 $23,456.00 $20,458.00 $31,345.00 $23,390.00 $39,876.00 $27,489.00 $48,767.00 $29,458.00 $63,254.00 G&A From the accountant, Belle was also told that her tax rate is 29%. Analysis Belle hopes that she will gain insight into her company by organizing and structuring all this information. 1) She would like to start by putting together a standard set of financial statements (Balance Sheet, Income Statement, & Cash Flow), including Amortization Table, and Depreciation Tables. 2) Analyze the information. a. Do you see any trends? b. How is the company doing? c. Provide at least two recommendations for Belle that you believe will improve profitability