Question

Here is a partial spreadsheet model for a small company named Ancient Relics Incorporated (ARI) The company manufactures souvenir copies of archaeological artifacts. The model is an annual model. For now, the model has just two years, using 2017 as a base and projecting data for 2018. Ancient Relics expects artifact sales revenue to grow at 40% per year. They have three expenses, Cost of Goods Sold (CGS), Warehousing, and Advertising. Cost of Goods Sold is calculated as 25% of sales revenue in each year. Warehousing expenses are expected to grow at 2% per year. Ancient Relics plans to spend an extra $2,000 per year on advertising. ARI’s tax rate is 22%. The partial spreadsheet shows the data for 2017 and the various inputs. You are to complete the spreadsheet by filling in the appropriate formulas for 2018 in column C. Eight formulas are needed (cells C13, C15, C16, C17, C18, C20, C21, and C22.) Make sure to write (type) your formulas so that it will be easy for someone to expand the model for several more years by copying cells. Make sure your model is mathematically sound and be sure to use good spreadsheet design practices. In particular, it should be possible for a user to perform "What if" analyses easily. You do NOT have to calculate the values of the formulas. You do NOT have to pay attention to formatting. Just write (type) the formulas in the cells. (cells C13, C15, C16, C17, C18, C20, C21, and C22.)

	A
1	Ancient Relics Incorporated (ARI)
2	Budget Projection Model
3
4	Input Variables
5	Sales Revenue Growth
6	Warehousing    Growth
7	Advertising increase
8	CGS as % revenue
9	Tax Rate
10
11
12
13	Gross Sales Revenue
14
15	CGS
16	Warehousing
17	Advertising
18	Total Expense
19
20	Earnings Before Taxes
21	Taxes
22	Net Income

Answer 1

For easier understanding, I have used the same cell numbers as given in the question.