In this project, you will use the upload to project future financials for Pops Lumber Yard. You have 3 years of past financials for the growing lumber company. The company is a discount lumber provider that relies on carrying large inventory amounts and selling on credit. As a result, the company carries a large amount of current assets and is constantly seeking more financing from the local bank.

The company has two sources of financing. The first is a long term mortgage on its facility currently valued at $50,000 (i.e. $50 on spreadsheet). The company is paying $7,000 down per year on the principal balance of this loan, and will continue this process in 2017.

The second source of financing is a credit revolver (or notes payable) that Pops uses to finance receivables and inventory. Currently, in 2016, the company has a credit limit of $250,000 (i.e. $250 on the balance sheet).

The company must project financials for 2017 to verify if it will exceed its financing limit, and then to address what options are available. The bank has already agreed to extend the credit line to $300,000.

Here are your assumptions:

1. Sales will grow by 33% in 2017.

2. The following items can be forecasted as a % of sales: all inventory numbers, purchases, accounts receivable, inventory, accounts payable, and accrued expenses.

3. Net property will increase by 10% in 2017.

4. The marginal income tax rate paid by the firm is 25%.

5. Interest on the long term debt is 11%, while interest on the notes payable is 10.5%.

6. The firm will continue the paydown of long term debt.

7. All net income is reinvested in the firm, with no dividends paid.

8. The company will forecast the cash balance at $40,000 for 2017.

Deliverables:

1. Build a common size financial statement for 2014-2016. Express each entry as a percentage of sales.

2. Take the average across the 3 years. Project the 2017 values for items that relate to sales using the average relationship. Use this approach for those items that relate to sales. For other entries, use accounting relationships to fill out the income statement and balance sheet.

3. Use accounting relationships to build the rest of the spreadsheet. The credit revolver (notes payable) will be your PLUG for the spreadsheet. (see Chapter 13 activities for examples of a PLUG)

4. On your sheet, calculate the follow ratios for 2014-2017: Current, quick, D/E, AR/Sales, INV/Sales, ROA, ROE.

5. Copy over your solution to the 30 days sheet. The company is currently not paying their payables in 30 days or less. At some point, suppliers will start charging interest on outstanding balances or cease supplying the company. We want to see the financing required to reduce Days Payables down to 30 days. We can calculate payables as follows:

Days Payable = COGS / (AP/365)

To reduce payables, the company will need greater financing. When you adjust the payables balance, the spreadsheet model should adjust to find the new required credit revolver from the bank. On this sheet, resolve your model with Days Payable at 30 days.

6. Copy your original model to the 10 days sheet. The company would like to investigate the possible benefit of taking trade discounts. The firm can get a 2% discount on their purchases if accounts payable are paid in 10 days or less. Resolve the model by getting days payable down to 10 days, and incorporate the discount into your spreadsheet. Observe the impact on the amount of notes, D/E, and net income.

7. Copy your original model over to sheet FCF. Calculate a FCF estimate for 2017.

8. In a paragraph or two, discuss if the company can finance their growth with the current credit arrangement. Then, discuss and show if the trade discount has value to the firm. Also, examine the FCF estimate and its meaning. Finally, comment on the company strategy. You can submit this as a word file with your submission.

POP'S LUMBER COMPANY: Net Sales COGS Beginning inventory % of sales 2014201s 2016 201420152016 AVERAGE SL69700 $2.013.00 $2,69400 I I $183.00 $23900 $325.00 Ending inventory Total Cost of Goods Sold Gross Profit Operating expense Operating income Plus: Discounts Interest expense Net income before taxes Provision for income taxes Net income $1,461.00 $1,763.00 $2,368.00 $239.00 $32600 $418.00 $1,222.00 $1,437.00 $1,950.00 $475.00 $576.00 $744.00 $425.00 $515.00 S658.00 $50.00 $61.00 S86.00 so00 $0.00 $0.00 $1300 $20.00 $33.00 $37.00 $41.00 $53.00 $6.00 $7.00 $9.00 $3100 $34.00 S4400 I I I I Balance Sheets Cash Accounts receivable, net Inventory Current Assets 2014 201s 2016 41 171222317 23932641s T90s 26140157 Total Assets Notes payablebank Accounts payable Accrued expenses 146 233 Current liabilities Total liabilities Total liabilities and net worth 324 43285 27030440 594 736 933 POP'S LUMBER COMPANY: Net Sales COGS Beginning inventory % of sales 2014201s 2016 201420152016 AVERAGE SL69700 $2.013.00 $2,69400 I I $183.00 $23900 $325.00 Ending inventory Total Cost of Goods Sold Gross Profit Operating expense Operating income Plus: Discounts Interest expense Net income before taxes Provision for income taxes Net income $1,461.00 $1,763.00 $2,368.00 $239.00 $32600 $418.00 $1,222.00 $1,437.00 $1,950.00 $475.00 $576.00 $744.00 $425.00 $515.00 S658.00 $50.00 $61.00 S86.00 so00 $0.00 $0.00 $1300 $20.00 $33.00 $37.00 $41.00 $53.00 $6.00 $7.00 $9.00 $3100 $34.00 S4400 I I I I Balance Sheets Cash Accounts receivable, net Inventory Current Assets 2014 201s 2016 41 171222317 23932641s T90s 26140157 Total Assets Notes payablebank Accounts payable Accrued expenses 146 233 Current liabilities Total liabilities Total liabilities and net worth 324 43285 27030440 594 736 933