This project is about preparing a pro forma income statement for a newly opened private bookstore. This bookstore expects sales of $1,500,000 in the first year (2017), with an increase (randomly determined) up to the range of 17.5%~18.5% in year 2, 18.0%~20.0% in year 3, and slowing down to the range of 15.0%~17.5% and 14.5%~16.0% in years 4 and 5 respectively.

The usual product mix in a collegiate bookstore is textbooks, software, non-book supplies (such as clothing, pens, notebooks, etc.), and general books (such as fiction, classics, reference, cookbooks, etc.). Such a product mix is usually necessary for offsetting both the low margins allowed for texts and the expenses of doing business. The Profit Margin Ratio (PMR) for each product is: Profit of the product divided by the Sales of the product. In addition, this bookstore estimates the following percentages of sales for various expenses.

Expense Percent of Sales

Salary & benefits 26.0%

Advertising 3.5%

General, selling & administration (SG&A) 4.8%

Miscellaneous expenses 2.1%

The Rent & Utility expense is $40,000 for the first year (2017) and grows at a different rate for each year as determined by the following formula: 6.5% + 0.2%*(number of years away from the base year)^1/2. Taxes are paid only when the Earnings Before Taxes is non-negative.

Use the Template (please do NOT change any items cell address) to prepare five-year pro forma income statements.

ASSIGNMENTS: (Make sure that worksheet data, B22~E39, are formatted as currency with no decimals. The rest of the worksheet assumptions are formatted as percent with one decimal). Note: All calculations should be done with formulas.

• Fill in the Product Mix percentages so that Text Books are at least two times the smallest product mix component.

• Calculate the Cost of Goods Sold based on provided Cost Margin Ratios.

• In cells C3 through E3 use the RAND() function to randomly generate a sales increase based on the ranges previously provided.

• Complete the Income Statement for Year 2017 using formulas in every cell possible. This will allow the cells to update dynamically if you change the numbers.

• Choose a set of profit margin ratios (only percentages of the format xx.0% are allowed) so that the predicted NET PROFIT AFTER TAXES for year 2019 is as large as possible within the range $130,000 and $135,000. Please note that for any two products, the higher profit margin ratio cannot be more than one and half times as much as the lower profit margin ratio.

• Based on the identified profit margin ratios from the previous task, make a copy of the worksheet and perform the next steps on the new worksheet. Seek a set of product mix ratios (i.e. the percentage combination for Text, Software, Non-Book, and General items) so that the predicted NET PROFIT AFTER TAXES for year 2020 is as large as possible within the range $120,000 and $130,000 (Constraints for 2020 do not apply here).

Each component in a product mix must be at least 10% but no more than 45%, and the percentage for the text book must be at least two times the smallest component. Also, only percentages of the format xx.0% are allowed. (Note that you might also need to re-identify a set of sales growth rates.)

Insert Draw Page Layout Home Formulas Data Review View Tell me C3 B C D E F 2018 2019 2020 2021 Product Mix Ratios Profit Margin Ratios Cost Margin Ratios 60% 28.0% 36% 54% 71% 25.0% 1 Pro Forma Assumptions 2 3 Sales Growth 4 5 6 Text 7 Software 8 Non-book 9 General 10 11 Expense Assumptions 12 Salary & Benefits 13 Advertising 14 Selling, Gen. & Adm. 15 Misc 16 Tax Rate 17 Base Year 18 19 26.0% 3.5% 4.8% 2.1% 35.0% 2010 20 INCOME STATEMENT 2017 2018 2019 2020 2021 1,500,000 21 SALES 22 Text 23 Software 24 Non-book 25 General 26 Total Sales 27 Cost of Goods Sold 28 Gross Profit 29 30 OPERATING EXPENSES 31 Salary & Benefits 32 Rent & Utilities 33 Advertising 34 Selling, Gen. & Adm. 35 Misc. 36 Total Operating Expense 37 Earnings Before Taxes 38 Taxes 39 Net Profit After Taxes 40 40,000 Sheet1 Insert Draw Page Layout Home Formulas Data Review View Tell me C3 B C D E F 2018 2019 2020 2021 Product Mix Ratios Profit Margin Ratios Cost Margin Ratios 60% 28.0% 36% 54% 71% 25.0% 1 Pro Forma Assumptions 2 3 Sales Growth 4 5 6 Text 7 Software 8 Non-book 9 General 10 11 Expense Assumptions 12 Salary & Benefits 13 Advertising 14 Selling, Gen. & Adm. 15 Misc 16 Tax Rate 17 Base Year 18 19 26.0% 3.5% 4.8% 2.1% 35.0% 2010 20 INCOME STATEMENT 2017 2018 2019 2020 2021 1,500,000 21 SALES 22 Text 23 Software 24 Non-book 25 General 26 Total Sales 27 Cost of Goods Sold 28 Gross Profit 29 30 OPERATING EXPENSES 31 Salary & Benefits 32 Rent & Utilities 33 Advertising 34 Selling, Gen. & Adm. 35 Misc. 36 Total Operating Expense 37 Earnings Before Taxes 38 Taxes 39 Net Profit After Taxes 40 40,000 Sheet1