Artero Corporation Solution Quarter
Ch 6, Prob. 7 (In $ Thousands)
Actual Projected Projected Projected Ending
Income Statements Sept. 2017 Oct. Nov. Dec. Dec. 2017
Sales 700.0 1000.0 1500.0 3000.0 5500.0
Cost of Goods Sold (80% of Sales) -560.0 -800.0 -1200.0 -2400.0 -4400.0
Gross Profit 140.0 200.0 300.0 600.0 1100.0
Other Expenses (7% of Sales) -49.0 -70.0 -105.0 -210.0 -385.0
Depreciation -10.0 -10.0 -10.0 -10.0 -30.0
Earnings Before Int. & Taxes (EBIT) 81.0 120.0 185.0 380.0 685.0
Balance Sheets Sept. 2017 Oct. Nov. Dec.
Required Cash 50.0 80.0 80.0 80.0
Surplus Cash 0.0 0.0 0.0 0.0
Accounts Receivable 700.0 1000.0 1500.0 3000.0
Inventories 500.0 500.0 500.0 500.0
Current Assets 1250.0 1580.0 2080.0 3580.0
Fixed Assets, Net 750.0 740.0 730.0 720.0
Total Assets 2000.0 2320.0 2810.0 4300.0
Statement of Cash Flows Sept. 2017 Oct. Nov. Dec.
Operating:
Net Income 64.8 102.3 216.9
Depreciation 10.0 10.0 10.0
Change in Accts. Receivable -300.0 -500.0 -1500.0
Change in Inventories 0.0 0.0 0.0
Change in Accts. Payable 0.0 0.0 0.0
Cash Flow from Operations -225.2 –387.7 -1273.1
Investing:
Change in Gross Fixed Assets 0.0 0.0 0.0
Financing:
New Bank Loans 255.2 387.7 1273.1
Beginning Cash 50.0 80.0 80.0
Check:
Cumulative Bank Loans 255.2 642.9 1916.0
Cash Budget Sept. 2017 Oct. Nov. Dec.
Collection of Receivables 700.0 1000.0 1500.0
Purchases -800.0 -1200.0 -2400.0
Other Cash Costs (7% of Sales) -70.0 -105.0 -210.0
Payment of Interest -12.0 -14.6 -18.4
Payment of Taxes -43.2 -68.2 -144.6
Investment in Fixed Assets 0.0 0.0 0.0
Net Monthly Cash Flow -225.2 -387.7 -1273.1
Beginning Cash 50.0 80.0 80.0
Less: Target Cash Amount 80.0 80.0 80.0
Ending Cash Before Borrowing -255.2 -387.7 -1273.1
New Bank Loans 255.2 387.7 1273.1
Ending (Required) Cash 80.0 80.0 80.0
Note For Instructors:
At this time, Excel spreadsheet solutions are not available for the Artero Corporation for
Problems 12 and 13.
Artero Corporation [More Complex Solution]
Ch 6, Prob. 7 ($ Thousands)
Actual Projected Projected Projected Projected
Income Statements Sept. 2017 Oct. Nov. Dec. Jan. 2018
Sales (S) 700.0 1000.0 1500.0 3000.0 700.0
Cost of Goods Sold (80% of S) -560.0 -800.0 -1200.0 -2400.0 -560.0
Gross Profit 140.0 200.0 300.0 600.0 140.0
Other Expenses (7% of S) -49.0 -70.0 -105.0 -210.0 -49.0
Depreciation -10.0 -10.0 -10.0 -10.0 -10.0
EBIT 81.0 120.0 185.0 380.0 81.0
Interest (1%/mth–all borrowed debt) -12.0 -15.3 -26.5 -31.6 -20.4
Earnings Before Taxes (EBT) 69.0 104.7 158.5 348.4 60.6
Taxes (40% of EBT) -27.6 -41.9 -63.4 -139.4 -24.3
forecasted making it necessary to prepare an inventories schedule.
Balance Sheets Sept. 2017 Oct. Nov. Dec. Jan. 2018
Required Cash 50.0 80.0 80.0 80.0 50.0
Surplus Cash 0.0 0.0 0.0 0.0 633.4
Accounts Receivable 700.0 1000.0 1500.0 3000.0 700.0
Inventories 500.0 900.0 2100.0 260.0 260.0
Current Assets 1250.0 1980.0 3680.0 3340.0 1643.4
Fixed Assets, Net 750.0 740.0 730.0 720.0 710.0
Total Assets 2000.0 2720.0 4410.0 4060.0 2353.4
Equity 800.0 862.8 957.9 1167.0 1203.4
Total Liab. & Equity 2000.0 2720.0 4410.0 4060.0 2403.4
Statement of Cash Flows Sept. 2017 Oct. Nov. Dec. Jan. 2018
Operating:
Inc. in Inventories -400.0 -1200.0 1840.0 0.0
Inc. in Accts. Payable 0.0 0.0 0.0 0.0
CF from Operations -627.2 -1594.9 559.1 2346.4
Investing:
Inc. in Gross Fixed Assets 0.0 0.0 0.0 0.0
Financing:
New Bank Borrowing 657.2 1594.9 -559.1 -1693.0
Net Monthly Cash Flow 30.0 0.0 0.0 653.4
Beginning Cash 50.0 80.0 80.0 80.0
Ending (Required) Cash 80.0 80.0 80.0 50.0
Total Cash 0.0 0.0 0.0 683.4
Surplus Cash 633.4
Check:
New Bank Borrowing 657.2 1594.9 -559.1 -1693.0
Cumulative Bank Borrowing 657.2 2252.1 1693.0 0.0
Inventories Schedule: Sept. 2017 Oct. Nov. Dec. Jan. 2018
Beginning Inventories 260.0 500.0 900.0 2100.0 260.0
Purchases 800.0 1200.0 2400.0 560.0 560.0
Cost of Goods Sold -560.0 -800.0 -1200.0 -2400.0 -560.0
Ending Inventories 500.0 900.0 2100.0 260.0 260.0
Interest-Bearing Debt Schedule:
S-T & L-T Interest-Bearing Debt 1200.0 1857.2 3452.1 2893.0 1200.0
Average of Beg & End Debt 1528.6 2654.6 3172.5 2046.5
Cash Budget Sept. 2017 Oct. Nov. Dec. Jan. 2018
Other Cash Costs (7% of S) -70.0 -105.0 -210.0 -49.0
Payment of Interest -15.3 -26.5 -31.6 -20.4
Payment of Taxes -41.9 -63.4 -139.4 -24.3
Investment in Fixed Assets 0.0 0.0 0.0 0.0
Net Monthly Cash Flow -627.2 -1594.9 559.1 2346.4
Beginning Cash 50.0 80.0 80.0 80.0
Less: Target Cash Amount 80.0 80.0 80.0 50.0
Ending Cash Before Borrowing -657.2 -1594.9 559.1 2376.4
Note For Instructors:
In this Excel solution scenario, assumptions differ in terms of how monthly interest is calculated
and level of inventories are projected to change monthly. See the “invenories schedule” which
assumes inventories (cost of goods sold) are purchased the month prior to when the sales are to be
made causing the level of inventories to change monthly.