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
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. ARIs 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 |
|
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started