50.1K

Verified Solution

Link Copied!

Please post formulas.

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 (2018), 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, Dens. 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 Marvin Deti (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 Salary & benefits Advertising General, selling, & administration Miscellaneous expenses Percent of Sales 26.0% 3.5% 4.8% 2.1% The Rent & Utility expense is $40,000 for the first year (2018), 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)". Taxes are paid only when the Earnings Before Taxes is non-negative. Using Exhibit 1 (please do NOT change any item's 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.) Decide 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 2020 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. Print the worksheet output with row numbers and column headings. Based on the identified profit margin ratios from the previous task, seek for another 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 2021 is as large as possible within the range $120,000 and $130,000. (Constraints for 2020 in the previous task do not apply here.) Each component in a product mix must be at least 15.0% but no more than 45.0%, and the percentage for the text book must be at least two times of the smallest component. In addition, each profit margin ratio may not be more than 50.0%. Remember, all the assumption rates must be in the format of one decimal. Print the worksheet output with row numbers and column headings. Replace C3's content with a formula involving RANDO. Print the cell formulas for cells C3 to C39 with row numbers and column headings. ATBc. I e IF Pro Forma Assumpties 2018 2019 2020 2021 2022 Sales Growth Product Mix Ratios Profit Marcin Ratios HTHTTTTTTTREFF F 28.0% Text Software Non-book General 25.0% 26.0% Expense Assumptions Salary & Benefits Advertising Selling. Gen.& Adm. Misc Tax Rate Base Year 2.15 35.0% 99 2015 INCOME STATEMENT 2019 2020 2018 2021 2022 21 SALES SALES Text Software Non-book General 1,500,000 Total Sales Cost of Goods Sold Gross Profit AL FIAT FISITEEREEEEEEEEEER! OPERATING EXPENSES Salary & Benefits Rent & Utilities 40,000 Advertising Selling, Gen. & Adm. Misc. 37 Total Operating Exp. Earnings Before Taxes Taxes Net Profit After Taxes 39 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 (2018), 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, Dens. 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 Marvin Deti (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 Salary & benefits Advertising General, selling, & administration Miscellaneous expenses Percent of Sales 26.0% 3.5% 4.8% 2.1% The Rent & Utility expense is $40,000 for the first year (2018), 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)". Taxes are paid only when the Earnings Before Taxes is non-negative. Using Exhibit 1 (please do NOT change any item's 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.) Decide 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 2020 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. Print the worksheet output with row numbers and column headings. Based on the identified profit margin ratios from the previous task, seek for another 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 2021 is as large as possible within the range $120,000 and $130,000. (Constraints for 2020 in the previous task do not apply here.) Each component in a product mix must be at least 15.0% but no more than 45.0%, and the percentage for the text book must be at least two times of the smallest component. In addition, each profit margin ratio may not be more than 50.0%. Remember, all the assumption rates must be in the format of one decimal. Print the worksheet output with row numbers and column headings. Replace C3's content with a formula involving RANDO. Print the cell formulas for cells C3 to C39 with row numbers and column headings. ATBc. I e IF Pro Forma Assumpties 2018 2019 2020 2021 2022 Sales Growth Product Mix Ratios Profit Marcin Ratios HTHTTTTTTTREFF F 28.0% Text Software Non-book General 25.0% 26.0% Expense Assumptions Salary & Benefits Advertising Selling. Gen.& Adm. Misc Tax Rate Base Year 2.15 35.0% 99 2015 INCOME STATEMENT 2019 2020 2018 2021 2022 21 SALES SALES Text Software Non-book General 1,500,000 Total Sales Cost of Goods Sold Gross Profit AL FIAT FISITEEREEEEEEEEEER! OPERATING EXPENSES Salary & Benefits Rent & Utilities 40,000 Advertising Selling, Gen. & Adm. Misc. 37 Total Operating Exp. Earnings Before Taxes Taxes Net Profit After Taxes 39

Answer & Explanation Solved by verified expert

See Answer