Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Frank Woods Business Accounting Volume 1

Authors: Alan Sangster Lewis Gordon Frank Wood

14th Edition

1292208627, 9781292208626

More Books

Students also viewed these Accounting questions