80.2K

Verified Solution

Link Copied!

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 (2023), 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 (2023) 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 Lab2-Workbook.xlsx 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 2023 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 2025 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 2024 is as large as possible within the range $120,000 and $130,000 (Constraints for 2026 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.)

• Complete the remaining years on the income statement.
• Submit your completed workbook labeled Lab
• I got this solution from the chegg. didn't understand the formula, please help me to solve each cell will formula.
• 
• 

Sales Growth Text Software Non-book General Expense Assumptions Salary \& Benefits Advertising Selling, Gen. \& Adm. Misc. Tax Rate Base Year SALES Text Software Non-book General Total Sales Cost of Goods Sold Gross Profit 2024 2025 2026 2057 \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline \end{tabular} \begin{tabular}{|c|r|c|} \hline Product Mix Ratios & Profit Margin Ratios & Cost Margin Ratios \\ \hline & 40% & 60% \\ \hline 28.0% & 64% & 36% \\ \hline 8.0% & 46% & 54% \\ \hline 25.0% & 29% & 71% \\ \hline \end{tabular} 0.56 17.0% \begin{tabular}{|c|} \hline 26.0% \\ \hline 3.5% \\ \hline 4.8% \\ \hline 2.1% \\ \hline 35.0% \\ \hline 2010 \\ \hline \end{tabular} INCOME STATEMENT 2023 2024 2025 2026 2027 OPERATING EXPENSES Salary \& Benefits 150000 Rent \& Utilities 260000 Advertising 40,000 Selling, Gen. \& Adm. Misc. Total Operating Expense Earnings Before Taxes Taxes Net Profit After Taxes \begin{tabular}{|l|l||l|l||} \hline Product Mix Ratios & Product Mix Ratios & Cost Margin % & COGS Value \\ \hline Text & 32.00% & 60% & 288000 \\ \hline Software & 28.00% & 36% & 151200 \\ \hline Non-book & 15.00% & 54% & 121500 \\ \hline General & 25.00% & 71% & 266250 \\ \hline Total & 100.00% & & 826950 \\ \hline \end{tabular} \begin{tabular}{|l|l|l|l|l|} \hline Expense Assumptions & % & SALES & Sales Breakup & Total Expenses \\ \hline Salary \& Benefits & 26.0% & Text & 480000 & 124800 \\ \hline Advertising & 3.5% & Software & 420000 & 14700 \\ \hline Selling, Gen. \& Adm. & 4.8% & Non-book & 225000 & 10800 \\ \hline Misc. & 2.1% & General & 375000 & 7875 \\ \hline Tax Rate & 35.0% & Total Sales & 1500000 & 158175 \\ \hline Base Year & 2010 & & & \\ \hline \end{tabular} \begin{tabular}{|l|l|} \hline INCOME STATEMENT & \multicolumn{2}{|l}{} \\ \hline Particulars & 2017 \\ \hline SALES & \\ \hline Text & 480000 \\ \hline Software & 420000 \\ \hline Non-book & 225000 \\ \hline General & 375000 \\ \hline Total Sales & 1500000 \\ \hline Less: Cost of Goods Sold & 826950 \\ \hline Gross Profit & 673050 \\ \hline OPERATING EXPENSES & \\ \hline Salary \& Benefits & 124800 \\ \hline Rent \& Utilities & 40000 \\ \hline Advertising & 14700 \\ \hline Selling, Gen. \& Adm. & 10800 \\ \hline Misc. & 7875 \\ \hline Operarting Expenses & 198175 \\ \hline Earning before Taxes & 474875 \\ \hline Less: Taxes & 166206 \\ \hline Net profit Before Taxes & 308669 \\ \hline \end{tabular}

Answer & Explanation Solved by verified expert

See Answer